Crash caused by query with aggregation over materialized derived

WITH demo AS (

  SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,

         FLOOR(RAND() * 5) AS p

    FROM seq_100_to_105 seq1

         JOIN seq_10_to_15 seq2

         JOIN seq_1_to_5 seq3

)

SELECT d.*, SUM(p)

  FROM demo d

Yuliyas-Air:maria10.6 Valerii$ bin/mysql test

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 32

Server version: 10.6.14-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> explain extended

    -> WITH demo AS (

    ->   SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,

    ->          FLOOR(RAND() * 5) AS p

    ->     FROM seq_100_to_105 seq1

    ->          JOIN seq_10_to_15 seq2

    ->          JOIN seq_1_to_5 seq3

    -> )

    -> SELECT d.*, SUM(p)

    ->   FROM demo d;

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------------+

| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                  |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------------+

|    1 | PRIMARY     |  | ALL   | NULL          | NULL    | NULL    | NULL | 180  |   100.00 |                                                        |

|    2 | DERIVED     | seq3       | index | NULL          | PRIMARY | 8       | NULL | 5    |   100.00 | Using index                                            |

|    2 | DERIVED     | seq1       | index | NULL          | PRIMARY | 8       | NULL | 6    |   100.00 | Using index; Using join buffer (flat, BNL join)        |

|    2 | DERIVED     | seq2       | index | NULL          | PRIMARY | 8       | NULL | 6    |   100.00 | Using index; Using join buffer (incremental, BNL join) |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------------+

4 rows in set, 1 warning (0.061 sec)

MariaDB [test]> set session sql_mode = "ONLY_FULL_GROUP_BY";

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> explain extended WITH demo AS (   SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,          FLOOR(RAND() * 5) AS p     FROM seq_100_to_105 seq1          JOIN seq_10_to_15 seq2          JOIN seq_1_to_5 seq3 ) SELECT d.*, SUM(p)   FROM demo d;

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Yuliyas-Air:maria10.6.12 Valerii$ bin/mysql test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 10.6.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> set sql_mode = 'ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected (0.005 sec)

MariaDB [test]> WITH demo AS (

    ->   SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,

    ->          FLOOR(RAND() * 5) AS p

    ->     FROM seq_100_to_105 seq1

    ->          JOIN seq_10_to_15 seq2

    ->          JOIN seq_1_to_5 seq3

    -> )

    -> SELECT d.*, SUM(p)

    ->   FROM demo d;

ERROR 2013 (HY000): Lost connection to server during query

...

2023-05-18 21:21:55 0 [Note] /Users/Valerii/dbs/maria10.6.12/bin/mariadbd: ready for connections.

Version: '10.6.12-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  MariaDB Server

230519  7:10:13 [ERROR] mysqld got signal 11 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

Server version: 10.6.12-MariaDB source revision: 4c79e15cc3716f69c044d4287ad2160da8101cdc

key_buffer_size=134217728

read_buffer_size=131072

max_used_connections=1

max_threads=153

thread_count=1

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467971 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fc0f58dd418

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 0x700004f28e70 thread_stack 0x49000

Printing to addr2line failed

0   mariadbd                            0x000000011045093c my_print_stacktrace + 60

0   mariadbd                            0x000000010fbf5844 handle_fatal_signal + 740

0   libsystem_platform.dylib            0x00007fff64d90f5a _sigtramp + 26

0   ???                                 0x0000000000000000 0x0 + 0

0   mariadbd                            0x000000010feb021b _ZN4JOIN21make_aggr_tables_infoEv + 3947

0   mariadbd                            0x000000010fea47db _ZN4JOIN15optimize_stage2Ev + 17995

0   mariadbd                            0x000000010fea7de5 _ZN4JOIN14optimize_innerEv + 7525

0   mariadbd                            0x000000010fe99b58 _Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex + 1144

0   mariadbd                            0x000000010fe995be _Z13handle_selectP3THDP3LEXP13select_resultm + 238

0   mariadbd                            0x000000010fe64a2f _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 1231

0   mariadbd                            0x000000010fe5e239 _Z21mysql_execute_commandP3THDb + 2617

0   mariadbd                            0x000000010fe59a6b _Z11mysql_parseP3THDPcjP12Parser_state + 731

0   mariadbd                            0x000000010fe5794b _Z16dispatch_command19enum_server_commandP3THDPcjb + 3899

0   mariadbd                            0x000000010fe5a027 _Z10do_commandP3THDb + 1031

0   mariadbd                            0x000000010ffa4a8e _Z24do_handle_one_connectionP7CONNECTb + 430

0   mariadbd                            0x000000010ffa47ab handle_one_connection + 91

0   mariadbd                            0x0000000110145c99 pfs_spawn_thread + 217

0   libsystem_pthread.dylib             0x00007fff64d9a661 _pthread_body + 340

0   libsystem_pthread.dylib             0x00007fff64d9a50d _pthread_body + 0

0   libsystem_pthread.dylib             0x00007fff64d99bf9 thread_start + 13

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0x7fc0f48be830): WITH demo AS (

  SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,

         FLOOR(RAND() * 5) AS p

    FROM seq_100_to_105 seq1

         JOIN seq_10_to_15 seq2

         JOIN seq_1_to_5 seq3

)

SELECT d.*, SUM(p)

  FROM demo d

Connection ID (thread ID): 4

Status: NOT_KILLED

...

MariaDB [test]> explain extended

    -> WITH demo AS (

    ->   SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,

    ->          FLOOR(RAND() * 5) AS p

    ->     FROM seq_100_to_105 seq1

    ->          JOIN seq_10_to_15 seq2

    ->          JOIN seq_1_to_5 seq3

    -> )

    -> SELECT d.dim1, d.dim2, d.dim3, SUM(p)

    ->   FROM demo d;

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------------+

| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                  |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------------+

|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL | 180  |   100.00 |                                                        |

|    2 | DERIVED     | seq3       | index | NULL          | PRIMARY | 8       | NULL | 5    |   100.00 | Using index                                            |

|    2 | DERIVED     | seq1       | index | NULL          | PRIMARY | 8       | NULL | 6    |   100.00 | Using index; Using join buffer (flat, BNL join)        |

|    2 | DERIVED     | seq2       | index | NULL          | PRIMARY | 8       | NULL | 6    |   100.00 | Using index; Using join buffer (incremental, BNL join) |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------------+

4 rows in set, 1 warning (0.005 sec)

MariaDB [test]> explain extended WITH demo AS (   SELECT seq1.seq AS dim1, seq2.seq AS dim2, seq3.seq AS dim3,          FLOOR(RAND() * 5) AS p     FROM seq_100_to_105 seq1          JOIN seq_10_to_15 seq2          JOIN seq_1_to_5 seq3 ) SELECT d.dim1, d.dim2, d.dim3, d.p, SUM(p)   FROM demo d;

ERROR 2013 (HY000): Lost connection to server during query