From edf72bf61356553ba0007085c69bdb068c2cdf31 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 19 Jan 2007 00:17:28 -0800 Subject: Fixed bug #25219: crash for a query that contains an EXIST subquery with UNION over correlated and uncorrelated SELECTS. In such subqueries each uncorrelated SELECT should be considered as uncacheable. Otherwise join_free is called for it and in many cases it causes some problems. mysql-test/r/subselect.result: Added a test case for bug #25219. mysql-test/t/subselect.test: Added a test case for bug #25219. sql/mysql_priv.h: Fixed bug #25219: crash for a query that contains an EXIST subquery with UNION over correlated and uncorrelated SELECTS. In such subqueries each uncorrelated SELECT should be considered as uncacheable. Otherwise join_free is called for it and in many cases it causes some problems. Added a new flag UNCACHEABLE_UNITED for such SELECTs. sql/sql_lex.cc: Fixed bug #25219: crash for a query that contains an EXIST subquery with UNION over correlated and uncorrelated SELECTS. In such subqueries each uncorrelated SELECT should be considered as uncacheable. Otherwise join_free is called for it and in many cases it causes some problems. Added a new flag UNCACHEABLE_UNITED for such SELECTs. --- mysql-test/r/subselect.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/subselect.test | 34 ++++++++++++++++++++++++++++++++++ sql/mysql_priv.h | 5 ++++- sql/sql_lex.cc | 12 ++++++++++-- 4 files changed, 81 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 1bf6d6c7716..5cf3cd110c0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3605,3 +3605,36 @@ FROM t1) t; COUNT(*) 3000 DROP TABLE t1,t2; +CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); +CREATE TABLE t2 (c int); +INSERT INTO t1 VALUES ('aa', 1); +INSERT INTO t2 VALUES (1); +SELECT * FROM t1 +WHERE EXISTS (SELECT c FROM t2 WHERE c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); +SELECT * FROM t1 +WHERE EXISTS (SELECT c FROM t2 WHERE c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +bb 2 +cc 3 +dd 1 +INSERT INTO t2 VALUES (2); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (1); +SELECT * FROM t1 +WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 +UNION +SELECT c from t2 WHERE c=t1.c); +id c +aa 1 +bb 2 +cc 3 +dd 1 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 0b658f746a4..c6dd34b5172 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2508,3 +2508,37 @@ SELECT SQL_NO_CACHE COUNT(*) FROM t1) t; DROP TABLE t1,t2; + +# +# Bug #25219: EXIST subquery with UNION over a mix of +# correlated and uncorrelated selects +# + +CREATE TABLE t1 (id char(4) PRIMARY KEY, c int); +CREATE TABLE t2 (c int); + +INSERT INTO t1 VALUES ('aa', 1); +INSERT INTO t2 VALUES (1); + +SELECT * FROM t1 + WHERE EXISTS (SELECT c FROM t2 WHERE c=1 + UNION + SELECT c from t2 WHERE c=t1.c); + +INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1); + +SELECT * FROM t1 + WHERE EXISTS (SELECT c FROM t2 WHERE c=1 + UNION + SELECT c from t2 WHERE c=t1.c); + +INSERT INTO t2 VALUES (2); +CREATE TABLE t3 (c int); +INSERT INTO t3 VALUES (1); + +SELECT * FROM t1 + WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1 + UNION + SELECT c from t2 WHERE c=t1.c); + +DROP TABLE t1,t2,t3; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index caf3e6479f9..3033c7f360c 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -410,7 +410,10 @@ MY_LOCALE *my_locale_by_name(const char *name); #define UNCACHEABLE_EXPLAIN 8 /* Don't evaluate subqueries in prepare even if they're not correlated */ #define UNCACHEABLE_PREPARE 16 -/* Used to chack GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */ +/* For uncorrelated SELECT in an UNION with some correlated SELECTs */ +#define UNCACHEABLE_UNITED 32 + +/* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */ #define UNDEF_POS (-1) #ifdef EXTRA_DEBUG /* diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 3ae95ef9036..87793849094 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1371,9 +1371,17 @@ void st_select_lex::mark_as_dependent(SELECT_LEX *last) if (!(s->uncacheable & UNCACHEABLE_DEPENDENT)) { // Select is dependent of outer select - s->uncacheable|= UNCACHEABLE_DEPENDENT; + s->uncacheable= (s->uncacheable & ~UNCACHEABLE_UNITED) | + UNCACHEABLE_DEPENDENT; SELECT_LEX_UNIT *munit= s->master_unit(); - munit->uncacheable|= UNCACHEABLE_DEPENDENT; + munit->uncacheable= (munit->uncacheable & ~UNCACHEABLE_UNITED) | + UNCACHEABLE_DEPENDENT; + for (SELECT_LEX *sl= munit->first_select(); sl ; sl= sl->next_select()) + { + if (sl != s && + !(sl->uncacheable & (UNCACHEABLE_DEPENDENT | UNCACHEABLE_UNITED))) + sl->uncacheable|= UNCACHEABLE_UNITED; + } } is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; -- cgit v1.2.1