query with derived table and GROUP BY on I_S crash when derived_with_keys=on

[Kristian Nielsen]

drop table if exists test.t1;
create table test.t1 (c1 int primary key, c2 char(5));
set optimizer_switch="derived_with_keys=on";
SELECT 1
  FROM INFORMATION_SCHEMA.COLUMNS
 INNER JOIN ( SELECT TABLE_SCHEMA,
                     GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES
                FROM INFORMATION_SCHEMA.STATISTICS
               GROUP BY TABLE_SCHEMA) AS UNIQUES
    ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
drop table t1;

This crashes mariadb-5.5.20 with null-pointer exception in
ha_maria::scan_time(). Does not crash in 5.3.

I did a rough trace of how the crash is reached by examing the mysqld.1.trace
file from mysql-test-run --debug:

    mysql_select
      JOIN::prepare
 st_select_lex::handle_derived
   TABLE_LIST::handle_derived
     mysql_handle_single_derived
       # Note, it doesn't call mysql_derived_init() here
       mysql_derived_prepare
  create_result_table
    create_tmp_table
      get_new_handler
                      # This is where we obtain the handler object for the
                      # tmp table. However, it is never opened.
        setup_tables_and_check_access
      JOIN::optimize
 JOIN::optimize
 JOIN::transform_max_min_subquery
 convert_join_subqueries_to_semijoins
 simplify_joins
 build_bitmap_for_nested_joins
 optimize_cond
 get_sort_by_table
 make_join_statistics
   get_best_combination
 make_select
 reset_nj_counters
 make_outerjoin_info
 make_join_select
   add_not_null_conds
     SQL_SELECT::test_quick_select
       ha_maria::scan_time()
  -> CRASH due to not opened handler

So inside mysql_select(), we first call JOIN::prepare, which ends up in
mysql_handle_single_derived(). It calls mysql_derived_prepare() which
allocates the new handler object for the Aria tmp table. Note that this
handler object is never open()'ed.

After, mysql_select() calls JOIN::optimize(). This goes through a number of
calls until it does make_join_select(). And inside make_join_select() we end
up in ha_maria::scan_time() on a not opened handler object, which crashes with
NULL pointer access.

I will have to leave to the optimiser people to investigate further why the
handler is accessed without being opened first and what the fix may be.

Stack trace:

#0 0x00007f1e65fdd4ec in __pthread_kill (threadid=<optimized out>, signo=<optimized out>) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:63
#1 0x0000000000cd6c06 in my_write_core (sig=11) at /home/knielsen/my/5.5/mariadb-5.5/mysys/stacktrace.c:457
#2 0x00000000007d5301 in handle_fatal_signal (sig=11) at /home/knielsen/my/5.5/mariadb-5.5/sql/signal_handler.cc:274
#3 <signal handler called>
#4 0x0000000000b41c6d in ha_maria::scan_time (this=0x2906b18) at /home/knielsen/my/5.5/mariadb-5.5/storage/maria/ha_maria.cc:1010
#5 0x00000000008d34b5 in SQL_SELECT::test_quick_select (this=0x2908aa0, thd=0x2779240, keys_to_use=..., prev_tables=4611686018427387905, limit=18446744073709551615, force_quick_range=false,
    ordered_output=false) at /home/knielsen/my/5.5/mariadb-5.5/sql/opt_range.cc:2890
#6 0x000000000066d223 in make_join_select (join=0x28f6838, select=0x2908748, cond=0x2908930) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:8429
#7 0x0000000000659dfe in JOIN::optimize (this=0x28f6838) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:1337
#8 0x000000000065f83d in mysql_select (thd=0x2779240, rref_pointer_array=0x277beb8, tables=0x2887c00, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2684619520, result=0x28a2718, unit=0x277b5b0, select_lex=0x277bc68) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:2951
#9 0x0000000000656a5c in handle_select (thd=0x2779240, lex=0x277b500, result=0x28a2718, setup_tables_done_option=0) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_select.cc:309
#10 0x0000000000630710 in execute_sqlcom_select (thd=0x2779240, all_tables=0x2887c00) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:4616
#11 0x00000000006299d0 in mysql_execute_command (thd=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:2185
#12 0x0000000000632e9f in mysql_parse (thd=0x2779240,
    rawbuf=0x2887888 "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES FROM INFORMATION_SCHEMA.STATISTICS GROUP BY TABLE_SCHEMA)"..., length=261, parser_state=0x7f1e5a57d630) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:5730
#13 0x00000000006270aa in dispatch_command (command=COM_QUERY, thd=0x2779240,
    packet=0x2871011 "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES FROM INFORMATION_SCHEMA.STATISTICS GROUP BY TABLE_SCHEMA)"..., packet_length=261) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:1056
#14 0x000000000062639a in do_command (thd=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_parse.cc:795
#15 0x0000000000721422 in do_handle_one_connection (thd_arg=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_connect.cc:1253
#16 0x0000000000720e0d in handle_one_connection (arg=0x2779240) at /home/knielsen/my/5.5/mariadb-5.5/sql/sql_connect.cc:1168
#17 0x0000000000c812c3 in pfs_spawn_thread (arg=0x28f1170) at /home/knielsen/my/5.5/mariadb-5.5/storage/perfschema/pfs.cc:1015
#18 0x00007f1e65fd7b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#19 0x00007f1e64d2c90d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
#20 0x0000000000000000 in ?? ()

This was originally reported on IRC #maria by user Dev0n, with the following
query generated by the openark online alter table tool:

drop table if exists test.t1;
create table test.t1 (c1 int primary key, c2 char(5));
set optimizer_switch="derived_with_keys=on";
SELECT COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, COLUMNS.COLUMN_NAME, UNIQUES.INDEX_NAME, UNIQUES.COLUMN_NAMES, UNIQUES.COUNT_COLUMN_IN_INDEX, COLUMNS.DATA_TYPE, COLUMNS.CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COUNT(*) AS COUNT_COLUMN_IN_INDEX, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES, SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE NON_UNIQUE=0 GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS UNIQUES ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA AND COLUMNS.TABLE_NAME = UNIQUES.TABLE_NAME AND COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME ) WHERE COLUMNS.TABLE_SCHEMA = 'test' AND COLUMNS.TABLE_NAME = 't1' ORDER BY COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, CASE UNIQUES.INDEX_NAME WHEN 'PRIMARY' THEN 0 ELSE 1 END, CASE IFNULL(CHARACTER_SET_NAME, '') WHEN '' THEN 0 ELSE 1 END, CASE DATA_TYPE WHEN 'tinyint' THEN 0 WHEN 'smallint' THEN 1 WHEN 'int' THEN 2 WHEN 'bigint' THEN 3 ELSE 100 END, COUNT_COLUMN_IN_INDEX;

If we set optimizer_switch="derived_with_keys=off" then we get no crash.

[Igor Babaev (igorb-seattle)]

The crash is caused by a bug in the function JOIN::drop_unused_derived_keys of the mariadb 5.3 code line.
The fix was pushed into the 5.3 tree.

[Elena Stepanova (elenst)]

Fix released in 5.3.6