Rex Johnston added a comment - 2025-01-16 01:23 - edited This is caused by check_and_do_in_subquery_rewrites()/Item_singlerow_subselect::select_transformer() If we disable this transformation, we have more consistency, but...   MariaDB [test]> select * from t2 where t2a in ( select t1a from t1 group by ( select t1a > t2b)); + ------+------+------+ | t2a | t2b | t2c | + ------+------+------+ | 1 | 1 | 1 | + ------+------+------+ 1 row in set (0.018 sec)   MariaDB [test]> select * from t2 where t2a in ( select t1a from t1 group by ( select t1a > t2b where true )); + ------+------+------+ | t2a | t2b | t2c | + ------+------+------+ | 1 | 1 | 1 | + ------+------+------+ 1 row in set (0.056 sec)   MariaDB [test]> select * from t2 where t2a in ( select t1a from t1 group by t1a > t2b); + ------+------+------+ | t2a | t2b | t2c | + ------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | + ------+------+------+ 2 rows in set (0.006 sec) We clearly have another problem elsewhere.

Rex Johnston added a comment - 2025-01-20 00:40 - edited The above problem can be distilled down to this wrong result MariaDB [test]> select 2 in ( select t1a from t1 group by t1a > 2 order by t1a); + ---------------------------------------------------------+ | 2 in ( select t1a from t1 group by t1a > 2 order by t1a) | + ---------------------------------------------------------+ | 1 | + ---------------------------------------------------------+ 1 row in set (0.008 sec)   MariaDB [test]> select t1a from t1 group by t1a > 2 order by t1a; + ------+ | t1a | + ------+ | 1 | + ------+ What is happening here is that the group by clause is removed by remove_redundant_subquery_clauses() , then the condition t1a = 2 is pushed into the Item_subquery by Item_in_subselect::inject_in_to_exists_cond , resulting in the select_lex looking like this select t1.t1a from t1 where 2 = t1.t1a or t1.t1a is null having t1.t1a is null order by t1.t1a which is clearly not equivalent to the original subquery. Note that we remove the group by clause prior to injecting the new condition into the Item_subselect.

Rex Johnston added a comment - 2025-01-20 00:57 - edited From remove_redundant_subquery_clauses() for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next) { /* Do not remove the item if it is used in select list and then referred from GROUP BY clause by its name or number. Example:   select (select ... ) as SUBQ ... group by SUBQ   Here SUBQ cannot be removed. */ if (!ord->in_field_list) { /* Not necessary due to workaround for MDEV-28621: (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL); */ /* Remove from the JOIN::all_fields list any reference to the elements of the eliminated GROUP BY list unless it is 'in_field_list'. This is needed in order not to confuse JOIN::make_aggr_tables_info() when it constructs different structure for execution phase. */ List_iterator<Item> li(subq_select_lex->join->all_fields); Item *item; while ((item= li++)) { if (item == *ord->item) li. remove (); } } the test, ord->in_field_list assumes that the order is an Item_ident , but here it is an expression. We need to walk this (potential) ord expression looking for Item_field and abort group by removal if we find any matching Item_field in the select list expressions.

Michael Widenius added a comment - 2025-01-21 20:44 select t1a from t1 group by (select t1a > t2b) order by t1a