[Commits] Rev 3097: Fixed LP bug #919427. in file:///home/igor/maria/maria-5.2-bug919427/

Igor Babaev igor at askmonty.org
Sat Jan 21 09:54:43 EET 2012


At file:///home/igor/maria/maria-5.2-bug919427/

------------------------------------------------------------
revno: 3097
revision-id: igor at askmonty.org-20120121075443-2nexbu7aiugg1h4i
parent: sergii at pisem.net-20120112191341-48b7nb3p9pgy29oo
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.2-bug919427
timestamp: Fri 2012-01-20 23:54:43 -0800
message:
  Fixed LP bug #919427.
  The function subselect_uniquesubquery_engine::copy_ref_key has to take into
  account that when EXPLAIN is processed the array of store_key object created
  for any TABLE_REF may contain elements for constant items. These items should
  be ignored by thefunction.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-01-14 08:02:02 +0000
+++ b/mysql-test/r/subselect.result	2012-01-21 07:54:43 +0000
@@ -5158,6 +5158,7 @@
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
 # (duplicate of LP bug #888456)
+#
 CREATE TABLE t1 (f1 varchar(1));
 INSERT INTO t1 VALUES ('v'),('s');
 CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
@@ -5196,4 +5197,52 @@
 v	s
 s	s
 DROP TABLE t1,t2;
+#
+# LP bug 919427: EXPLAIN for a query over a single-row table 
+#                with IN subquery in WHERE condition
+# 
+CREATE TABLE ot (
+col_int_nokey int(11), 
+col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+CREATE TABLE it1(
+col_int_key int(11), 
+col_varchar_key varchar(1), 
+KEY idx_cvk_cik (col_varchar_key,col_int_key)
+);
+INSERT INTO it1 VALUES (NULL,'x'), (NULL,'f');
+CREATE TABLE it2 (
+col_int_key int(11),
+col_varchar_key varchar(1),
+col_varchar_key2 varchar(1),
+KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it2 VALUES (NULL,'x','x'), (NULL,'f','f');
+EXPLAIN
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	it1	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	Using index; Using where
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+col_int_nokey
+1
+EXPLAIN
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	DEPENDENT SUBQUERY	it2	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	func,const	1	Using index; Using where
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+col_int_nokey
+1
+DROP TABLE ot,it1,it2;
 End of 5.2 tests

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-01-14 08:02:02 +0000
+++ b/mysql-test/t/subselect.test	2012-01-21 07:54:43 +0000
@@ -4025,6 +4025,7 @@
 --echo #
 --echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
 --echo # (duplicate of LP bug #888456)
+--echo #
 
 CREATE TABLE t1 (f1 varchar(1));
 INSERT INTO t1 VALUES ('v'),('s');
@@ -4043,4 +4044,49 @@
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # LP bug 919427: EXPLAIN for a query over a single-row table 
+--echo #                with IN subquery in WHERE condition
+--echo # 
+
+CREATE TABLE ot (
+  col_int_nokey int(11), 
+  col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+
+CREATE TABLE it1(
+  col_int_key int(11), 
+  col_varchar_key varchar(1), 
+  KEY idx_cvk_cik (col_varchar_key,col_int_key)
+);
+INSERT INTO it1 VALUES (NULL,'x'), (NULL,'f');
+
+CREATE TABLE it2 (
+   col_int_key int(11),
+   col_varchar_key varchar(1),
+   col_varchar_key2 varchar(1),
+   KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+   KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it2 VALUES (NULL,'x','x'), (NULL,'f','f');
+
+EXPLAIN
+SELECT col_int_nokey FROM ot 
+  WHERE col_varchar_nokey IN 
+        (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+SELECT col_int_nokey FROM ot 
+  WHERE col_varchar_nokey IN 
+        (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+
+EXPLAIN
+SELECT col_int_nokey FROM ot
+  WHERE (col_varchar_nokey, 'x') IN
+        (SELECT col_varchar_key, col_varchar_key2 FROM it2);
+SELECT col_int_nokey FROM ot
+  WHERE (col_varchar_nokey, 'x') IN
+        (SELECT col_varchar_key, col_varchar_key2 FROM it2);
+
+DROP TABLE ot,it1,it2;
+
 --echo End of 5.2 tests

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-01-13 20:23:19 +0000
+++ b/sql/item_subselect.cc	2012-01-21 07:54:43 +0000
@@ -2171,6 +2171,8 @@
 
   for (store_key **copy= tab->ref.key_copy ; *copy ; copy++)
   {
+    if ((*copy)->store_key_is_const())
+      continue;
     tab->ref.key_err= (*copy)->copy();
 
     /*

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-11-21 17:13:14 +0000
+++ b/sql/sql_select.h	2012-01-21 07:54:43 +0000
@@ -670,6 +670,7 @@
   }
   virtual ~store_key() {}			/** Not actually needed */
   virtual const char *name() const=0;
+  virtual bool store_key_is_const() { return false; }
 
   /**
     @brief sets ignore truncation warnings mode and calls the real copy method
@@ -784,6 +785,7 @@
   {
   }
   const char *name() const { return "const"; }
+  bool store_key_is_const() { return true; }
 
 protected:  
   enum store_key_result copy_inner()



More information about the commits mailing list