Bug #19136 Crashing log-bin and uninitialized user variables in a derived table
Submitted: 17 Apr 2006 9:23 Modified: 13 May 2006 4:45
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL 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 CPU Architecture:Any

[17 Apr 2006 9: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 9: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 11: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 18:03] Shane Bester
Affects 4.1.18 also..
[5 May 2006 8: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 13:39] Andrei Elkin
Mats approved.
[7 May 2006 16: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 9:46] Andrei Elkin
fixed in 4.1.20, 5.0.22, 5.1.11-beta
[13 May 2006 4: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.