Bug #72814 Assertion `!(ordered_index_usage == ordered_index_void && ... fails
Submitted: 30 May 2014 9:31 Modified: 2 Jun 2014 9:41
Reporter: Elena Stepanova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.17 OS:Any
Assigned to: CPU Architecture:Any

[30 May 2014 9:31] Elena Stepanova 
Description:
mysqld: mysql-5.6/sql/sql_select.cc:5130: bool JOIN::make_tmp_tables_info(): Assertion `!(ordered_index_usage == ordered_index_void && !plan_is_const() && join_tab[const_tables].use_order())' failed.

#6  0x00007fbb08de2621 in *__GI___assert_fail (assertion=0xf6ed48 "!(ordered_index_usage == ordered_index_void && !plan_is_const() && join_tab[const_tables].use_order())", file=<optimized out>, line=5130, function=0xf6f540 "bool JOIN::make_tmp_tables_info()") at assert.c:81
#7  0x0000000000817aea in JOIN::make_tmp_tables_info (this=0x2ff6398) at mysql-5.6/sql/sql_select.cc:5128
#8  0x00000000009a891b in JOIN::optimize (this=0x2ff6398) at mysql-5.6/sql/sql_optimizer.cc:1013
#9  0x000000000080e612 in mysql_execute_select (thd=0x7fbaf8079010, select_lex=0x7fbaf807b888, free_join=true) at mysql-5.6/sql/sql_select.cc:1086
#10 0x000000000080e94d in mysql_select (thd=0x7fbaf8079010, tables=0x2ecef40, wild_num=0, fields=..., conds=0x2ed05f0, order=0x7fbaf807ba50, group=0x7fbaf807b988, having=0x0, select_options=2147748608, result=0x2ed0940, unit=0x7fbaf807b240, select_lex=0x7fbaf807b888) at mysql-5.6/sql/sql_select.cc:1221
#11 0x000000000080ca66 in handle_select (thd=0x7fbaf8079010, result=0x2ed0940, setup_tables_done_option=0) at mysql-5.6/sql/sql_select.cc:110
#12 0x00000000007e8195 in execute_sqlcom_select (thd=0x7fbaf8079010, all_tables=0x2ecef40) at mysql-5.6/sql/sql_parse.cc:5094
#13 0x00000000007e1114 in mysql_execute_command (thd=0x7fbaf8079010) at mysql-5.6/sql/sql_parse.cc:2642
#14 0x00000000007ea7aa in mysql_parse (thd=0x7fbaf8079010, rawbuf=0x2ece990 "SELECT a, SUM( DISTINCT a ) FROM t1 WHERE a IN ( SELECT c FROM t2 ) GROUP BY a ORDER BY b", length=89, parser_state=0x7fbb01291e60) at mysql-5.6/sql/sql_parse.cc:6235
#15 0x00000000007de314 in dispatch_command (command=COM_QUERY, thd=0x7fbaf8079010, packet=0x7fbaf814fbc1 "SELECT a, SUM( DISTINCT a ) FROM t1 WHERE a IN ( SELECT c FROM t2 ) GROUP BY a ORDER BY b", packet_length=89) at mysql-5.6/sql/sql_parse.cc:1334
#16 0x00000000007dd4f6 in do_command (thd=0x7fbaf8079010) at mysql-5.6/sql/sql_parse.cc:1036
#17 0x00000000007a7da4 in do_handle_one_connection (thd_arg=0x7fbaf8079010) at mysql-5.6/sql/sql_connect.cc:982
#18 0x00000000007a78d3 in handle_one_connection (arg=0x7fbaf8079010) at mysql-5.6/sql/sql_connect.cc:898
#19 0x0000000000dc099d in pfs_spawn_thread (arg=0x7fbaf80ca120) at mysql-5.6/storage/perfschema/pfs.cc:1860
#20 0x00007fbb0a12bb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#21 0x00007fbb08e91a7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

How to repeat:
CREATE TABLE t1 (a INT, b VARCHAR(1), KEY(b,a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,'v'),(2,'s');

CREATE TABLE t2 (c INT, KEY(c)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(3);

SELECT a, SUM( DISTINCT a ) FROM t1 WHERE a IN ( SELECT c FROM t2 ) GROUP BY a ORDER BY b;
[2 Jun 2014 9:41] MySQL Verification Team 
Hello Elena,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[2 Jun 2014 9:42] MySQL Verification Team 
Observed that debug builds are crashing where as release builds are not affected.

/////

mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.20-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (a INT, b VARCHAR(1), KEY(b,a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO t1 VALUES (1,'v'),(2,'s');
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (c INT, KEY(c)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO t2 VALUES (1),(3);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a, SUM( DISTINCT a ) FROM t1 WHERE a IN ( SELECT c FROM t2 ) GROUP BY a ORDER BY b;
+------+-------------------+
| a    | SUM( DISTINCT a ) |
+------+-------------------+
|    1 |                 1 |
+------+-------------------+
1 row in set (0.01 sec)

//

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (a INT, b VARCHAR(1), KEY(b,a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (1,'v'),(2,'s');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE t2 (c INT, KEY(c)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES (1),(3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a, SUM( DISTINCT a ) FROM t1 WHERE a IN ( SELECT c FROM t2 ) GROUP BY a ORDER BY b;
+------+-------------------+
| a    | SUM( DISTINCT a ) |
+------+-------------------+
|    1 |                 1 |
+------+-------------------+
1 row in set (0.01 sec)
[2 Jun 2014 9:43] MySQL Verification Team 
// 5.7.5 - Not affected (debug/non-debug builds)

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (a INT, b VARCHAR(1), KEY(b,a)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (1,'v'),(2,'s');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE t2 (c INT, KEY(c)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t2 VALUES (1),(3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a, SUM( DISTINCT a ) FROM t1 WHERE a IN ( SELECT c FROM t2 ) GROUP BY a ORDER BY b;
+------+-------------------+
| a    | SUM( DISTINCT a ) |
+------+-------------------+
|    1 |                 1 |
+------+-------------------+
1 row in set (0.04 sec)