Assertion `! is_set()' failed in Diagnostics_area::set_ok_status(THD*, ha_rows, ulonglong, const char*) on CREATE .. SELECT with ER_SUBQUERY_NO_1_ROW

[Elena Stepanova]

Version: '5.3.4-MariaDB-rc-debug-log'
mysqld: sql_class.cc:565: void Diagnostics_area::set_ok_status(THD*, ha_rows, ulonglong, const char*): Assertion `! is_set()' failed.

#8 0xb73bf014 in __assert_fail () from /lib/libc.so.6
#9 0x082b5458 in Diagnostics_area::set_ok_status (this=0x93efd98, thd=0x93eeae0, affected_rows_arg=0,
    last_insert_id_arg=0, message_arg=0xae83734f "Records: 0 Duplicates: 0 Warnings: 0")
    at sql_class.cc:565
#10 0x081ecc73 in my_ok (thd=0x93eeae0, affected_rows=0, id=0,
    message=0xae83734f "Records: 0 Duplicates: 0 Warnings: 0") at sql_class.h:2699
#11 0x08390ca3 in select_insert::send_eof (this=0x945ec50) at sql_insert.cc:3434
#12 0x083927c5 in select_create::send_eof (this=0x945ec50) at sql_insert.cc:4039
#13 0x0836ae4b in return_zero_rows (join=0x946f8d8, result=0x945ec50, tables=..., fields=...,
    send_row=false, select_options=2416200192,
    info=0x8936114 "Impossible WHERE noticed after reading const tables", having=0x0) at sql_select.cc:10456
#14 0x08356def in JOIN::exec (this=0x946f8d8) at sql_select.cc:2160
#15 0x08359589 in mysql_select (thd=0x93eeae0, rref_pointer_array=0x93f067c, tables=0x945e080, wild_num=0,
    fields=..., conds=0x945ebb8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2416200192, result=0x945ec50, unit=0x93f0260, select_lex=0x93f0540) at sql_select.cc:2931
#16 0x083512a3 in handle_select (thd=0x93eeae0, lex=0x93f0204, result=0x945ec50, setup_tables_done_option=0)
    at sql_select.cc:284
#17 0x082e4b17 in mysql_execute_command (thd=0x93eeae0) at sql_parse.cc:2780
#18 0x082eea54 in mysql_parse (thd=0x93eeae0,
    rawbuf=0x945dc10 "CREATE TABLE t AS \nSELECT a AS field1 FROM t1 \nWHERE ( SELECT alias1.a\nFROM t1 AS alias1\n) IS NOT NULL", length=102, found_semicolon=0xae838234) at sql_parse.cc:6152
#19 0x082e1435 in dispatch_command (command=COM_QUERY, thd=0x93eeae0,
    packet=0x9448351 "CREATE TABLE t AS \nSELECT a AS field1 FROM t1 \nWHERE ( SELECT alias1.a\nFROM t1 AS alias1\n) IS NOT NULL", packet_length=102) at sql_parse.cc:1228
#20 0x082e08df in do_command (thd=0x93eeae0) at sql_parse.cc:923
#21 0x082dd865 in handle_one_connection (arg=0x93eeae0) at sql_connect.cc:1193
#22 0xb764db25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-02-16 20:15:57 +0400
build-date: 2012-02-18 04:08:37 +0400
revno: 3424

Could not reproduce on MariaDB 5.2, 5.5, MySQL 5.1.60, 5.5.20

Query (0x945dc10): CREATE TABLE t AS SELECT a AS field1 FROM t1 WHERE ( SELECT alias1.a FROM t1 AS alias1 ) IS NOT NULL
Connection ID (thread ID): 2
Status: NOT_KILLED

Minimal optimizer_switch: in_to_exists=on
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

# Test case:

CREATE TABLE t1 ( a VARCHAR(1) );
INSERT INTO t1 VALUES ('u'),('k');
CREATE TABLE t AS
 SELECT a AS field1 FROM t1
 WHERE ( SELECT alias1.a
         FROM t1 AS alias1
       ) IS NOT NULL;

# End of test case

[Sergey Petrunia (sergefp)]

Not repeatable in the latest 5.3 tree:

mysql> CREATE TABLE t AS
    -> SELECT a AS field1 FROM t1
    -> WHERE ( SELECT alias1.a
    -> FROM t1 AS alias1
    -> ) IS NOT NULL;
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1242
Message: Subquery returns more than 1 row
1 row in set (0.00 sec)

[Sergey Petrunia (sergefp)]

Tried 5.3 with:

revno: 3424
revision-id: <email address hidden>

compiled with ./BUILD/compile-pentium-debug-max-no-ndb on x86_64. Still was unable to repeat.

[Sergey Petrunia (sergefp)]

Elena could you help with repeating this bug?

[Elena Stepanova (elenst)]

I've rebuilt the latest 5.3, the bug is still reproducible for me on revision-id: <email address hidden> (revno: 3437), openSUSE 11.3, both i686 and x86_64, built with the same BUILD/compile-pentium-debug-max-no-ndb. I've set up a test tree on the remote test machine so we could share the environment, details on the IRC.

[Sergey Petrunia (sergefp)]

Ok, here is why I was unable to repeat: the bug only show up when binary logging is disabled.

[Sergey Petrunia (sergefp)]

The problem does not show up when the error occurs inside
select_insert::send_data().

It only shows up when
- The "subquery returns > 1 row" error occurs when checking
  exec_const_cond (that is, at execution phase we find out that
  there won't be any matching rows).
- After that, select_insert::send_eof() fails to deal with error state.

Attempts to repeat this situation with a failure in other clause than
exec_const_cond were not successfull: I can either
- get an error in select_insert::send_data(), which is handled
- get an error elsewhere, which causes a general error condition and a
  call to select_create::abort() (and not ::send_data).

[Elena Stepanova (elenst)]

Fix released with 5.3.5-ga.