Crash in test_if_skip_sort_order() with RIGHT JOIN + STRAIGHT JOIN

[Philip Stoev]

The following query:

SELECT STRAIGHT_JOIN t1.a
FROM t1
RIGHT JOIN t2 ON t1.b = t2.b
WHERE
( t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b )
AND t2.b = 4
GROUP BY 1 ;

crashes as follows:

#4 0x0000000000741c11 in test_if_skip_sort_order (tab=0x181e0dd8, order=0x181d1fd8, select_limit_arg=18446744073709551615, no_changes=false, map=0x181f4f28)
    at sql_select.cc:17652
#5 0x000000000074fdef in JOIN::optimize (this=0x181da4b0) at sql_select.cc:1548
#6 0x0000000000751b6d in mysql_select (thd=0x2aaab5071e18, rref_pointer_array=0x2aaab50749f0, tables=0x181b6f90, wild_num=0, fields=..., conds=0x181d1e20,
    og_num=1, order=0x0, group=0x181d1fd8, having=0x0, proc_param=0x0, select_options=2147764738, result=0x181d20b8, unit=0x2aaab50742b8,
    select_lex=0x2aaab50747a0) at sql_select.cc:2886
#7 0x000000000075803c in handle_select (thd=0x2aaab5071e18, lex=0x2aaab5074218, result=0x181d20b8, setup_tables_done_option=0) at sql_select.cc:283
#8 0x00000000006a40ea in execute_sqlcom_select (thd=0x2aaab5071e18, all_tables=0x181b6f90) at sql_parse.cc:5102
#9 0x00000000006a61ca in mysql_execute_command (thd=0x2aaab5071e18) at sql_parse.cc:2247
#10 0x00000000006aef63 in mysql_parse (thd=0x2aaab5071e18,
    rawbuf=0x181b6c90 "SELECT STRAIGHT_JOIN t1.a\nFROM t1\nRIGHT JOIN t2 ON t1.b = t2.b\nWHERE\n( t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b )\nAND t2.b = 4\nGROUP BY 1", length=153, found_semicolon=0x41832f08) at sql_parse.cc:6102
#11 0x00000000006afaf1 in dispatch_command (command=COM_QUERY, thd=0x2aaab5071e18,
    packet=0x2aaab50f01b9 "SELECT STRAIGHT_JOIN t1.a\nFROM t1\nRIGHT JOIN t2 ON t1.b = t2.b\nWHERE\n( t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b )\nAND t2.b = 4\nGROUP BY 1 ", packet_length=154) at sql_parse.cc:1221
#12 0x00000000006b112f in do_command (thd=0x2aaab5071e18) at sql_parse.cc:916
#13 0x000000000069b93f in handle_one_connection (arg=0x2aaab5071e18) at sql_connect.cc:1187
#14 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#15 0x00000033b58d40cd in clone () from /lib64/libc.so.6

explain also crashes. repeatable on maria-5.1,5.2,5.3. Not repeatable on mysql 5.1, 5.5 . May be one of the test_if_skip_sort_order bugs marked as closed in bugs.mysql.com.

Test case:

--source include/have_innodb.inc
CREATE TABLE t1 ( a integer, b integer, KEY (a), PRIMARY KEY (b)) ENGINE=InnoDB;

CREATE TABLE t2 (b integer, PRIMARY KEY (b));
INSERT INTO t2 VALUES (4),(9);

SELECT STRAIGHT_JOIN t1.a
FROM t1
RIGHT JOIN t2 ON t1.b = t2.b
WHERE
( t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b )
AND t2.b = 4
GROUP BY 1;

[Philip Stoev (pstoev-askmonty)]

Assigning to sanja since it may be a GROUP BY / sorting bug.

[Philip Stoev (pstoev-askmonty)]

 bzr version-info
revision-id: <email address hidden>
date: 2011-09-10 18:01:27 +0300
build-date: 2011-09-13 09:33:50 +0300
revno: 3183
branch-nick: maria-5.3