Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Description
Consider the following simplified test case:
MariaDB [test]> select version();
|
+-----------------+
|
| version() |
|
+-----------------+
|
| 10.6.14-MariaDB |
|
+-----------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> drop table if exists div_req_sel_w_scores;
|
Query OK, 0 rows affected (0.130 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE div_req_sel_w_scores (
|
-> request_id int(11) NOT NULL,
|
-> score_value int(11) NOT NULL,
|
-> net_names int(11) not null,
|
-> PRIMARY KEY (request_id, score_value)
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
|
Query OK, 0 rows affected (0.090 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> drop table if exists div_mdl_seg;
|
Query OK, 0 rows affected (0.084 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE div_mdl_seg (
|
-> model_id int(11) NOT NULL,
|
-> seg_id smallint(6) NOT NULL,
|
-> low_score int(11) NOT NULL,
|
-> PRIMARY KEY (model_id, seg_id)
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
|
Query OK, 0 rows affected (0.074 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> drop table if exists div_req_sel;
|
Query OK, 0 rows affected (0.088 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE TABLE div_req_sel (
|
-> request_id int(11) NOT NULL AUTO_INCREMENT,
|
-> subsystem_id int(11) NOT NULL,
|
-> model_id int(11) DEFAULT NULL,
|
-> PRIMARY KEY (request_id, subsystem_id) USING BTREE
|
-> ) ENGINE=InnoDB AUTO_INCREMENT=1006382 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC;
|
Query OK, 0 rows affected (0.085 sec)
|
|
|
MariaDB [test]>
|
MariaDB [test]>
|
MariaDB [test]> insert into div_req_sel
|
-> (request_id, subsystem_id, model_id)
|
-> values
|
-> (1462687, 4, 118620);
|
Query OK, 1 row affected (0.004 sec)
|
|
|
MariaDB [test]> insert into div_mdl_seg
|
-> (model_id, seg_id, low_score)
|
-> values
|
-> (118620, 1, 50),
|
-> (118620, 2, 30);
|
Query OK, 2 rows affected (0.004 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> insert into div_req_sel_w_scores
|
-> (request_id, score_value, net_names)
|
-> values
|
-> (1462687,-30, 351242),
|
-> (1462687,-29, 2090304);
|
Query OK, 2 rows affected (0.003 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select @@sql_mode;
|
+-------------------------------------------------------------------------------------------+
|
| @@sql_mode |
|
+-------------------------------------------------------------------------------------------+
|
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
+-------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT a.seg_id, s.score_value, s.net_names
|
"> FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
|
"> LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
|
"> FROM div_mdl_seg dms
|
"> JOIN div_req_sel rs USING (model_id)
|
"> WHERE request_id = 1462687
|
"> ) a
|
"> LEFT JOIN div_req_sel_w_scores s
|
"> ON ( s.request_id = a.request_id
|
"> AND a.low_score_inclusive <= s.score_value
|
"> AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
|
"> WHERE a.low_score_inclusive >=
|
"> (SELECT MIN(score_value)
|
"> FROM div_req_sel_w_scores
|
"> WHERE request_id = 1462687)
|
"> ORDER BY s.score_value DESC
|
"> ";
|
Query OK, 0 rows affected (0.001 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
+--------+-------------+-----------+
|
| seg_id | score_value | net_names |
|
+--------+-------------+-----------+
|
| 1 | NULL | NULL |
|
| 2 | NULL | NULL |
|
+--------+-------------+-----------+
|
2 rows in set (0.002 sec)
|
|
|
MariaDB [test]> EXECUTE stmt;
|
+--------+-------------+-----------+
|
| seg_id | score_value | net_names |
|
+--------+-------------+-----------+
|
| 1 | NULL | NULL |
|
| 2 | NULL | NULL |
|
+--------+-------------+-----------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [test]> set session sql_mode = "ONLY_FULL_GROUP_BY";
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 2013 (HY000): Lost connection to server during query
|
We get a crash with a backtrace similar to the following (this is from macOS, but the bug is repeatable on Linux with official binaries, including ES 10.6.12-7 as well):
2023-05-17 10:13:31 0 [Note] /Users/Valerii/dbs/maria10.6/bin/mariadbd: ready for connections.
|
Version: '10.6.14-MariaDB' socket: '/tmp/mysql.sock' port: 3306 MariaDB Server
|
2023-05-17 10:13:31 0 [Note] InnoDB: Buffer pool(s) load completed at 230517 10:13:31
|
Yuliyas-Air:maria10.6 Valerii$ tail -110 data/Yuliyas-Air.err
|
diagnose the problem, but since we have already crashed,
|
something is definitely wrong and this may fail.
|
|
|
Server version: 10.6.14-MariaDB source revision: afe44ef2126d74c11407266cf7d0225ac0392e61
|
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 = 467973 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x7faf23833818
|
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 = 0x70000905ee70 thread_stack 0x49000
|
Printing to addr2line failed
|
0 mariadbd 0x000000010afb726c my_print_stacktrace + 60
|
0 mariadbd 0x000000010a750094 handle_fatal_signal + 740
|
0 libsystem_platform.dylib 0x00007fff64d90f5a _sigtramp + 26
|
0 ??? 0x0000000000000000 0x0 + 0
|
0 mariadbd 0x000000010a7c0acb _ZN9Item_func10fix_fieldsEP3THDPP4Item + 139
|
0 mariadbd 0x000000010a94ae8a _Z13setup_on_exprP3THDP10TABLE_LISTb + 586
|
0 mariadbd 0x000000010a94b0f0 _Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item + 400
|
0 mariadbd 0x000000010a9ff645 _ZN4JOIN7prepareEP10TABLE_LISTP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit + 1717
|
0 mariadbd 0x000000010aaa09fc _ZN18st_select_lex_unit12prepare_joinEP3THDP13st_select_lexP13select_resultyb + 396
|
0 mariadbd 0x000000010aa9c75a _ZN18st_select_lex_unit7prepareEP10TABLE_LISTP13select_resulty + 1322
|
0 mariadbd 0x000000010a9771c0 _ZL21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST + 640
|
0 mariadbd 0x000000010a978eeb _Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj + 171
|
0 mariadbd 0x000000010aaca4bc _ZN10TABLE_LIST14handle_derivedEP3LEXj + 140
|
0 mariadbd 0x000000010a99b6cf _ZN13st_select_lex14handle_derivedEP3LEXj + 63
|
0 mariadbd 0x000000010a9ff1f0 _ZN4JOIN7prepareEP10TABLE_LISTP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit + 608
|
0 mariadbd 0x000000010a9fb292 _Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex + 546
|
0 mariadbd 0x000000010a9faf4e _Z13handle_selectP3THDP3LEXP13select_resultm + 238
|
0 mariadbd 0x000000010a9c614f _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 1231
|
0 mariadbd 0x000000010a9bf8e9 _Z21mysql_execute_commandP3THDb + 2617
|
0 mariadbd 0x000000010a9ec7c1 _ZN18Prepared_statement7executeEP6Stringb + 993
|
0 mariadbd 0x000000010a9e76c9 _ZN18Prepared_statement12execute_loopEP6StringbPhS2_ + 201
|
0 mariadbd 0x000000010a9e74dd _Z22mysql_sql_stmt_executeP3THD + 413
|
0 mariadbd 0x000000010a9c2199 _Z21mysql_execute_commandP3THDb + 13033
|
0 mariadbd 0x000000010a9bb11b _Z11mysql_parseP3THDPcjP12Parser_state + 731
|
0 mariadbd 0x000000010a9b8ffb _Z16dispatch_command19enum_server_commandP3THDPcjb + 3899
|
0 mariadbd 0x000000010a9bb6d7 _Z10do_commandP3THDb + 1031
|
0 mariadbd 0x000000010ab0828e _Z24do_handle_one_connectionP7CONNECTb + 430
|
0 mariadbd 0x000000010ab07fab handle_one_connection + 91
|
0 mariadbd 0x000000010acaaa89 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 (0x7faf24028418): SELECT a.seg_id, s.score_value, s.net_names
|
FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
|
LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
|
FROM div_mdl_seg dms
|
JOIN div_req_sel rs USING (model_id)
|
WHERE request_id = 1462687
|
) a
|
LEFT JOIN div_req_sel_w_scores s
|
ON ( s.request_id = a.request_id
|
AND a.low_score_inclusive <= s.score_value
|
AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
|
WHERE a.low_score_inclusive >=
|
(SELECT MIN(score_value)
|
FROM div_req_sel_w_scores
|
WHERE request_id = 1462687)
|
ORDER BY s.score_value DESC
|
|
|
Connection ID (thread ID): 3
|
Status: NOT_KILLED
|
Note that crash happens upon the first execution as well, but does NOT happen for normal, non-prepared statement. In a fresh session:
MariaDB [test]> set session sql_mode = "ONLY_FULL_GROUP_BY";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select @@sql_mode, version();
|
+--------------------+-----------------+
|
| @@sql_mode | version() |
|
+--------------------+-----------------+
|
| ONLY_FULL_GROUP_BY | 10.6.14-MariaDB |
|
+--------------------+-----------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> SELECT a.seg_id, s.score_value, s.net_names
|
-> FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
|
-> LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
|
-> FROM div_mdl_seg dms
|
-> JOIN div_req_sel rs USING (model_id)
|
-> WHERE request_id = 1462687
|
-> ) a
|
-> LEFT JOIN div_req_sel_w_scores s
|
-> ON ( s.request_id = a.request_id
|
-> AND a.low_score_inclusive <= s.score_value
|
-> AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
|
-> WHERE a.low_score_inclusive >=
|
-> (SELECT MIN(score_value)
|
-> FROM div_req_sel_w_scores
|
-> WHERE request_id = 1462687)
|
-> ORDER BY s.score_value DESC;
|
+--------+-------------+-----------+
|
| seg_id | score_value | net_names |
|
+--------+-------------+-----------+
|
| 1 | NULL | NULL |
|
| 2 | NULL | NULL |
|
+--------+-------------+-----------+
|
2 rows in set (0.001 sec)
|
|
|
MariaDB [test]> PREPARE stmt FROM "
|
"> SELECT a.seg_id, s.score_value, s.net_names
|
"> FROM (SELECT rs.request_id, seg_id, low_score AS low_score_inclusive,
|
"> LEAD(low_score) OVER (PARTITION BY NULL ORDER BY seg_id) AS high_score_exclusive
|
"> FROM div_mdl_seg dms
|
"> JOIN div_req_sel rs USING (model_id)
|
"> WHERE request_id = 1462687
|
"> ) a
|
"> LEFT JOIN div_req_sel_w_scores s
|
"> ON ( s.request_id = a.request_id
|
"> AND a.low_score_inclusive <= s.score_value
|
"> AND IFNULL(a.high_score_exclusive, 9999999) > s.score_value)
|
"> WHERE a.low_score_inclusive >=
|
"> (SELECT MIN(score_value)
|
"> FROM div_req_sel_w_scores
|
"> WHERE request_id = 1462687)
|
"> ORDER BY s.score_value DESC
|
"> ";
|
Query OK, 0 rows affected (0.001 sec)
|
Statement prepared
|
|
|
MariaDB [test]> EXECUTE stmt;
|
ERROR 2013 (HY000): Lost connection to server during query
|
The same query worked well as a prepared statement in 10.5.15, for example, so it's some kind of a regression in (at least recent) 10.6.
Attachments
Issue Links
- relates to
-
-
- Closed
-