MWL#253: Server hangs in table_cond_selectivity with optimizer_use_condition_selectivity>1, IN subquery with aggregate function

SET use_stat_tables=PREFERABLY;

SET optimizer_use_condition_selectivity=3;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (5),(9);

CREATE TABLE t2 (b VARCHAR(8));

INSERT INTO t2 VALUES ('red'),('blue');

CREATE TABLE t3 (c VARCHAR(8), d VARCHAR(8));

INSERT INTO t3 VALUES ('white','black'),('cyan','yellow');

ANALYZE TABLE t1, t2, t3;

FLUSH TABLES;

SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( 

  SELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c 

);

MariaDB [test]> show full processlist \G

*************************** 1. row ***************************

      Id: 2

    User: root

    Host: localhost

      db: test

 Command: Query

    Time: 37

   State: statistics

    Info: SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( 

SELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c 

)

Progress: 0.000

#0  0x0000000000658977 in table_cond_selectivity (join=0x7f693010e480, idx=0, s=0x7f6930012e60, rem_tables=3) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:7095

#1  0x0000000000659092 in best_extension_by_limited_search (join=0x7f693010e480, remaining_tables=7, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=3) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:7344

#2  0x000000000065775d in greedy_search (join=0x7f693010e480, remaining_tables=7, search_depth=62, prune_level=1, use_cond_selectivity=3) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:6665

#3  0x0000000000656d63 in choose_plan (join=0x7f693010e480, join_tables=7) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:6242

#4  0x0000000000650c6d in make_join_statistics (join=0x7f693010e480, tables_list=..., conds=0x7f6930011f50, keyuse_array=0x7f693010e7b8) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:3899

#5  0x0000000000647b0d in JOIN::optimize_inner (this=0x7f693010e480) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:1306

#6  0x0000000000646ab8 in JOIN::optimize (this=0x7f693010e480) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:1008

#7  0x000000000064e76d in mysql_select (thd=0x3ec2608, rref_pointer_array=0x3ec55b0, tables=0x7f6930040f10, wild_num=1, fields=..., conds=0x7f6930006ef0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f6930002e40, unit=0x3ec4c08, select_lex=0x3ec5300) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:3220

#8  0x0000000000644f23 in handle_select (thd=0x3ec2608, lex=0x3ec4b50, result=0x7f6930002e40, setup_tables_done_option=0) at /data/bzr/maria-10.0-mwl253/sql/sql_select.cc:376

#9  0x000000000061c3d4 in execute_sqlcom_select (thd=0x3ec2608, all_tables=0x7f6930040f10) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:4797

#10 0x00000000006149d4 in mysql_execute_command (thd=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:2266

#11 0x000000000061ec76 in mysql_parse (thd=0x3ec2608, rawbuf=0x7f69300f5860 "SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( \nSELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c \n)", length=114, parser_state=0x7f6948a4d4f0) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:5920

#12 0x0000000000611cf8 in dispatch_command (command=COM_QUERY, thd=0x3ec2608, packet=0x3ec75e9 "SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( \nSELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c \n)", packet_length=114) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:1091

#13 0x0000000000610f26 in do_command (thd=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_parse.cc:810

#14 0x0000000000729f64 in do_handle_one_connection (thd_arg=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_connect.cc:1266

#15 0x000000000072994f in handle_one_connection (arg=0x3ec2608) at /data/bzr/maria-10.0-mwl253/sql/sql_connect.cc:1181

#16 0x0000000000c59fe6 in pfs_spawn_thread (arg=0x3e637e8) at /data/bzr/maria-10.0-mwl253/storage/perfschema/pfs.cc:1015

#17 0x00007f694c683e9a in start_thread (arg=0x7f6948a4e700) at pthread_create.c:308

#18 0x00007f694b977cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112