Igor Babaev (Inactive) added a comment - 2024-12-18 06:28 - edited Let's investigate why we have a problem when fixing dt2.x from the expression dt2.x >=1 pushed into the derived table dt. This dt2.x is a full copy of dt2.x from the item list of the select that we get after having merged dt1. Due to this merge this occurrence of dt2.x is wrapped into an Item_direct_view_ref reference item. Another occurrence of dt2.x appears after the merge in (SELECT dt1.x). It's also wrapped into an Item_direct_view_ref reference item. Bear in mind that both wrapper reference item points to the same dt2.x in the translation table created for dt1. We see that by the time when the first item Item_direct_view_ref(dt2.x) is used to create a copy of it to replace dt1.x from the expression dt1.x>= 1 to be pushed into dt the context structure of dt2.x from the translation table is already incorrect. Let's check when the pointer to this structure is changed. It's changed in the function: bool Item_ident::remove_dependence_processor(void * arg) { DBUG_ENTER("Item_ident::remove_dependence_processor"); if (get_depended_from() == (st_select_lex *) arg) depended_from= 0; context= &((st_select_lex *) arg)->context; DBUG_RETURN(0); } when we execute the following code: /* it is single select without tables => possible optimization remove the dependence mark since the item is moved to upper select and is not outer anymore. */ where_item->walk(&Item::remove_dependence_processor, 0, select_lex->outer_select()); Here we have: (gdb) p dbug_print_select(select_lex) $8 = 0x555557b87600 <dbug_item_print_buf> "select dt2.x" (gdb) p dbug_print_item(where_item) $9 = 0x555557b87600 <dbug_item_print_buf> "dt2.x" (gdb) p where_item->type() $10 = Item::REF_ITEM (gdb) p ((Item_ref*)where_item)->ref_type() $11 = Item_ref::VIEW_REF (gdb) p ((Item_direct_view_ref*)where_item)->view->alias $12 = {str = 0x7fff74019b30 "dt1", length = 3} (gdb) p (*(((Item_direct_view_ref*)where_item)->ref))->type() $14 = Item::FIELD_ITEM (gdb) p dbug_print_item(*(((Item_direct_view_ref*)where_item)->ref)) $15 = 0x555557b87600 <dbug_item_print_buf> "dt2.x" We see that the statement: context= &((st_select_lex *) arg)->context; effectively sets the new context of the occurrence dt2.x in the select list. After this change we have the context with: (gdb) p dbug_print_select(((st_select_lex *) arg)->context.select_lex) $38 = 0x555557b87600 <dbug_item_print_buf> "select t2.a from t2 where 1 in (subquery#6) and t2.a < 2" gdb) p ((st_select_lex *) arg)->context.first_name_resolution_table->alias $39 = {str = 0x7fff7801a8e0 "t2", length = 2} dt2.x cannot be resolved a a field of t2. The context of Item_direct_view_ref wrapper around dt2.x is also changed and in the same way. Note that only the occurrence of dt2.x in the IN predicate is an outer reference. And get_depended_from() != NULL only for this occurrence. The following implementation of Item_ident::remove_dependence_processor could fix this problem: bool Item_ident::remove_dependence_processor(void * arg) { DBUG_ENTER("Item_ident::remove_dependence_processor"); if (get_depended_from() == (st_select_lex *) arg) { depended_from= 0; context= &((st_select_lex *) arg)->context; } DBUG_RETURN(0); } And indeed with this change we have: MariaDB [test]> SELECT dt.x -> FROM -> ( -> SELECT dt1.x -> FROM -> ( -> SELECT dt2.x -> FROM -> ( -> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x -> ) dt2 -> ) dt1 -> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 -> GROUP BY dt1.x -> ) dt -> WHERE dt.x>=1; +------+ | x | +------+ | 1 | +------+ and all tests from the main test suite pass, and with --ps-protocol and --view-protocol as well. Yet the query itself returns the empty set when executed in prepared mode. MariaDB [test]> PREPARE stmt FROM " "> SELECT dt.x "> FROM "> ( "> SELECT dt1.x "> FROM "> ( "> SELECT dt2.x "> FROM "> ( "> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x "> ) dt2 "> ) dt1 "> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 "> GROUP BY dt1.x "> ) dt "> WHERE dt.x>=1; "> "; Query OK, 0 rows affected (0.002 sec) Statement prepared   MariaDB [test]> EXECUTE stmt; Empty set ( 0.003 sec)

Igor Babaev (Inactive) added a comment - 2024-12-24 04:23 - edited Moreover, even for the query to which no pushdown into derived can be applied: SELECT dt.x FROM ( SELECT dt1.x FROM ( SELECT dt2.x FROM ( SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x ) dt2 ) dt1 WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 GROUP BY dt1.x ) dt; we see the same wrong result when executing the query in prepared mode: MariaDB [test]> PREPARE stmt FROM " "> SELECT dt.x "> FROM "> ( "> SELECT dt1.x "> FROM "> ( "> SELECT dt2.x "> FROM "> ( "> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x "> ) dt2 "> ) dt1 "> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 "> GROUP BY dt1.x "> ) dt; "> "; Query OK, 0 rows affected (0.002 sec) Statement prepared   MariaDB [test]> EXECUTE stmt; Empty set (0.003 sec) Let's investigate why it happens. When processing the EXECUTE command at the call of JOIN::prepare for the query we see: (gdb) p dbug_print_select(select_lex) $40 = 0x555557b87600 <dbug_item_print_buf> "select dt.x AS x from (select dt1.x AS x from (select dt2.x AS x from (select t1.x AS x from t1 where t1.x < 2 group by t1.x) dt2) dt1 where (subquery#5) <> 0 group by dt1.x) dt" and subquery#5 here looks like this: (gdb) p dbug_print_select(select_lex) $44 = 0x555557b87600 <dbug_item_print_buf> "select t2.a from t2 where 1 = dt2.x and t2.a < 2" Here dt2.x in the expression "1 = dt2.x" is actually wrapped into an Item_direct_view_ref object. When fix_fields is called for the where condition of subquery#5 it calls fix_fields for the expression. The latter calls fix_fields for Item_direct_view_ref around dt2.x. Note that dt2.x is the item from the translation table created for the mergeable derived table dt1. Because dt2.x is also used in the item list of select containing subquery#5 it has already been fixed and no new fix_fields is called for the occurrence of dt2.x from subquery#5. As a result no Item_field::fix_outer_field is called for this occurrence of dt2.x and the list upper_refs remains empty for the subquery#5. It leads to a wrong re-calculation of used_tables_cache for the subquery in the call of st_select_lex::update_used_tables() at the optimization stage after which the subquery mistakenly is treated as non-correlated. Opposed to the occurrence of dt2.x from the item list of the select that uses subquery#5 the occurrence of dt2.x in this subquery preserves an Item_direct_view_ref wrapper while such wrapper for the first occurrence is rolled back at the end of processing the PREPARE command. The wrapper around the second occurrence has not been rolled back due to the conversion of the predicate 1 IN (SELECT dt1.x) into the expression 1 = dt1.x done when the PREPARE command was executed.

Igor Babaev (Inactive) added a comment - 2024-12-28 04:12 If we do the conversion of the predicate 1 IN (SELECT dt1.x) into the expression 1 = dt1.x at the first execution of the prepared statement rather than when processing the PREPARE command we'll get an empty result set at the second execution. After having the following results for the procedure: CREATE PROCEDURE p() SELECT dt.x FROM ( SELECT dt1.x FROM ( SELECT dt2.x FROM ( SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x ) dt2 ) dt1 WHERE ( SELECT t2.a FROM t2 WHERE (1 IN ( SELECT dt1.x)) AND t2.a < 2) != 0 GROUP BY dt1.x ) dt; MariaDB [test]> CALL p(); +------+ | x | +------+ | 1 | +------+ 1 row in set (0.003 sec)   Query OK, 0 rows affected (0.003 sec)   MariaDB [test]> CALL p(); Empty set (0.003 sec) we easily can predict it.

Igor Babaev (Inactive) added a comment - 2024-12-28 04:37 Summing up a full solution for this bug requires a resolution of the problem of wrong re-calculation of used_tables_cache for subqueries with outer references. Such re-calculation has to be done after any permanent transformation. The problem is reported in MDEV-32294.

Rex Johnston added a comment - 2025-01-16 20:51 With the latest patch from https://github.com/MariaDB/server/tree/MDEV-32294-Work_In_Progress https://github.com/MariaDB/server/commit/f8e2caaa4036cece9368831d15670594a7e2e1d1 We have this MariaDB [test]> CREATE PROCEDURE p() -> SELECT dt.x -> FROM -> ( -> SELECT dt1.x -> FROM -> ( -> SELECT dt2.x -> FROM -> ( -> SELECT t1.x FROM t1 WHERE t1.x < 2 GROUP BY t1.x -> ) dt2 -> ) dt1 -> WHERE (SELECT t2.a FROM t2 WHERE (1 IN (SELECT dt1.x)) AND t2.a < 2) != 0 -> GROUP BY dt1.x -> ) dt; Query OK, 0 rows affected (0.031 sec)   MariaDB [test]> call p(); +------+ | x | +------+ | 1 | +------+ 1 row in set (0.012 sec)   Query OK, 0 rows affected (0.013 sec)   MariaDB [test]> call p(); +------+ | x | +------+ | 1 | +------+ 1 row in set (0.009 sec) I'm still investigating some wrong results from queries i've detailed in MDEV-35859