[Commits] Rev 3552: fix for MDEV-367 in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-367/

sanja at askmonty.org sanja at askmonty.org
Wed Aug 22 16:54:06 EEST 2012


At file:///home/bell/maria/bzr/work-maria-5.3-MDEV-367/

------------------------------------------------------------
revno: 3552
revision-id: sanja at askmonty.org-20120822135404-i6o0gbkbxypv5824
parent: sanja at montyprogram.com-20120626184334-ptpg39ptq3t79dg5
committer: sanja at askmonty.org
branch nick: work-maria-5.3-MDEV-367
timestamp: Wed 2012-08-22 16:54:04 +0300
message:
  fix for MDEV-367
  
  The problem was that was_null and null_value variables was reset in each reexecution of IN subquery, but engine rerun only for non-constant subqueries.
  
  Fixed checking constant in Item_equal sort.
  Fix constant reporting in Item_subselect.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-06-15 08:33:24 +0000
+++ b/mysql-test/r/subselect.result	2012-08-22 13:54:04 +0000
@@ -6137,5 +6137,31 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NU
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-06-15 08:33:24 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-08-22 13:54:04 +0000
@@ -6136,6 +6136,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NU
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-06-15 08:33:24 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-08-22 13:54:04 +0000
@@ -6132,6 +6132,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NU
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-06-15 08:33:24 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-08-22 13:54:04 +0000
@@ -6143,6 +6143,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NU
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-06-15 08:33:24 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-08-22 13:54:04 +0000
@@ -6132,6 +6132,32 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NU
 SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1;
 min_a	a
 drop table t1;
+#
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-06-15 08:33:24 +0000
+++ b/mysql-test/t/subselect.test	2012-08-22 13:54:04 +0000
@@ -5208,5 +5208,26 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE
 
 drop table t1;
 
+--echo #
+--echo # MDEV-367: Different results with and without subquery_cache on
+--echo # a query with a constant NOT IN condition
+--echo #
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+
+set @mdev367_optimizer_switch = @@optimizer_switch;
+
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+set optimizer_switch=@mdev367_optimizer_switch;
+ 
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+set optimizer_switch=@mdev367_optimizer_switch;
+
+DROP TABLE t1;
+
 --echo # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-06-15 08:33:24 +0000
+++ b/sql/item_subselect.cc	2012-08-22 13:54:04 +0000
@@ -765,7 +765,9 @@ table_map Item_subselect::used_tables()
 
 bool Item_subselect::const_item() const
 {
-  return thd->lex->context_analysis_only ? FALSE : const_item_cache;
+  return (thd->lex->context_analysis_only ?
+          FALSE :
+          forced_const || const_item_cache);
 }
 
 Item *Item_subselect::get_tmp_table_item(THD *thd_arg)
@@ -1484,6 +1486,10 @@ double Item_in_subselect::val_real()
   */
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value;
+  DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
+              ! engine->is_executed());
   null_value= was_null= FALSE;
   if (exec())
   {
@@ -1504,6 +1510,10 @@ longlong Item_in_subselect::val_int()
   */
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    return value;
+  DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
+              ! engine->is_executed());
   null_value= was_null= FALSE;
   if (exec())
   {
@@ -1524,6 +1534,10 @@ String *Item_in_subselect::val_str(Strin
   */
   DBUG_ASSERT(0);
   DBUG_ASSERT(fixed == 1);
+  if (forced_const)
+    goto value_is_ready;
+  DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
+              ! engine->is_executed());
   null_value= was_null= FALSE;
   if (exec())
   {
@@ -1535,6 +1549,7 @@ String *Item_in_subselect::val_str(Strin
     null_value= TRUE;
     return 0;
   }
+value_is_ready:
   str->set((ulonglong)value, &my_charset_bin);
   return str;
 }
@@ -1545,6 +1560,8 @@ bool Item_in_subselect::val_bool()
   DBUG_ASSERT(fixed == 1);
   if (forced_const)
     return value;
+  DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
+              ! engine->is_executed());
   null_value= was_null= FALSE;
   if (exec())
   {
@@ -1563,6 +1580,10 @@ my_decimal *Item_in_subselect::val_decim
     method should not be used
   */
   DBUG_ASSERT(0);
+  if (forced_const)
+    goto value_is_ready;
+  DBUG_ASSERT((engine->uncacheable() & ~UNCACHEABLE_EXPLAIN) ||
+              ! engine->is_executed());
   null_value= was_null= FALSE;
   DBUG_ASSERT(fixed == 1);
   if (exec())
@@ -1572,6 +1593,7 @@ my_decimal *Item_in_subselect::val_decim
   }
   if (was_null && !value)
     null_value= TRUE;
+value_is_ready:
   int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value);
   return decimal_value;
 }
@@ -3117,6 +3139,8 @@ int subselect_single_select_engine::exec
       tab->read_record.read_record= tab->save_read_record;
     }
     executed= 1;
+    if (!(uncacheable() & ~UNCACHEABLE_EXPLAIN))
+      item->make_const();
     thd->where= save_where;
     thd->lex->current_select= save_select;
     DBUG_RETURN(join->error || thd->is_fatal_error || thd->is_error());

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-06-18 18:38:11 +0000
+++ b/sql/sql_select.cc	2012-08-22 13:54:04 +0000
@@ -11615,9 +11615,9 @@ static int compare_fields_by_table_order
   bool outer_ref= 0;
   Item_field *f1= (Item_field *) (field1->real_item());
   Item_field *f2= (Item_field *) (field2->real_item());
-  if (f1->const_item())
+  if (field1->const_item() || f1->const_item())
     return 1;
-  if (f2->const_item())
+  if (field2->const_item() || f2->const_item())
     return -1;
   if (f2->used_tables() & OUTER_REF_TABLE_BIT)
   {  

=== modified file 'sql/sql_union.cc'
--- a/sql/sql_union.cc	2012-02-25 00:50:22 +0000
+++ b/sql/sql_union.cc	2012-08-22 13:54:04 +0000
@@ -616,6 +616,8 @@ bool st_select_lex_unit::exec()
   if (executed && !uncacheable && !describe)
     DBUG_RETURN(FALSE);
   executed= 1;
+  if (!(uncacheable & ~UNCACHEABLE_EXPLAIN) && item)
+    item->make_const();
   
   saved_error= optimize();
 



More information about the commits mailing list