|
Consider the following EXPLAIN for the query referencing to CTE d columns as d.*:
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
|
This query works as expected (depending on sql_mode) on 10.6.13 or current code, for example:
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
|
but on 10.6.12 and other affected versions we get a crash:
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
|
with the following stack trace (macOS here):
...
|
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
|
...
|
If we reference proper columns of CTE explicitly, everything works, though:
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)
|
but if we reference d.p, we crash again the same way:
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
|
As I can not reproduce the crash on 10.6.13 the bug is fixed somehow, but I can not find any existing MDEV based on the stack trace, so it may be just by chance and the problem may be re-introduced later. Hence this bug report.
|