3810 Jorgen Loland 2012-01-31
BUG#13599013 - MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
ROWS + INDEX DOES NOT RETURN NULL
An aggregated expression of type MIN or MAX shall return NULL
if the query is implicitly grouped and there is no HAVING
clause that evaluates to FALSE. In this bug we had a query
of the form
SELECT MIN(indexed_column) FROM t1 WHERE (4) IN (SELECT 1)
No row can satisfy the WHERE condition but there is no HAVING
clause, so the query should have returned NULL (but did
return an empty set).
The problem was that when a query only selects aggregate
functions that are optimized away, JOIN::exec() goes into an
early-exit branch. This branch did not honor the requirement
stated by the definition above. The fix is to call
return_zero_rows() instead of send_eof() in this branch. If
the query selects aggregates and does not have a HAVING
clause that evaluates to false, return_zero_rows() returns
a NULL-based row before calling send_eof().
@ mysql-test/include/subquery_sj.inc
Add test for BUG#13599013
@ mysql-test/r/subquery_mat.result
Updated result of test that used to be incorrect
@ mysql-test/r/subquery_mat_none.result
Updated result of test that used to be incorrect
@ mysql-test/r/subquery_sj_all.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_all_bka.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_all_bka_nixbnl.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_all_bkaunique.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_dupsweed.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_dupsweed_bka.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_dupsweed_bkaunique.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_firstmatch.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_firstmatch_bka.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_firstmatch_bkaunique.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_loosescan.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_loosescan_bka.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_loosescan_bkaunique.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_mat.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_mat_bka.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_mat_bka_nixbnl.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_mat_bkaunique.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_mat_nosj.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_none.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_none_bka.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_none_bka_nixbnl.result
Add test for BUG#13599013
@ mysql-test/r/subquery_sj_none_bkaunique.result
Add test for BUG#13599013
@ sql/sql_executor.cc
Call return_zero_rows() instead of send_eof() if all tables have
been optimized away and there are no matching rows.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_none.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_executor.cc
3809 Georgi Kodinov 2012-01-31
Addendum to the fix for bug #13586336
Suppressed the warnings in the pre-existing test change_user
because this test is also run in embedded where the
authentication checks are not compiled, so no warnings are
emitted.
modified:
mysql-test/r/change_user.result
mysql-test/t/change_user.test
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-01-27 12:28:14 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-01-31 11:19:25 +0000
@@ -4867,4 +4867,24 @@ DROP TABLE t1, t2;
--echo # End of test for bug#13596176.
+--echo #
+--echo # BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+--echo # BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+--echo # ROWS + INDEX DOES NOT RETURN NULL
+--echo #
+
+CREATE TABLE t1 (
+ pk int(11) PRIMARY KEY,
+ int_key int(11),
+ KEY int_key (int_key)
+);
+
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+
+DROP TABLE t1;
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result 2012-01-20 15:30:14 +0000
+++ b/mysql-test/r/subquery_mat.result 2012-01-31 11:19:25 +0000
@@ -717,12 +717,14 @@ id select_type table type possible_keys
2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
min(a1)
+NULL
explain select min(a1) from t1 where 7 in (select b1 from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
select min(a1) from t1 where 7 in (select b1 from t2);
min(a1)
+NULL
drop table t1,t2;
create table t1 (a char(2), b varchar(10));
insert into t1 values ('a', 'aaa');
=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result 2012-01-20 09:07:08 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2012-01-31 11:19:25 +0000
@@ -716,12 +716,14 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
min(a1)
+NULL
explain select min(a1) from t1 where 7 in (select b1 from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select min(a1) from t1 where 7 in (select b1 from t2);
min(a1)
+NULL
drop table t1,t2;
create table t1 (a char(2), b varchar(10));
insert into t1 values ('a', 'aaa');
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-01-31 11:19:25 +0000
@@ -7679,5 +7679,26 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-31 11:19:25 +0000
@@ -7681,6 +7681,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-31 11:19:25 +0000
@@ -7678,5 +7678,26 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-31 11:19:25 +0000
@@ -7679,6 +7679,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-31 11:19:25 +0000
@@ -7679,6 +7679,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-31 11:19:25 +0000
@@ -7679,6 +7679,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-31 11:19:25 +0000
@@ -7681,6 +7681,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-31 11:19:25 +0000
@@ -7679,5 +7679,26 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-31 11:19:25 +0000
@@ -7681,6 +7681,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-01-31 11:19:25 +0000
@@ -7679,5 +7679,26 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-31 11:19:25 +0000
@@ -7680,6 +7680,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-31 11:19:25 +0000
@@ -7681,6 +7681,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-31 11:19:25 +0000
@@ -7755,5 +7755,26 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-01-31 11:19:25 +0000
@@ -7690,5 +7690,26 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-31 11:19:25 +0000
@@ -7691,6 +7691,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-31 11:19:25 +0000
@@ -7691,6 +7691,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-27 12:28:14 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-31 11:19:25 +0000
@@ -7692,6 +7692,27 @@ vc_key
g
DROP TABLE t1, t2;
# End of test for bug#13596176.
+#
+# BUG#11754478: MAX/MIN + SUBQUERY + AND FAILS TO RETURN ANY ROWS
+# BUG#13599013: MAX/MIN + SUBQUERY IN WHERE CLAUSE MATCHING NO
+# ROWS + INDEX DOES NOT RETURN NULL
+#
+CREATE TABLE t1 (
+pk int(11) PRIMARY KEY,
+int_key int(11),
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES (1,0),(2,0),(3,2),(4,0),(5,3),(6,0);
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 1, 2);
+MIN(int_key)
+NULL
+SELECT MIN(int_key) FROM t1 WHERE (4, 4) IN (SELECT 4, 4);
+MIN(int_key)
+0
+SELECT MIN(pk) FROM t1 WHERE pk IN (SELECT int_key FROM t1) AND pk = 6;
+MIN(pk)
+NULL
+DROP TABLE t1;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-01-31 07:45:59 +0000
+++ b/sql/sql_executor.cc 2012-01-31 11:19:25 +0000
@@ -145,12 +145,6 @@ JOIN::exec()
if (!tables_list && (tables || !select_lex->with_sum_func))
{ // Only test of functions
- if (result->send_result_set_metadata(*columns_list,
- Protocol::SEND_NUM_ROWS |
- Protocol::SEND_EOF))
- {
- DBUG_VOID_RETURN;
- }
/*
We have to test for 'conds' here as the WHERE may not be constant
even if we don't have any tables for prepared statements or if
@@ -165,6 +159,12 @@ JOIN::exec()
(!conds || conds->val_int()) &&
(!having || having->val_int()))
{
+ if (result->send_result_set_metadata(*columns_list,
+ Protocol::SEND_NUM_ROWS |
+ Protocol::SEND_EOF))
+ {
+ DBUG_VOID_RETURN;
+ }
if (do_send_rows &&
(procedure ? (procedure->send_row(procedure_fields_list) ||
procedure->end_of_records()) : result->send_data(fields_list)))
@@ -175,15 +175,15 @@ JOIN::exec()
send_records= ((select_options & OPTION_FOUND_ROWS) ? 1 :
thd->get_sent_row_count());
}
+ /* Query block (without union) always returns 0 or 1 row */
+ thd->limit_found_rows= send_records;
+ thd->set_examined_row_count(0);
}
else
{
- error=(int) result->send_eof();
- send_records= 0;
+ tables= 0;
+ return_zero_rows(this, *columns_list);
}
- /* Single select (without union) always returns 0 or 1 row */
- thd->limit_found_rows= send_records;
- thd->set_examined_row_count(0);
DBUG_VOID_RETURN;
}
/*
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3809 to 3810) Bug#13599013 | Jorgen Loland | 31 Jan |
