revision-id: ef985d3de923334248a03732b042a18f18925e8a (mariadb-10.1.43-251-gef985d3de92) parent(s): ab578bdf453c3cb0e9ca561cf373f64c96b22fda author: Varun Gupta committer: Varun Gupta timestamp: 2020-08-12 02:19:17 +0530 message: MDEV-23449: alias do not exist and a query do not report an error For an IN/ANY/ALL subquery without an aggregate function and HAVING clause, the GROUP BY clause is removed. Due to the GROUP BY list being removed, the invalid reference in the GROUP BY clause was never resolved. Remove the GROUP BY list only when the all the items in the GROUP BY list are resolved. --- mysql-test/r/subselect4.result | 8 ++++++++ mysql-test/t/subselect4.test | 11 +++++++++++ sql/sql_select.cc | 32 ++++++++++++++++---------------- 3 files changed, 35 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index e7655131fcf..a4fd1123227 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2648,4 +2648,12 @@ a 1 2 DROP TABLE t1,t2; +# +# MDEV-23449: alias do not exist and a query do not report an error +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); +ERROR 42S22: Unknown column 's.id' in 'group statement' +DROP TABLE t1; # end of 10.1 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 8f1ad51ca50..03929517126 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2162,4 +2162,15 @@ SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FR DROP TABLE t1,t2; +--echo # +--echo # MDEV-23449: alias do not exist and a query do not report an error +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); + +--error ER_BAD_FIELD_ERROR +SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); +DROP TABLE t1; + --echo # end of 10.1 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4c6e87e4f27..7a1a7baaa1c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -731,22 +731,6 @@ JOIN::prepare(Item ***rref_pointer_array, tables_list, select_lex->leaf_tables, FALSE, SELECT_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(-1); - - /* - Permanently remove redundant parts from the query if - 1) This is a subquery - 2) This is the first time this query is optimized (since the - transformation is permanent - 3) Not normalizing a view. Removal should take place when a - query involving a view is optimized, not when the view - is created - */ - if (select_lex->master_unit()->item && // 1) - select_lex->first_cond_optimization && // 2) - !thd->lex->is_view_context_analysis()) // 3) - { - remove_redundant_subquery_clauses(select_lex); - } /* TRUE if the SELECT list mixes elements with and without grouping, @@ -814,6 +798,22 @@ JOIN::prepare(Item ***rref_pointer_array, ref_pointer_array= *rref_pointer_array; + /* + Permanently remove redundant parts from the query if + 1) This is a subquery + 2) This is the first time this query is optimized (since the + transformation is permanent + 3) Not normalizing a view. Removal should take place when a + query involving a view is optimized, not when the view + is created + */ + if (select_lex->master_unit()->item && // 1) + select_lex->first_cond_optimization && // 2) + !thd->lex->is_view_context_analysis()) // 3) + { + remove_redundant_subquery_clauses(select_lex); + } + /* Resolve the ORDER BY that was skipped, then remove it. */ if (skip_order_by && select_lex != select_lex->master_unit()->global_parameters())