[Commits] Rev 3472: Merge 5.5 -> 10.0-serg in file:///data0/psergey/dev2/10.0-fix-subq-merge/

Sergey Petrunya psergey at askmonty.org
Sun Nov 4 17:32:35 EET 2012


At file:///data0/psergey/dev2/10.0-fix-subq-merge/

------------------------------------------------------------
revno: 3472 [merge]
revision-id: psergey at askmonty.org-20121104153232-z3q54rbvvqbmvgom
parent: psergey at askmonty.org-20121103082436-wyyctfe5gpl4vu3f
parent: psergey at askmonty.org-20121104150946-gjj2f536kt0e7jkq
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0-fix-subq-merge
timestamp: Sun 2012-11-04 19:32:32 +0400
message:
  Merge 5.5 -> 10.0-serg
modified:
  mysql-test/r/subselect2.result sp1f-subselect2.result-20030708080328-m676mj6xl2kf27rcthbx6tuskvlwugrr
  mysql-test/t/subselect2.test   sp1f-subselect2.test-20030708080328-cd5bnufcdc4krdcli6y4mjqva6rbxxiv
  mysys/my_context.c             my_context.c-20110606104754-eg3v207ie9dt9eau-2
  sql/opt_range.cc               sp1f-opt_range.cc-19700101030959-afe3wtevb7zwrg4xyibt35uamov5r7ds
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/sql_select.h               sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
=== modified file 'mysql-test/r/subselect2.result'
--- a/mysql-test/r/subselect2.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/subselect2.result	2012-11-04 15:09:46 +0000
@@ -180,6 +180,32 @@
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 #
+# MDEV-536: LP:1050806 - different result for a query using subquery 
+#
+DROP TABLE IF EXISTS `t1`;
+Warnings:
+Note	1051	Unknown table 't1'
+CREATE TABLE `t1` (
+`node_uid` bigint(20) unsigned DEFAULT NULL,
+`date` datetime DEFAULT NULL,
+`mirror_date` datetime DEFAULT NULL,
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid	date	mirror_date	result
+2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
+2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
+DROP TABLE t1;
+#
 # MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed
 #
 CREATE TABLE t1 (a int, b int, INDEX idx(a));
@@ -197,5 +223,76 @@
 1	0
 1	1
 1	3
-DROP TABLE t1, t2, t3;
+set @tmp_mdev567=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+SELECT * FROM t3
+WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+AND t3.b = t1.b
+GROUP BY t1.b);
+a	b
+1	0
+1	1
+1	3
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@tmp_mdev567;
+#
+# MDEV-614, also MDEV-536, also LP:1050806:
+#  different result for a query using subquery between 5.5.25 and 5.5.27
+#
+CREATE TABLE `t1` (
+`node_uid` bigint(20) unsigned DEFAULT NULL,
+`date` datetime DEFAULT NULL,
+`mirror_date` datetime DEFAULT NULL,
+KEY `date` (`date`)
+) ENGINE=MyISAM;
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+explain
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	range	date	date	9	NULL	2	Using index condition; Using where; Rowid-ordered scan; Using filesort
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid	date	mirror_date	result
+2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
+2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
+set @tmp_mdev614=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+explain
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	range	date	date	9	NULL	2	Using index condition; Using where; Using filesort
+SELECT * FROM (
+SELECT node_uid, date, mirror_date, @result := 0 AS result
+FROM t1
+WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+ORDER BY mirror_date ASC
+) AS calculated_result;
+node_uid	date	mirror_date	result
+2085	2012-01-01 00:00:00	2013-01-01 00:00:00	0
+2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
+set optimizer_switch=@tmp_mdev614;
+DROP TABLE t1;
 set optimizer_switch=@subselect2_test_tmp;

=== modified file 'mysql-test/t/subselect2.test'
--- a/mysql-test/t/subselect2.test	2012-10-10 05:21:22 +0000
+++ b/mysql-test/t/subselect2.test	2012-11-04 15:09:46 +0000
@@ -204,6 +204,32 @@
 DROP TABLE t1,t2,t3;
 
 --echo #
+--echo # MDEV-536: LP:1050806 - different result for a query using subquery 
+--echo #
+DROP TABLE IF EXISTS `t1`;
+
+CREATE TABLE `t1` (
+  `node_uid` bigint(20) unsigned DEFAULT NULL,
+  `date` datetime DEFAULT NULL,
+  `mirror_date` datetime DEFAULT NULL,
+  KEY `date` (`date`)
+) ENGINE=MyISAM;
+
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+
+SELECT * FROM (
+  SELECT node_uid, date, mirror_date, @result := 0 AS result
+  FROM t1
+  WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+  ORDER BY mirror_date ASC
+) AS calculated_result;
+
+DROP TABLE t1;
+
+--echo #
 --echo # MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed
 --echo #
 CREATE TABLE t1 (a int, b int, INDEX idx(a));
@@ -220,7 +246,75 @@
                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
                       AND t3.b = t1.b
               GROUP BY t1.b);
-DROP TABLE t1, t2, t3;
+
+
+set @tmp_mdev567=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+SELECT * FROM t3
+  WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+                WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+                      AND t3.b = t1.b
+              GROUP BY t1.b);
+
+DROP TABLE t1,t2,t3;
+set optimizer_switch=@tmp_mdev567;
+
+--echo #
+--echo # MDEV-614, also MDEV-536, also LP:1050806:
+--echo #  different result for a query using subquery between 5.5.25 and 5.5.27
+--echo #
+
+CREATE TABLE `t1` (
+  `node_uid` bigint(20) unsigned DEFAULT NULL,
+  `date` datetime DEFAULT NULL,
+  `mirror_date` datetime DEFAULT NULL,
+  KEY `date` (`date`)
+) ENGINE=MyISAM;
+
+INSERT INTO `t1` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
+INSERT INTO `t1` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
+
+explain
+SELECT * FROM (
+  SELECT node_uid, date, mirror_date, @result := 0 AS result
+  FROM t1
+  WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+  ORDER BY mirror_date ASC
+) AS calculated_result;
+
+SELECT * FROM (
+  SELECT node_uid, date, mirror_date, @result := 0 AS result
+  FROM t1
+  WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+  ORDER BY mirror_date ASC
+) AS calculated_result;
+
+set @tmp_mdev614=@@optimizer_switch;
+set optimizer_switch='mrr=off';
+explain
+SELECT * FROM (
+  SELECT node_uid, date, mirror_date, @result := 0 AS result
+  FROM t1
+  WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+  ORDER BY mirror_date ASC
+) AS calculated_result;
+
+SELECT * FROM (
+  SELECT node_uid, date, mirror_date, @result := 0 AS result
+  FROM t1
+  WHERE date < '2012-12-12 12:12:12'
+    AND node_uid in (2085, 2084)
+  ORDER BY mirror_date ASC
+) AS calculated_result;
+
+set optimizer_switch=@tmp_mdev614;
+
+DROP TABLE t1;
+
 
 set optimizer_switch=@subselect2_test_tmp;
 

=== modified file 'mysys/my_context.c'
--- a/mysys/my_context.c	2012-11-03 11:28:51 +0000
+++ b/mysys/my_context.c	2012-11-04 15:32:32 +0000
@@ -206,7 +206,7 @@
     (
      "movq %%rsp, (%[save])\n\t"
      "movq %[stack], %%rsp\n\t"
-#if __GNUC__ >= 4 && __GNUC_MINOR__ >= 4
+#if __GNUC__ >= 4 && __GNUC_MINOR__ >= 4 && !defined(__INTEL_COMPILER)
      /*
        This emits a DWARF DW_CFA_undefined directive to make the return address
        undefined. This indicates that this is the top of the stack frame, and

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2012-11-03 08:24:36 +0000
+++ b/sql/opt_range.cc	2012-11-04 15:32:32 +0000
@@ -10959,6 +10959,13 @@
   last_range= NULL;
   cur_range= (QUICK_RANGE**) ranges.buffer;
   RANGE_SEQ_IF seq_funcs= {NULL, quick_range_seq_init, quick_range_seq_next, 0, 0};
+  
+  if (file->inited == handler::RND)
+  {
+    /* Handler could be left in this state by MRR */
+    if ((error= file->ha_rnd_end()))
+      DBUG_RETURN(error);
+  }
 
   if (in_ror_merged_scan)
     head->column_bitmaps_set_no_signal(&column_bitmap, &column_bitmap);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-11-03 11:28:51 +0000
+++ b/sql/sql_select.cc	2012-11-04 15:32:32 +0000
@@ -19065,6 +19065,20 @@
     save_pre_sort_join_tab= join->pre_sort_join_tab;
     join->pre_sort_join_tab= NULL;
   }
+  else
+  {
+    /* 
+      Save index #, save index condition. Do it right now, because MRR may 
+    */
+    if (table->file->inited == handler::INDEX)
+    {
+      join->pre_sort_index= table->file->active_index;
+      join->pre_sort_idx_pushed_cond= table->file->pushed_idx_cond;
+      // no need to save key_read
+    }
+    else
+      join->pre_sort_index= MAX_KEY;
+  }
 
   /* Currently ORDER BY ... LIMIT is not supported in subqueries. */
   DBUG_ASSERT(join->group_list || !join->is_in_subquery());
@@ -19155,17 +19169,6 @@
 
   *(join->pre_sort_join_tab)= *tab;
   
-  if (table->file->inited == handler::INDEX)
-  {
-    // Save index #, save index condition
-    join->pre_sort_index= table->file->active_index;
-    join->pre_sort_idx_pushed_cond= table->file->pushed_idx_cond;
-    // no need to save key_read? 
-    err= table->file->ha_index_end();
-  }
-  else
-    join->pre_sort_index= MAX_KEY;
-
   /*TODO: here, close the index scan, cancel index-only read. */
 #if 0 
   /* MariaDB doesn't need the following: */
@@ -19211,8 +19214,6 @@
 #endif
   tab->select=NULL;
   tab->set_select_cond(NULL, __LINE__);
-//  tab->last_inner= 0;
-//  tab->first_unmatched= 0;
   tab->type=JT_ALL;				// Read with normal read_record
   tab->read_first_record= join_init_read_record;
   tab->table->file->ha_index_or_rnd_end();

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-11-03 11:28:51 +0000
+++ b/sql/sql_select.h	2012-11-04 15:32:32 +0000
@@ -895,6 +895,7 @@
 
 public:
   JOIN_TAB *join_tab, **best_ref;
+  
   /*
     For "Using temporary+Using filesort" queries, JOIN::join_tab can point to
     either: 



More information about the commits mailing list