Bug #19136 Crashing log-bin and uninitialized user variables in a derived table
Submitted: 17 Apr 2006 11:23 Modified: 13 May 2006 6:45
Reporter: Shane Bester
Status: Closed
Category:Server: Replication Severity:S2 (Serious)
Version:4.1.18,5.0.19, 5.0.20, 5.0-bk OS:Linux (RHEL4, Windows)
Assigned to: Andrei Elkin Target Version:

[17 Apr 2006 11:23] Shane Bester 
Description:
When issueing in a query in the form of:

INSERT INTO .....
SELECT ..... FROM
(
 SELECT ..... FROM ..... WHERE ....., GROUP BY .....
 UNION
 SELECT ..... FROM ..... WHERE ....., GROUP BY .....
 UNION
 SELECT ..... FROM ..... WHERE ....., GROUP BY .....
 UNION
 SELECT ..... FROM ..... WHERE ....., GROUP BY .....
 UNION
 SELECT ..... FROM ..... WHERE ....., GROUP BY .....
) AS .....
GROUP BY ....., .....

The server crashes quickly on an ASSERT(table = 0).  The full gdb output is attached.

#0  0x0826bee8 in select_union::create_result_table
#1  0x0826c621 in st_select_lex_unit::prepare
#2  0x0826d808 in mysql_derived_prepare
#3  0x0826d6f8 in mysql_handle_derived
#4  0x081adc25 in open_and_lock_tables
#5  0x0818c803 in mysql_execute_command
#6  0x08191876 in mysql_parse
#7  0x081893fc in dispatch_command
#8  0x08188de3 in do_command
#9  0x081880da in handle_one_connection

How to repeat:
This is 100% repeatable, using a current 12GB dataset.  We are attempting to create a
smaller testcase.

Suggested fix:
not sure.
[17 Apr 2006 11:27] Shane Bester 
bt full (I masked the original query)

Attachment: gdb_1_thread_sensored.txt (plain/text, text), 3.54 KiB.
[17 Apr 2006 13:21] Shane Bester 
Updated synopsis.

How to repeat:

start server as mysqld --log-bin:
launch new client connection and run:

<snip>
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT);
INSERT INTO t1(id) VALUES(1),(2),(3);
#SET @var1=1;
#SET @var2=2;
INSERT INTO t1
SELECT id FROM
(
  SELECT id FROM t1 WHERE id BETWEEN @var1 AND @var2
  UNION 
  SELECT id  FROM t1 WHERE id BETWEEN @var1 AND @var2
) AS t2
;
</snip>

If you uncomment the above #SET lines, no crash occurs.
If you start a server without binlogging, no crash occurs.
[19 Apr 2006 20:03] Shane Bester 
Affects 4.1.18 also..
[5 May 2006 10:21] 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/5994
[5 May 2006 15:39] Andrei Elkin 
Mats approved.
[7 May 2006 18:55] 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/6069
[12 May 2006 11:46] Andrei Elkin 
fixed in 4.1.20, 5.0.22, 5.1.11-beta
[13 May 2006 6:45] Paul DuBois 
Noted in 4.1.20, 5.0.22, 5.1.11 changelogs.

Use of uninitialized user variables in a subquery in the
<literal>FROM</literal> clause results in bad entries in the
binary log.