Bug #10020 MySQL Crashes when referencing a column with a subquery condition
Submitted: 19 Apr 2005 22:51 Modified: 24 May 2005 19:59
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.11/4.1.12 BK OS:Linux (Redhat Fedora Core 3)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[19 Apr 2005 22:51] [ name withheld ]
Description:
Crash is reproduceable each time select query below is ran.

I have included a single log entry of this crash.

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, ... something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=258048
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x897a4b0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb1a1303c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x814b9ef
0x3d57c8
0x8184ed6
0x8184ed6
0x8185030
0x81854d9
0x81855e0
0x8190ae1
0x8196641
0x8196c55
0x81643b7
0x8167965
0x81682e5
0x81693e6
0x3cf341
0x2e7fee

New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x898efc8 = SELECT s.ip, count(e.itemid) FROM `events` e
JOIN sessions s ON s.sessionid = e.sessionid
WHERE e.sessionid=(SELECT sessionid FROM sessions ORDER BY sessionid DESC LIMIT 1) 
group by s.ip
HAVING count(e.itemid) > 0 LIMIT 0, 30
thd->thread_id=8
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
050419 16:38:18  mysqld restarted
050419 16:38:19  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050419 16:38:19  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 2432115.
InnoDB: Doing recovery: scanned up to log sequence number 0 2432115
InnoDB: Last MySQL binlog file position 0 79, file name ./ISABELL-bin.000020
050419 16:38:19  InnoDB: Flushing modified pages from the buffer pool...
050419 16:38:19  InnoDB: Started; log sequence number 0 2432115
/usr/libexec/mysqld: ready for connections.
Version: '4.1.11-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

#resolve_stack_dump -s /tmp/mysqld.sym -n /tmp/mysqld.stack
0x814b9ef handle_segfault + 587
0x3d57c8 (?)
0x8184ed6 _Z24change_cond_ref_to_constP3THDP6I_ListI8COND_CMPEP4ItemS6_S6_S6_ + 174
0x8184ed6 _Z24change_cond_ref_to_constP3THDP6I_ListI8COND_CMPEP4ItemS6_S6_S6_ + 174
0x8185030 _Z24change_cond_ref_to_constP3THDP6I_ListI8COND_CMPEP4ItemS6_S6_S6_ + 520
0x81854d9 _Z24propagate_cond_constantsP3THDP6I_ListI8COND_CMPEP4ItemS6_ + 269
0x81855e0 _Z24propagate_cond_constantsP3THDP6I_ListI8COND_CMPEP4ItemS6_ + 532
0x8190ae1 _ZN4JOIN8optimizeEv + 113
0x8196641 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 101
0x8196c55 _Z13handle_selectP3THDP6st_lexP13select_result + 177
0x81643b7 _Z21mysql_execute_commandP3THD + 17399
0x8167965 _Z11mysql_parseP3THDPcj + 297
0x81682e5 _Z16dispatch_command19enum_server_commandP3THDPcj + 2321
0x81693e6 handle_one_connection + 1438
0x3cf341 (?)
0x2e7fee (?)

How to repeat:
-- 
-- Table structure for table `events`
-- 

CREATE TABLE `events` (
  `itemid` bigint(20) unsigned NOT NULL auto_increment,
  `sessionid` bigint(20) unsigned default NULL,
  `time` int(10) unsigned NOT NULL default '0',
  `type` set('A','D','E','F','G','I','L','N','U') collate latin1_general_ci NOT NULL default '',
  `data` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `events`
-- 

INSERT INTO `events` VALUES (1, 1, 1, 'D', '');

-- --------------------------------------------------------

-- 
-- Table structure for table `sessions`
-- 

CREATE TABLE `sessions` (
  `sessionid` bigint(20) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `date` int(10) unsigned NOT NULL default '0',
  `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`sessionid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `sessions`
-- 

INSERT INTO `sessions` VALUES (1, 1, 1, '10.10.10.1');

Then execute:
SELECT s.ip, count( e.itemid )
FROM `events` e
JOIN sessions s ON s.sessionid = e.sessionid
WHERE e.sessionid = (
SELECT sessionid
FROM sessions
ORDER BY sessionid DESC
LIMIT 1 )
GROUP BY s.ip
HAVING count( e.itemid ) >0
LIMIT 0 , 30
[19 Apr 2005 23:22] [ name withheld ]
After further testing, I am thinking the problem lies in referencing a column with a subquery conidition in another part of the where clause.

(This example is a little far fetched, but I wanted to illustrate my point simply)

The following query produces the crash:
SELECT count( e.itemid )
FROM `events` e, sessions s
WHERE e.sessionid = (
SELECT sessionid
FROM sessions
WHERE sessionid =1 )
AND e.sessionid = s.sessionid

The following query works:
SELECT count( e.itemid )
FROM `events` e, sessions s
WHERE e.sessionid = (
SELECT sessionid
FROM sessions
WHERE sessionid =1 )
AND s.sessionid = (
SELECT sessionid
FROM sessions
WHERE sessionid =1 )
[20 Apr 2005 0:15] MySQL Verification Team
Tested on Slackware 10.1.
Thank you for the bug report.
[20 Apr 2005 2:04] Heikki Tuuri
Hi!

I am changing this to a 'Server' bug, since the crash happens in the MySQL optimizer apparently.

Regards,

Heikki
[16 May 2005 9:22] 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/internals/24935
[16 May 2005 9:26] Oleksandr Byelkin
ChangeSet
  1.2270 05/05/16 12:19:10 bell@book.sanja.is.com.ua +4 -0
  relaxed DBUG_ASSERT in Item_int_with_ref::new_item() to "any constant" (BUG#10020)
[17 May 2005 20:54] Oleksandr Byelkin
pushed to 4.1.13
[24 May 2005 19:59] Paul DuBois
Noted in 4.1.13 changelog.