Bug #39069: <row constructor> IN <table-subquery> seriously

            messed up

"ROW(...) IN (SELECT ... FROM DUAL)" always returned TRUE.

Item_in_subselect::row_value_transformer rewrites "ROW(...)
IN SELECT" conditions into the "EXISTS (SELECT ... HAVING ...)"
form.
For a subquery from the DUAL pseudotable resulting HAVING
condition is an expression on constant values, so further
transformation with optimize_cond() eliminates this HAVING
condition and resets JOIN::having to NULL.
Then JOIN::exec treated that NULL as an always-true-HAVING
and that caused a bug.

To distinguish an optimized out "HAVING TRUE" clause from
"HAVING FALSE" we already have the JOIN::having_value flag.
However, JOIN::exec() ignored JOIN::having_value as described
above as if it always set to COND_TRUE.

The JOIN::exec method has been modified to take into account
the value of the JOIN::having_value field.


mysql-test/r/subselect3.result:
  Added test case for bug #39069.
mysql-test/t/subselect3.test:
  Added test case for bug #39069.
sql/sql_select.cc:
  Bug #39069: <row constructor> IN <table-subquery> seriously
              messed up
  
  The JOIN::exec method has been modified to take into account
  the value of the JOIN::having_value field.

1 files changed, 54 insertions, 0 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 97479418bec..9a6f4436ff0 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -795,4 +795,58 @@ WHERE INNR.varchar_key > 'n{'
 );
 varchar_nokey
 DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1), (2), (11);
+# 2nd and 3rd columns should be same
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
+a	ROW(11, 12) = (SELECT a, 22)	ROW(11, 12) IN (SELECT a, 22)
+1	0	0
+2	0	0
+11	0	0
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
+a	ROW(11, 12) = (SELECT a, 12)	ROW(11, 12) IN (SELECT a, 12)
+1	0	0
+2	0	0
+11	1	1
+SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
+a	ROW(11, 12) = (SELECT a, 22)	ROW(11, 12) IN (SELECT a, 22)
+1	0	0
+2	0	0
+11	0	0
+SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
+a	ROW(11, 12) = (SELECT a, 12)	ROW(11, 12) IN (SELECT a, 12)
+1	0	0
+2	0	0
+11	1	1
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
+x	ROW(11, 12) = (SELECT MAX(x), 22)	ROW(11, 12) IN (SELECT MAX(x), 22)
+1	0	0
+2	0	0
+11	0	0
+# 2nd and 3rd columns should be same for x == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
+x	ROW(11, 12) = (SELECT MAX(x), 12)	ROW(11, 12) IN (SELECT MAX(x), 12)
+1	0	1
+2	0	1
+11	1	1
+DROP TABLE t1;
+# both columns should be same
+SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
+ROW(1,2) = (SELECT NULL, NULL)	ROW(1,2) IN (SELECT NULL, NULL)
+NULL	NULL
+SELECT ROW(1,2) = (SELECT   1,  NULL), ROW(1,2) IN (SELECT    1, NULL);
+ROW(1,2) = (SELECT   1,  NULL)	ROW(1,2) IN (SELECT    1, NULL)
+NULL	NULL
+SELECT ROW(1,2) = (SELECT NULL,    2), ROW(1,2) IN (SELECT NULL,    2);
+ROW(1,2) = (SELECT NULL,    2)	ROW(1,2) IN (SELECT NULL,    2)
+NULL	NULL
+SELECT ROW(1,2) = (SELECT NULL,    1), ROW(1,2) IN (SELECT NULL,    1);
+ROW(1,2) = (SELECT NULL,    1)	ROW(1,2) IN (SELECT NULL,    1)
+0	0
+SELECT ROW(1,2) = (SELECT    1,    1), ROW(1,2) IN (SELECT    1,    1);
+ROW(1,2) = (SELECT    1,    1)	ROW(1,2) IN (SELECT    1,    1)
+0	0
+SELECT ROW(1,2) = (SELECT    1,    2), ROW(1,2) IN (SELECT    1,    2);
+ROW(1,2) = (SELECT    1,    2)	ROW(1,2) IN (SELECT    1,    2)
+1	1
 End of 5.0 tests