From 6dfe0956d64b41a2e4eda0f40feaad799cd84058 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 13 Jan 2012 12:23:19 -0800 Subject: Back-ported the test cases for bug #12763207 from mysql-5.6 code line into 5.2 Completed the fix for this bug. Note: in 5.3 the affected 'if' statement in Item_in_subselect::single_value_transformer() starting with the condition (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) should be removed altogether. The change from table.cc is not needed either. This is because in 5.3 - min/max transformation for subqueries are done at the optimization phase - evaluation of the expensive subqueries is done at the execution phase. Added an EXPLAIN EXTENDED to the test case for bug #12329653. --- mysql-test/r/subselect.result | 53 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect.test | 53 +++++++++++++++++++++++++++++++++++++++++++ sql/item_subselect.cc | 3 ++- sql/table.cc | 1 + 4 files changed, 109 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 65e76d04c1f..5063b4c6db9 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4568,6 +4568,13 @@ CREATE TABLE t1(a1 int); INSERT INTO t1 VALUES (1),(2); SELECT @@session.sql_mode INTO @old_sql_mode; SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +EXPLAIN EXTENDED +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select 1 AS `1` from `test`.`t1` where 1 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); 1 1 @@ -5088,4 +5095,50 @@ NULL NULL 5 DROP TABLE t1, t2, t3; +# +# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +# +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +1 +1 +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +1 +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); +1 +1 +1 +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1, t2; +create table t2(i int); +insert into t2 values(0); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; +CREATE TABLE t1 ( +pk int NOT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key) +); +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; +pk +SET SESSION sql_mode=@old_sql_mode; +drop table t2, t1; +drop view v1; End of 5.2 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index b918e800dd5..e4ed6188f25 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3460,6 +3460,8 @@ SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; ## First a simpler query, illustrating the transformation ## '1 < some (...)' => '1 < max(...)' +EXPLAIN EXTENDED +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); ## The query which made the server crash. @@ -3958,4 +3960,55 @@ INSERT INTO t3 VALUES (0),(0); SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; DROP TABLE t1, t2, t3; +--echo # +--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2(a1 int); +INSERT INTO t2 VALUES (3); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## All these are subject to the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2); +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2); + +SET SESSION sql_mode=@old_sql_mode; + +DROP TABLE t1, t2; + +create table t2(i int); +insert into t2 values(0); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +CREATE VIEW v1 AS +SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2 +; + +CREATE TABLE t1 ( + pk int NOT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key) +); + +SELECT t1.pk +FROM t1 +WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 ) +; + +SET SESSION sql_mode=@old_sql_mode; + +drop table t2, t1; +drop view v1; + --echo End of 5.2 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 82d2313aa2b..2828ae8c4ec 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1057,7 +1057,8 @@ Item_in_subselect::single_value_transformer(JOIN *join, print_where(item, "rewrite with MIN/MAX", QT_ORDINARY);); if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) { - DBUG_ASSERT(select_lex->non_agg_field_used()); + DBUG_ASSERT(item->get_arg(0)->real_item()->type() != Item::FIELD_ITEM || + select_lex->non_agg_field_used()); select_lex->set_non_agg_field_used(false); } diff --git a/sql/table.cc b/sql/table.cc index ca14b2ef4d2..a3df9023805 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4467,6 +4467,7 @@ Item *Field_iterator_table::create_item(THD *thd) { select->non_agg_fields.push_back(item); item->marker= select->cur_pos_in_select_list; + select->set_non_agg_field_used(true); } return item; } -- cgit v1.2.1