diff options
| author | Gleb Shchepa <gshchepa@mysql.com> | 2009-01-28 22:46:45 +0400 |
|---|---|---|
| committer | Gleb Shchepa <gshchepa@mysql.com> | 2009-01-28 22:46:45 +0400 |
| commit | 334e249f914359864996adaf54508ed7b40971e2 (patch) | |
| tree | 0df7b2174b0c191159b3184df86b581a26390bb0 /mysql-test/t | |
| parent | aa0641dcf6da9d5ecf70d9963a61b2deac81b0b8 (diff) | |
| download | mariadb-git-334e249f914359864996adaf54508ed7b40971e2.tar.gz | |
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.
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/subselect3.test | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 7e9aa1554c0..2d88d1660b0 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -640,4 +640,33 @@ WHERE NULL NOT IN ( DROP TABLE t1; +# +# Bug #39069: <row constructor> IN <table-subquery> seriously messed up +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (11); + +--echo # 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; +SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a; +SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1; +SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1; + +# The x alias is used below to workaround bug #40674. +# Regression tests for sum function on outer column in subselect from dual: +SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1; +--echo # 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; + +DROP TABLE t1; + +--echo # both columns should be same +SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL); +SELECT ROW(1,2) = (SELECT 1, NULL), ROW(1,2) IN (SELECT 1, NULL); +SELECT ROW(1,2) = (SELECT NULL, 2), ROW(1,2) IN (SELECT NULL, 2); +SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1); +SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1); +SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); + --echo End of 5.0 tests |