Rex Johnston added a comment - 2024-07-02 18:47 Hi igor I discovered this when formulating pushdown tests for 31466. I think it needs to be applied to maintained builds. Thanks, Rex

Rex Johnston added a comment - 2024-07-22 00:53 - edited A step by step analysis of the issue. The following SQL statements illustrate the problem here create table t1 (c1 int , c2 int , c3 int ); insert into t1 values (1,2,3),(4,5,6); create table t2 (c4 int , c5 int , c6 int ); insert into t2 values (7,8,9),(10,11,12); prepare stmt from 'analyze format=json select * from ( select c1, c2, sum(c3) as s from t1 group by c1, c2 having s > 2 union select c4, c5, sum(c6) as u from t2 group by c4, c5 having u > 3 ) dt where c2 > 0' ; execute stmt; execute stmt; during the first execution of our statement stmt , JOIN::prepare() handles derived statements, indirectly calling mysql_derived_prepare() . This calls prepare on the unit represented by select c1, c2, sum (c3) as s from t1 group by c1, c2 having s > 2 union select c4, c5, sum (c6) as u from t2 group by c4, c5 having u > 3 fix_fields() on our having clause(s) resolves u > 3 by matching u to the item for sum(c6) as u JOIN::optimize() is called before actual statement execution. is_materialized_derived() is true, so pushdown_cond_for_derived() is called for derived TABLE_LIST for the unit show above. The pushed outer cond is c2 > 0 . This is pushed into select 1 in the unit (select t1.c1 AS c1,t1.c2 AS c2,sum(t1.c3) AS s from t1 group by t1.c1, t1.c2 having s > 2) then into select 2 (select t2.c4 AS c4,t2.c5 AS c5,sum(t2.c6) AS u from t2 group by t2.c4,t2.c5 having u > 3). As part of this, it does the following for the reasons in the comment. /* Rename the columns of all non-first selects of a union to be compatible by names with the columns of the first select. It will allow to use copies of the same expression pushed into having clauses of different selects. */ if (sl != first_sl) { DBUG_ASSERT(sl->item_list.elements == first_sl->item_list.elements); List_iterator_fast<Item> it(sl->item_list); List_iterator_fast<Item> nm_it(unit->types); while (Item *item= it++) item->share_name_with(nm_it++); } This permanently renames the item list overwriting 'sum(t2.c6) AS u' to 'sum(t2.c6) AS s' this is not reversed in mysql_derived_reinit() . During the second execution, the unit now looks like this select c1, c2, sum (c3) as s from t1 group by c1, c2 having s > 2 union select c4, c5, sum (c6) as s from t2 group by c4, c5 having u > 3 During fix_fields() , name resolution on u > 3 will now fail.

Igor Babaev (Inactive) added a comment - 2024-07-25 00:51 The following query over the tables from the above commit also fails on the second execution: select * from ( select c1, sum (c3) as s from t1 group by c1 union select c4 as c, sum (c6) as u from t2 group by c ) dt where c1 > 0 MariaDB [test]> prepare stmt from " "> select * from "> ( "> select c1, sum(c3) as s from t1 group by c1 "> union "> select c4 as c, sum(c6) as u from t2 group by c "> ) dt "> where c1 > 0 "> "; Query OK, 0 rows affected (0.002 sec) Statement prepared   MariaDB [test]> execute stmt; +------+------+ | c1 | s | +------+------+ | 1 | 3 | | 4 | 6 | | 7 | 9 | | 10 | 12 | +------+------+ 4 rows in set (0.004 sec)   MariaDB [test]> execute stmt; ERROR 1054 (42S22): Unknown column 'c' in 'group statement' As we can see this query does not contain any having clauses.

Igor Babaev (Inactive) added a comment - 2024-07-29 21:19 Ok to push into 10.5

Rex Johnston added a comment - 2024-07-29 22:07 for release notes: grouping operators referring to column aliases in unions inside derived tables can cause name resolution problems with prepared statements.