Sergei Golubchik added a comment - 2025-02-04 09:04 The fix is pushed and released, so this issue is closed with specific fix versions. A followup is moved into MDEV-36021 . Please, feel free to add more content to MDEV-36021 description

Igor Babaev (Inactive) added a comment - 2025-01-16 21:02 - edited The query in the last test case uses degenerated subquery with a window function with ORDER BY list that is eliminated. If we use a FROM clause for the subquery the query is executed without any problems returning the expected result: MariaDB [test]> CREATE TABLE t2 (b int); Query OK, 0 rows affected (0.019 sec)   MariaDB [test]> INSERT INTO t2 VALUES (4); Query OK, 1 row affected (0.013 sec)   MariaDB [test]> SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a))) FROM t2); +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.006 sec) If we set a breakpoint in the function JOIN::make_aggr_tables_info() at the code: /* Change sum_fields reference to calculated fields in tmp_table */ items1= ref_ptr_array_slice(2); if ((sort_and_group || curr_tab->table->group || tmp_table_param.precomputed_group_by) && !implicit_grouping_without_tables) { if (change_to_use_tmp_fields(thd, items1, tmp_fields_list1, tmp_all_fields1, fields_list.elements, all_fields)) DBUG_RETURN(true); } else { if (change_refs_to_tmp_fields(thd, items1, tmp_fields_list1, tmp_all_fields1, fields_list.elements, all_fields)) DBUG_RETURN(true); } we see that for the query SELECT * FROM t1 WHERE EXISTS( SELECT avg (3) OVER ( ORDER BY COUNT ( DISTINCT t1.a , hex(t1.a)))); the 'else' branch with a call of change_refs_to_tmp_fields() is used while for the query SELECT * FROM t1 WHERE EXISTS( SELECT avg (3) OVER ( ORDER BY COUNT ( DISTINCT t1.a , hex(t1.a))) FROM t2); the 'then' branch is used with a call of change_to_use_tmp_fields() Let's use the call of change_to_use_tmp_fields() for the first query with the following diff: if ((sort_and_group || curr_tab->table->group || tmp_table_param.precomputed_group_by) && - !implicit_grouping_without_tables) + (!implicit_grouping_without_tables || + (implicit_grouping_with_window_funcs && + !select_lex->table_list.elements))) { if (change_to_use_tmp_fields(thd, items1, After having applied this patch I have: MariaDB [test]> SELECT * FROM t1 WHERE EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))); +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.003 sec) This is the expected result. However for the query SELECT * FROM t1 WHERE (SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))) = 3; as well as for the query SELECT * FROM t1 WHERE ( SELECT avg (3) OVER ()) = 3; we don't have expected results: MariaDB [test]> SELECT * FROM t1 WHERE (SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a)))) = 3; Empty set (0.001 sec) MariaDB [test]> SELECT * FROM t1 WHERE (SELECT avg(3) OVER ()) = 3; Empty set (0.000 sec) Indeed MariaDB [test]> SELECT avg(3) OVER (); +----------------+ | avg(3) OVER () | +----------------+ | 3.0000 | +----------------+ 1 row in set (0.001 sec) This because of another bug concerning degenerated subqueries with window functions noticed in the previous comments. This bug is reported in MDEV-35869 . It makes sense to fix MDEV-35869 first.

Igor Babaev (Inactive) added a comment - 2025-01-16 18:01 Before the patch the same crash can be reproduced with the following test case as well: CREATE TABLE t1 (a int DEFAULT 2); INSERT INTO t1 VALUES (1), (2); SELECT * FROM t1 WHERE EXISTS( SELECT avg (3) OVER ( ORDER BY COUNT ( DISTINCT t1.a , hex(t1.a))));

Oleg Smirnov added a comment - 2025-01-16 03:35 The crash for the query is eliminated, however it was found that the query itself is not valid. I filed MDEV-35846 for addressing that.

Igor Babaev (Inactive) added a comment - 2025-01-15 21:57 For the build just before the pushed patch we have: MariaDB [test]> CREATE TABLE t1 (a int DEFAULT 2); Query OK, 0 rows affected (0.014 sec) MariaDB [test]> INSERT INTO t1 VALUES (1), (2); Query OK, 2 rows affected (0.007 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.001 sec) MariaDB [test]> INSERT INTO t1 SELECT avg(4); Query OK, 1 row affected (0.004 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | +------+ 3 rows in set (0.001 sec) MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (); Query OK, 1 row affected (0.003 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | +------+ 4 rows in set (0.001 sec) MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4))); Query OK, 1 row affected (0.004 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | +------+ 5 rows in set (0.003 sec) MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER ())); Query OK, 1 row affected (0.005 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | +------+ 6 rows in set (0.002 sec) MariaDB [test]> SELECT avg(4) OVER (); +----------------+ | avg(4) OVER () | +----------------+ | 4.0000 | +----------------+ 1 row in set (0.001 sec) MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (ORDER BY t1.a); ERROR 1109 (42S02): Unknown table 't1' in order clause MariaDB [test]> INSERT INTO t1 SELECT avg(4) ORDER BY t1.a; ERROR 1109 (42S02): Unknown table 't1' in order clause MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) ORDER BY t1.a)); Query OK, 1 row affected (0.005 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | +------+ 7 rows in set (0.001 sec) MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER (ORDER BY t1.a))); Query OK, 1 row affected (0.005 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | +------+ 8 rows in set (0.002 sec) MariaDB [test]> CREATE TABLE t2 (b int); Query OK, 0 rows affected (0.018 sec) MariaDB [test]> INSERT INTO t2 VALUES (2); Query OK, 1 row affected (0.006 sec) MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER () FROM t2; Query OK, 1 row affected (0.005 sec) Records: 1 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | | 4 | +------+ 9 rows in set (0.002 sec) MariaDB [test]> INSERT INTO t1 SELECT avg(4) OVER (ORDER BY t1.a) FROM t2; ERROR 1054 (42S22): Unknown column 't1.a' in 'order clause' MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER () FROM t2)); Query OK, 1 row affected (0.006 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | | 4 | | 4 | +------+ 10 rows in set (0.002 sec) MariaDB [test]> INSERT INTO t1 SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a))); ERROR 1054 (42S22): Unknown column 'a' in 'order clause' MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a))))); Query OK, 1 row affected (0.005 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | | 4 | | 4 | | NULL | +------+ 11 rows in set (0.002 sec) MariaDB [test]> INSERT INTO t1 VALUES(EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT a , hex(a))))); ERROR 2013 (HY000): Lost connection to MySQL server during query MariaDB [test]> INSERT INTO t1 VALUES(EXISTS(SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a))))); ERROR 2013 (HY000): Lost connection to MySQL server during query MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(3) OVER (ORDER BY COUNT( DISTINCT t1.a , hex(t1.a))))); Query OK, 1 row affected (0.015 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | | 4 | | 4 | | NULL | | NULL | +------+ 12 rows in set (0.002 sec) MariaDB [test]> INSERT INTO t1 SELECT * FROM t2 WHERE (SELECT avg(t1.a) FROM t1 t) > 0; ERROR 1054 (42S22): Unknown column 't1.a' in 'field list' MariaDB [test]> INSERT INTO t1 VALUES(( SELECT * FROM t2 WHERE (SELECT avg(t1.a) FROM t1 t) > 0)); Query OK, 1 row affected (0.005 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | | 4 | | 4 | | NULL | | NULL | | 2 | +------+ 13 rows in set (0.002 sec) MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (VALUES((SELECT avg(3) FROM t2))) dt) > 0)); Query OK, 1 row affected (0.004 sec) MariaDB [test]> SELECT * FROM t1; +------+ | a | +------+ | 1 | | 2 | | 4 | | 4 | | 4 | | NULL | | 4 | | NULL | | 4 | | 4 | | NULL | | NULL | | 2 | | 2 | +------+ 14 rows in set (0.001 sec) MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (VALUES((SELECT avg(t1.a) FROM t2))) dt) > 0)); ERROR 1054 (42S22): Unknown column 't1.a' in 'field list' MariaDB [test]> INSERT INTO t1 VALUES((SELECT t2.b FROM t2 WHERE (SELECT 1 FROM (SELECT avg(t1.a) FROM t2) dt) > 0)); ERROR 1054 (42S22): Unknown column 't1.a' in 'field list'