diff options
| -rw-r--r-- | mysql-test/r/subselect.result | 49 | ||||
| -rw-r--r-- | mysql-test/t/subselect.test | 46 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 2 | ||||
| -rw-r--r-- | sql/sql_select.h | 2 |
4 files changed, 99 insertions, 0 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 101869e2843..4e12294da79 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5158,6 +5158,7 @@ DROP TABLE t1; # # 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 @@ s d 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 diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 24aad3a145d..6bb9a9ae875 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4025,6 +4025,7 @@ DROP TABLE t1; --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 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2 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 diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 2828ae8c4ec..3aa8dcd56f0 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2171,6 +2171,8 @@ bool subselect_uniquesubquery_engine::copy_ref_key() for (store_key **copy= tab->ref.key_copy ; *copy ; copy++) { + if ((*copy)->store_key_is_const()) + continue; tab->ref.key_err= (*copy)->copy(); /* diff --git a/sql/sql_select.h b/sql/sql_select.h index 1d1a023d9cf..79c07e80b25 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -670,6 +670,7 @@ public: } 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 @@ public: { } const char *name() const { return "const"; } + bool store_key_is_const() { return true; } protected: enum store_key_result copy_inner() |