[MDEV-29681] Server crashes when optimizing SQL with ORDER BY Created: 2022-10-02  Updated: 2023-12-20

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer, Server
Affects Version/s: 10.6.7, 10.3, 10.4, 10.6.10, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Critical
Reporter: Yevgeny Kosarzhevsky Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: crash
Environment:

CentOS Linux release 7.9.2009 (Core)
Linux sql1 3.10.0-1160.76.1.el7.x86_64 #1 SMP Wed Aug 10 16:21:17 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

Attachments: Text File gdb.txt     Text File mariadb.log    
Issue Links:
Duplicate
is duplicated by MDEV-31197 Server crash on (SELECT ... INNER JOI... Closed
is duplicated by MDEV-32154 MariaDB server crashes in get_sort_b... Closed
is duplicated by MDEV-32426 Segmentation fault at /mariadb-11.3.0... Closed
is duplicated by MDEV-32872 mysqld got signal 11 Closed
Relates
relates to MDEV-28501 SIGSEGV in update_depend_map_for_orde... Closed
relates to MDEV-29935 Server crashes in get_sort_by_table/m... Confirmed

 Description   

Hello.

MariaDB crashes with the following scenario:
Default installation on centos7 from mariadb yum repo.

CREATE DATABASE d;
 
USE d;
 
CREATE TABLE `vohgajoo` (
 
  `ohtighit` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `heipeeri` datetime DEFAULT NULL,
 
  `yingaexe` datetime DEFAULT NULL,
 
  `itahweit` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 
  `ooghohva` varchar(12) DEFAULT NULL,
 
  `iedacoox` tinytext DEFAULT NULL,
 
  `meingeic` enum('m','f','t') DEFAULT NULL,
 
  `quauxeej` date DEFAULT NULL,
 
  `ahmaepao` varchar(16) DEFAULT NULL,
 
  `chiavahn` float DEFAULT NULL,
 
  `eebiavit` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`eebiavit`)),
 
  `theibaej` bit(1) NOT NULL DEFAULT b'0',
 
  PRIMARY KEY (`ohtighit`),
 
  KEY `itahweit` (`itahweit`(12)),
 
  KEY `ooghohva` (`ooghohva`)
 
) ENGINE=InnoDB AUTO_INCREMENT=381603 DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `imaewaey` (
 
  `angooyie` int(11) NOT NULL,
 
  `binaeshe` int(11) NOT NULL,
 
  `itizahra` varchar(5) NOT NULL DEFAULT 'en',
 
  `aithixis` int(11) NOT NULL,
 
  `yingaexe` datetime NOT NULL,
 
  PRIMARY KEY (`angooyie`,`binaeshe`,`itizahra`),
 
  KEY `hieghieg` (`angooyie`,`binaeshe`,`aithixis`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
 
 
 
(SELECT bx.itahweit, v.aithixis/2 aithixis, 2 angooyie, JSON_UNQUOTE(JSON_EXTRACT(eebiavit, '$.claumexf')) claumexf FROM d.vohgajoo bx LEFT JOIN d.imaewaey v ON v.itizahra='en' AND v.angooyie=2 AND v.binaeshe=bx.ohtighit WHERE bx.ohtighit IN (4985) AND bx.theibaej=1 ORDER BY bx.meingeic IN ('f','t') DESC, JSON_EXTRACT(bx.eebiavit, '$.balmrusb') DESC LIMIT 1) ORDER BY angooyie=16, aithixis DESC;



 Comments   
Comment by Yevgeny Kosarzhevsky [ 2022-10-02 ]

I am getting this on 10.6.7.
Confirmed on test environment on 10.6.10 too. Looks like the problem in 'ORDER BY angooyie=16' part.

Comment by Alice Sherepa [ 2022-10-04 ]

Thanks for the report!
I repeated on 10.3-10.10 with InnoDB engine:

--source include/have_innodb.inc 
 
 
CREATE TABLE t1 (b int) engine=innodb;
 
CREATE TABLE t2 (a int, c int) engine=innodb;
 
insert into t1 values (1),(2),(3); #not important
 
insert into t2 values (1,1),(2,2),(3,3); #not important
 
 
 
(SELECT * FROM t1 JOIN t2 ON a=b LIMIT 5) ORDER BY a=16;

10.3 c0817dac99c28698dfc2b548d89

 
Version: '10.3.37-MariaDB-debug-log' 
221004 12:47:39 [ERROR] mysqld got signal 11 ;
 
 
 
sql/signal_handler.cc:231(handle_fatal_signal)[0x55e0cef20cbc]
 
sigaction.c:0(__restore_rt)[0x7f010f4b2420]
 
sql/sql_select.cc:24221(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55e0ce8fecd8]
 
sql/sql_select.cc:4964(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55e0ce875361]
 
sql/sql_select.cc:1992(JOIN::optimize_inner())[0x55e0ce85721c]
 
sql/sql_select.cc:1534(JOIN::optimize())[0x55e0ce8526f7]
 
sql/sql_select.cc:4360(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55e0ce86fdd1]
 
sql/sql_union.cc:1685(st_select_lex_unit::exec())[0x55e0cea48b4f]
 
sql/sql_union.cc:42(mysql_union(THD*, LEX*, select_result*, st_select_lex_unit*, unsigned long))[0x55e0cea37240]
 
sql/sql_select.cc:362(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e0ce845dde]
 
sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e0ce7b6d83]
 
sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x55e0ce7a4db8]
 
sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e0ce7c0a64]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e0ce7979e1]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x55e0ce794500]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55e0ceb696bb]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x55e0ceb68f75]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e0d019b07f]
 
nptl/pthread_create.c:478(start_thread)[0x7f010f4a6609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f010f3cb133]
 
 
 
Query (0x62b000000290): (SELECT * FROM t1 JOIN t2 ON a=b LIMIT 5) ORDER BY a=16


--source include/have_innodb.inc 
 
 
CREATE TABLE t1 (b int) engine=innodb;
 
CREATE TABLE t2 (a int, c int) engine=innodb;
 
 
 
(SELECT * FROM t1 JOIN t2 ON a=b LIMIT 5) ORDER BY a=16,  b;

10.3 c0817dac99c28698dfc2b548d89

 
Version: '10.3.37-MariaDB-debug-log' 
221004 12:46:06 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.37-MariaDB-debug-log
 
 
 
sql/signal_handler.cc:231(handle_fatal_signal)[0x55bbc6e5dcbc]
 
sigaction.c:0(__restore_rt)[0x7fbefcab7420]
 
sql/sql_select.cc:13446(update_depend_map_for_order(JOIN*, st_order*))[0x55bbc67ee9d9]
 
sql/sql_select.cc:13526(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x55bbc67ef012]
 
sql/sql_select.cc:2262(JOIN::optimize_stage2())[0x55bbc6796ef8]
 
sql/sql_select.cc:2018(JOIN::optimize_inner())[0x55bbc67944c4]
 
sql/sql_select.cc:1534(JOIN::optimize())[0x55bbc678f6f7]
 
sql/sql_select.cc:4360(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55bbc67acdd1]
 
sql/sql_union.cc:1685(st_select_lex_unit::exec())[0x55bbc6985b4f]
 
sql/sql_union.cc:42(mysql_union(THD*, LEX*, select_result*, st_select_lex_unit*, unsigned long))[0x55bbc6974240]
 
sql/sql_select.cc:362(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55bbc6782dde]
 
sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55bbc66f3d83]
 
sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x55bbc66e1db8]
 
sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55bbc66fda64]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55bbc66d49e1]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x55bbc66d1500]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55bbc6aa66bb]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x55bbc6aa5f75]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55bbc80d807f]
 
nptl/pthread_create.c:478(start_thread)[0x7fbefcaab609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fbefc9d0133]
 
 
 
Query (0x62b000000290): (SELECT * FROM t1 JOIN t2 ON a=b LIMIT 5) ORDER BY a=16,  b

Comment by Alice Sherepa [ 2023-03-01 ]

test case from MDEV-30756:

CREATE TABLE t1 (id int);
 
 
 
SELECT * FROM t1 k WHERE 1 IN
 
  (SELECT  1 FROM t1 WHERE EXISTS (SELECT id  FROM (SELECT 1 FROM t1) d GROUP BY  (SELECT 1 FROM t1 dt HAVING id) BETWEEN 0 AND 10 HAVING 1)) ;


230301 10:30:51 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.38-MariaDB-debug-log source revision: 2743a510a156456fe57429032bf41c0da0f11198
 
 
 
sql/signal_handler.cc:233(handle_fatal_signal)[0x55d84d648c37]
 
sigaction.c:0(__restore_rt)[0x7fdd8f271420]
 
sql/sql_select.cc:24262(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55d84d025788]
 
sql/sql_select.cc:4983(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55d84cf9bdcb]
 
sql/sql_select.cc:2000(JOIN::optimize_inner())[0x55d84cf7dbac]
 
sql/sql_select.cc:1542(JOIN::optimize())[0x55d84cf79086]
 
sql/sql_lex.cc:4107(st_select_lex::optimize_unflattened_subqueries(bool))[0x55d84ce6bb72]
 
sql/opt_subselect.cc:5412(JOIN::optimize_unflattened_subqueries())[0x55d84d37e3d9]
 
sql/sql_select.cc:2774(JOIN::optimize_stage2())[0x55d84cf8570c]
 
sql/sql_select.cc:2026(JOIN::optimize_inner())[0x55d84cf7de54]
 
sql/sql_select.cc:1542(JOIN::optimize())[0x55d84cf79086]
 
sql/sql_select.cc:4379(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55d84cf9683b]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55d84cf6ca1f]
 
sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55d84cedd708]
 
sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x55d84cecb72e]
 
sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d84cee73ea]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d84cebe3a7]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x55d84cebaec6]
 
sql/sql_connect.cc:1404(do_handle_one_connection(CONNECT*))[0x55d84d29162d]
 
sql/sql_connect.cc:1310(handle_one_connection)[0x55d84d290ee7]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55d84e8c8201]
 
nptl/pthread_create.c:478(start_thread)[0x7fdd8f265609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7fdd8f18a133]
 
 
 
Query (0x62b000000290): SELECT * FROM t1 k WHERE 1 IN
 
(SELECT  1 FROM t1 WHERE EXISTS (SELECT id  FROM (SELECT 1 FROM t1) d GROUP BY  (SELECT 1 FROM t1 dt HAVING id) BETWEEN 0 AND 10 HAVING 1))


--source include/have_innodb.inc
 
CREATE TABLE t1 (id int) engine=innodb;
 
 
 
SELECT * FROM t1 k WHERE 1 IN
 
  (SELECT  1 FROM t1 WHERE EXISTS (SELECT id  FROM (SELECT 1 FROM t1 where id) d GROUP BY  (SELECT 1 FROM t1 dt HAVING id)  having 1 )) ;
 

230301 10:31:05 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.38-MariaDB-debug-log source revision: 2743a510a156456fe57429032bf41c0da0f11198
 
 
 
sql/signal_handler.cc:233(handle_fatal_signal)[0x5626a7925c37]
 
sigaction.c:0(__restore_rt)[0x7fa9c5b98420]
 
sql/sql_select.cc:13466(update_depend_map_for_order(JOIN*, st_order*))[0x5626a72b547a]
 
sql/sql_select.cc:13546(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x5626a72b5ac2]
 
sql/sql_select.cc:2430(JOIN::optimize_stage2())[0x5626a725f394]
 
sql/sql_select.cc:2026(JOIN::optimize_inner())[0x5626a725ae54]
 
sql/sql_select.cc:1542(JOIN::optimize())[0x5626a7256086]
 
sql/sql_lex.cc:4107(st_select_lex::optimize_unflattened_subqueries(bool))[0x5626a7148b72]
 
sql/opt_subselect.cc:5412(JOIN::optimize_unflattened_subqueries())[0x5626a765b3d9]
 
sql/sql_select.cc:2554(JOIN::optimize_stage2())[0x5626a7260932]
 
sql/sql_select.cc:2026(JOIN::optimize_inner())[0x5626a725ae54]
 
sql/sql_select.cc:1542(JOIN::optimize())[0x5626a7256086]
 
sql/sql_select.cc:4379(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5626a727383b]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5626a7249a1f]
 
sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5626a71ba708]
 
sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x5626a71a872e]
 
sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5626a71c43ea]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5626a719b3a7]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x5626a7197ec6]
 
sql/sql_connect.cc:1404(do_handle_one_connection(CONNECT*))[0x5626a756e62d]
 
sql/sql_connect.cc:1310(handle_one_connection)[0x5626a756dee7]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x5626a8ba5201]
 
nptl/pthread_create.c:478(start_thread)[0x7fa9c5b8c609]
 
 
 
Query (0x62b000000290): SELECT * FROM t1 k WHERE 1 IN
 
(SELECT  1 FROM t1 WHERE EXISTS (SELECT id  FROM (SELECT 1 FROM t1 where id) d GROUP BY  (SELECT 1 FROM t1 dt HAVING id)  having 1 ))

Comment by Alice Sherepa [ 2023-05-05 ]

test from MDEV-31197 - here crash repeatable with Myisam:

CREATE TABLE `tttttt` (`id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM character set utf8mb4;
 
CREATE TABLE `sssss` (`id` int(11) NOT NULL, `t` varchar(100) NOT NULL) ENGINE=MyISAM character set utf8mb4;
 
 
 
(SELECT `t` FROM `tttttt` a INNER JOIN `sssss` b ON a.id=b.id LIMIT 20) ORDER BY `t` COLLATE utf8mb4_general_ci DESC;


230505 11:00:25 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 11.0.2-MariaDB-debug-log source revision: 8d26537fbf20d249d65d4daba0394a8ff71e540e
 
 
 
mysys/stacktrace.c:215(my_print_stacktrace)[0x55be19f4a1c9]
 
sql/signal_handler.cc:241(handle_fatal_signal)[0x55be18b38890]
 
sigaction.c:0(__restore_rt)[0x7f9f6d102420]
 
sql/sql_select.cc:27595(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55be183e8794]
 
sql/sql_select.cc:5804(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55be1834ce29]
 
sql/sql_select.cc:2577(JOIN::optimize_inner())[0x55be1832c239]
 
sql/sql_select.cc:1905(JOIN::optimize())[0x55be18325311]
 
sql/sql_select.cc:5144(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55be18347085]
 
sql/sql_select.cc:616(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x55be18316ccf]
 
sql/sql_parse.cc:6279(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55be18236f80]
 
sql/sql_parse.cc:3949(mysql_execute_command(THD*, bool))[0x55be18225638]
 
sql/sql_parse.cc:8014(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55be18241dd4]
 
sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55be18217d1e]
 
sql/sql_parse.cc:1407(do_command(THD*, bool))[0x55be18214a4e]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55be186ecebf]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x55be186ec81c]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55be19321ba0]
 
nptl/pthread_create.c:478(start_thread)[0x7f9f6d0f6609]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f9f6ccc7133]
 
 
 
Query (0x6290001092a8): (SELECT `t` FROM `tttttt` a INNER JOIN `sssss` b ON a.id=b.id LIMIT 20) ORDER BY `t` COLLATE utf8mb4_general_ci DESC

Comment by Alice Sherepa [ 2023-09-13 ]

test case from MDEV-32154

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 ( j int) engine=innodb;
 
CREATE TABLE t2 ( i int) engine=innodb;
 
 
 
(SELECT j FROM t1,t2 GROUP BY i LOCK IN SHARE MODE ) ORDER BY i ;
 
drop table t1,t2;


Version: '10.4.32-MariaDB-debug-log'  
230913 10:57:42 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: d762e9d943aa444695ebe845a7376fd9cbb0e3dc
 
 
 
sql/signal_handler.cc:238(handle_fatal_signal)[0x55e828c3f771]
 
sigaction.c:0(__restore_rt)[0x7f5c1355c420]
 
sql/sql_select.cc:25348(get_sort_by_table(st_order*, st_order*, List<TABLE_LIST>&, unsigned long long))[0x55e8285ef2d9]
 
sql/sql_select.cc:5427(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55e82855fe17]
 
sql/sql_select.cc:2368(JOIN::optimize_inner())[0x55e8285407a3]
 
sql/sql_select.cc:1711(JOIN::optimize())[0x55e8285397b9]
 
sql/sql_select.cc:4812(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55e82855a7ab]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e82852b4b0]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e8284972ba]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55e828484a31]
 
sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e8284a0795]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e828476bbb]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55e8284736e6]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55e82888131b]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x55e828880bbf]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e82952ace8]
 
nptl/pthread_create.c:478(start_thread)[0x7f5c13550609]
 
 
 
Query (0x62b0000a1290): (SELECT j FROM t1,t2 GROUP BY i LOCK IN SHARE MODE ) ORDER BY i

Comment by Alice Sherepa [ 2023-10-12 ]

could you please also check the test case from MDEV-32395:

CREATE TABLE t0 ( c6 INT , c21 INT ) ;
 
INSERT INTO t0 VALUES ( 55 , -95 ) , ( 9 , 90 ) ;
 
ALTER TABLE t0 ADD COLUMN c37 INT AFTER c6 ;
 
INSERT INTO t0 VALUES ( ) , ( ) ;
 
SELECT t0 . c6 AS c42 FROM ( SELECT t0 . c6 = TRIM( TRAILING FROM 96 ) SOUNDS LIKE CONVERT ( t0 . c6 , UNSIGNED ) >> PI ( ) AS c49 FROM t0 ) AS t1 JOIN t0 ON RTRIM ( - RAND ( -66 ) BETWEEN FIND_IN_SET ( 20 , UNHEX ( -80 ) IS NULL OR IF ( 85 , -83 , -113 ) ) AND -125 ) / EXP ( c21 ) = t1 . c49 ORDER BY c42 , ( c42 + ( SELECT c21 AS c61 FROM t0 WHERE t0 . c37 >= -19.601384 = RAND ( ) / TRIM( t0 . c21 FROM 'C@rG3D(#9*17(a.,rV' ) = -106 GROUP BY c21 , c42 HAVING c42 = -73 LIMIT 1 ) ) ;

Comment by Oleg Smirnov [ 2023-11-01 ]

Tackling the test case

CREATE TABLE t1 (b int);
 
CREATE TABLE t2 (a int, c int);
 
 
(SELECT * FROM t1 JOIN t2 ON a=b LIMIT 5) ORDER BY a=16;

I have found that the field "a" in the ORDER BY clause is incorrectly resolved to "t2.a". The correct resolution should set it to "<unnamed_derived_table>.a".

Comment by Oleg Smirnov [ 2023-11-05 ]

MDEV-31197 and MDEV-32154 are confirmed to be caused by the same bug, but MDEV-32395 and MDEV-30756 are separate issues, so I reopened them.

commit 15ba38083837013d0795a425a403ed9b71d9b750 (HEAD -> bb-10.4-MDEV-29681, origin/bb-10.4-MDEV-29681)
 
Author: Oleg Smirnov <oleg.smirnov@mariadb.com>
 
Date:   Sat Nov 4 20:43:17 2023 +0700
 
 
 
    MDEV-29681 Server crashes when optimizing SQL with ORDER BY
 
    
    When parsing statements like (SELECT .. FROM ..) ORDER BY <expr>,
 
    there is a step LEX::add_tail_to_query_expression_body_ext_parens()
 
    which calls LEX::wrap_unit_into_derived(). After that the statement
 
    looks like SELECT * FROM (SELECT .. FROM ..), and parser's
 
    Lex_order_limit_lock structure (ORDER BY <expr>) is assigned to
 
    the new SELECT. But what is missing here is that Items in
 
    Lex_order_limit_lock are left with their original name resolution
 
    contexts, and fix_fields() later resolves the names incorrectly.
 
    
    For example, when processing
 
      (SELECT * FROM t1 JOIN t2 ON a=b) ORDER BY a
 
    Item_field 'a' in the ORDER BY clause is left with the name resolution
 
    context of the derived table (first_name_resolution_table='t1'), so
 
    it is resolved to 't1.a', which is incorrect.
 
    After LEX::wrap_unit_into_derived() the statement looks like
 
      SELECT * FROM (SELECT * FROM t1 JOIN t2 ON a=b) AS '__2' ORDER BY a,
 
    and the name resolution context for Item_field 'a' in the ORDER BY
 
    must be set to the wrapping SELECT's one.
 
    
    This commit fixes the issue by changing context for Items in
 
    Lex_order_limit_lock after LEX::wrap_unit_into_derived().

sanja, can you please review bb-10.4-MDEV-29681?
There are more calls to LEX::wrap_unit_into_derived() from other places but in my opinion there is no such problem as here (correct me if I'm wrong).

Comment by Oleksandr Byelkin [ 2023-11-10 ]

This patch fixes consequences not a problem and also make overhead for expression pared with correct context. Correct fix will be one of two:

1) parse it with correct context
2) if above not possible (SELECT is not made jet) then context should be changed whan we assign the ORDER BY to the SELECT

Comment by Oleksandr Byelkin [ 2023-11-10 ]

Ah, sorry I missed that it is the second case

Comment by Oleksandr Byelkin [ 2023-11-10 ]

OK to push

Comment by Oleg Smirnov [ 2023-11-10 ]

sanja, what do you think about the second question: "There are more calls to LEX::wrap_unit_into_derived() from other places". Are they free of such an issue?

Comment by Oleg Smirnov [ 2023-11-14 ]

The fix is pushed to 10.4 but the question above is still actual.

Comment by Alice Sherepa [ 2023-11-24 ]

please also check the test case from MDEV-32872 before closing:

CREATE TEMPORARY TABLE TableA (A INT, B INT, PRIMARY KEY (A));
 
CREATE TEMPORARY TABLE TableB (A VARCHAR(100), B INT);
 
 
(
 
    SELECT
 
        TableA.A AS AA, TableB.A AS BA
 
    FROM TableA
 
        LEFT JOIN TableB ON TableB.B = TableA.B
 
    GROUP BY TableA.A, TableB.A
 
    ORDER BY CAST(CAST(TableB.A AS DECIMAL) AS UNSIGNED)
 
)
 
ORDER BY CAST(CAST(BA AS DECIMAL) AS UNSIGNED);

Comment by Oleg Smirnov [ 2023-11-24 ]

@alice, there is no crash on 10.4 with the pushed patch, but the user is complaining on 10.6. Once the fix is merged upstream we can tell certainly if it's the same bug or not.

Generated at Thu Feb 08 10:10:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.