Bug #37899 Assertion in net_end_statement() on a query containing XOR
Submitted: 6 Jul 2008 7:33 Modified: 20 Nov 2010 23:10
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[6 Jul 2008 7:33] Philip Stoev
Description:
When executing a query containing XOR as a logical operator, mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085b0da4 in my_write_core (sig=6) at stacktrace.c:307
#3  0x08213e98 in handle_segfault (sig=6) at mysqld.cc:2638
#4  <signal handler called>
#5  0x00110416 in __kernel_vsyscall ()
#6  0x003e6660 in raise () from /lib/libc.so.6
#7  0x003e8028 in abort () from /lib/libc.so.6
#8  0x003df57e in __assert_fail () from /lib/libc.so.6
#9  0x0820b33c in net_end_statement (thd=0x9db0260) at protocol.cc:467
#10 0x0822b499 in dispatch_command (command=COM_QUERY, thd=0x9db0260,
    packet=0x9db7011 "SELECT STDDEV_POP( OUTR . `date_nokey` ) AS X FROM C AS OUTR WHERE ( OUTR . `int_nokey` , OUTR . `varchar_key` ) IN ( SELECT INNR . `int_key` AS X , INNR . `varchar_nokey` AS Z FROM BB AS INNR2 LEFT J"..., packet_length=375) at sql_parse.cc:1426
#11 0x0822ba49 in do_command (thd=0x9db0260) at sql_parse.cc:724
#12 0x0821bef0 in handle_one_connection (arg=0x9db0260) at sql_connect.cc:1153
#13 0x0057d32f in start_thread () from /lib/libpthread.so.0
#14 0x0049a27e in clone () from /lib/libc.so.6

The assert is here:

462         break;
463       case Diagnostics_area::DA_DISABLED:
464         break;
465       case Diagnostics_area::DA_EMPTY:
466       default:
467         DBUG_ASSERT(0);
468         net_send_ok(thd, thd->server_status, thd->total_warn_count,
469                     0, 0, NULL);
470         break;
471       }

This is a switch statement examining thd->main_da.status()

(gdb) print thd->main_da.m_status
$4 = Diagnostics_area::DA_EMPTY

How to repeat:
The query that crashes is:

 SELECT STDDEV_POP( OUTR . `date_nokey` ) AS X FROM C AS OUTR WHERE ( OUTR . `int_nokey` , OUTR . `varchar_key` ) IN ( SELECT INNR . `int_key` AS X , INNR . `varchar_nokey` AS Z FROM BB AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `int_nokey` >= INNR . `pk` ) WHERE INNR . `time_nokey` <= '2004-11-13' OR INNR . `varchar_nokey` = INNR . `varchar_key`   ) XOR OUTR . `pk` = 30

A simplifed test case will be uploaded shortly.
[7 Jul 2008 18:48] Philip Stoev
Here is a simple test case. In other words, every subquery with an XOR is probably affected:

CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `varchar_key` varchar(5) DEFAULT NULL,
  `varchar_nokey` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `varchar_key` (`varchar_key`)
);

INSERT INTO t1 VALUES (1,'qk','qk'),(2,'j','j'),(3,'aew','aew'),(4,NULL,NULL),(5,'qu','qu');

SELECT *
FROM t1
WHERE varchar_key IN (
 SELECT
 varchar_nokey
 FROM
 t1
) XOR pk = 30;
[9 Jul 2008 14:34] Sergey Petrunya
Need to check if this is same as BUG#36135.  BUG#36135 is "Patch approved" so it's possible to check with little effort.
[9 Jul 2008 16:12] Philip Stoev
Setting to Open so that the bug verification team can check previous releases.
[10 Jul 2008 14:13] Susanne Ebrecht
Please check like Sergei suggested and let us know if the patch will fix your issue.
[10 Jul 2008 16:22] Sveta Smirnova
Applied patch, located at http://lists.mysql.com/commits/46131, to 6.0 tree, mysqld crashed in same place.

Bug is not repeatabel with version 5.0 and 5.1.
[2 Oct 2008 21:45] Konstantin Osipov
This is an optimizier bug.
replace_where_subcondition() returns TRUE without setting an error in THD.
After that flatten_subqueries() returns TRUE.
Neither of the branches of the function work, and we reach this:

  return TRUE;
}

Which returns error without setting a message in THD.
[7 Oct 2008 19:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55623

2853 Evgeny Potemkin	2008-10-07
      Bug#37899: Wrongly checked optimization prerequisite caused failed assertion.
      
      One of the prerequisites for subquery optimizations is that the subquery
      predicate should be a top-level item or under a top-level AND item in the
      WHERE expression. XOR function was wrongly allowed for top-level function for
      the subquery optimization thus causing an internal error and failed assertion
      later.
      
      The Item_cond::fix_fields function now allow only the AND function as a 
      top-level item for subquery optimization.
      Added an assertion at the end of the replace_where_subcondition function
      to catch such errors.
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.8-alpha  (revid:epotemkin@mysql.com-20081008092802-2mru3ax0t9s1kjgv) (version source revid:epotemkin@mysql.com-20081008092802-2mru3ax0t9s1kjgv) (pib:5)
[11 Nov 2008 16:08] Paul Dubois
The version is actually 6.0.9.
[11 Nov 2008 23:48] Paul Dubois
Noted in 6.0.9 changelog.

An incorrectly checked XOR subquery optimization resulted in an
assertion failure.
[16 Aug 2010 6:40] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:09] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 23:10] Paul Dubois
Noted in 5.6.1 changelog.