Bug #17366 crash on left join on union with literal, 5.0.18
Submitted: 14 Feb 2006 9:59 Modified: 14 Mar 2006 18:12
Reporter: Stijn Hoop Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.19-BK, 5.0.18 OS:Linux (Linux & FreeBSD)
Assigned to: Evgeny Potemkin

[14 Feb 2006 9:59] Stijn Hoop
Description:
Running the following query, with everything uncommented, crashes MySQL 5.0.18 on FreeBSD and MySQL 5.0.15 on Linux. The crash is apparently *after* the query has completed as the mysql client
reports a reconnect on the next statement sent.

Running it as is does not cause a crash, hence the comments.

%%%
drop table if exists crash1, crash2;
create table crash1 (id int primary key, alt int);
create table crash2 (id int primary key, alt int);

insert into crash1 values (1, 1), (2, 2), (3, 3);
insert into crash2 values (1, 4), (2, 5), (3, 6);

-- Uncomment the crash2 stuff in the query below to make MySQL crash
select
        uni.t as uni_type,
        uni.id as uni_id,
        crash1.alt as crash1_alt
--      , crash2.alt as crash2_alt
from
        (select 'crash1' as t,
                crash1.id as id
         from crash1
         union
         select 'crash2' as t,
                crash2.id as id
         from crash2
        ) as uni
        left join crash1 on
                uni.t = 'crash1'
                and uni.id = crash1.id
--      left join crash2 on
--              uni.t = 'crash2'
--              and uni.id = crash2.id
where
        uni.t = 'crash1'
%%%

How to repeat:
See above.
[14 Feb 2006 10:05] Stijn Hoop
Forgot to mention: MySQL 4.1 ran the original query where I discovered this bug just fine; I can't test this crasher anymore (no server on 4.1 anymore) but I think this is a regression.
[14 Feb 2006 12:37] Stijn Hoop
A debug version of mysqld reported a backtrace in the error log. A resolve_stack_dump run did not produce very obvious results:

thd=0xa4c1d18
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=0xaad1ec4c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817b011 handle_segfault + 639
0xc0c420 (?)
0x819782e _Z16dispatch_command19enum_server_commandP3THDPcj + 1492
0x819782e _Z16dispatch_command19enum_server_commandP3THDPcj + 1492
0x8198c84 _Z10do_commandP3THD + 256
0x819970c handle_one_connection + 2224
0x766b80 (?)
0x6be9ce (?)
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 0xa5c98c8 = select
        uni.t as uni_type,
        uni.id as uni_id,
        crash1.alt as crash1_alt
        , crash2.alt as crash2_alt
from
        (select 'crash1' as t,
                crash1.id as id
         from crash1
         union
         select 'crash2' as t,
                crash2.id as id
         from crash2
        ) as uni
        left join crash1 on
                uni.t = 'crash1'
                and uni.id = crash1.id
        left join crash2 on
                uni.t = 'crash2'
                and uni.id = crash2.id
where
        uni.t = 'crash1'
thd->thread_id=1
[19 Feb 2006 14:39] Valerii Kravchuk
Verified just as described with 5.0.19-BK (ChangeSet@1.2064, 2006-02-18 21:08:41+01:00) on Linux:

mysql> create table crash1 (id int primary key, alt int);
creaQuery OK, 0 rows affected (0.02 sec)

mysql> create table crash2 (id int primary key, alt int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into crash1 values (1, 1), (2, 2), (3, 3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into crash2 values (1, 4), (2, 5), (3, 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select
    ->         uni.t as uni_type,
    ->         uni.id as uni_id,
    ->         crash1.alt as crash1_alt
    -> --      , crash2.alt as crash2_alt
    -> from
    ->         (select 'crash1' as t,
    ->                 crash1.id as id
    ->          from crash1
    ->          union
    ->          select 'crash2' as t,
    ->                 crash2.id as id
    ->          from crash2
    ->         ) as uni
    ->         left join crash1 on
    ->                 uni.t = 'crash1'
    ->                 and uni.id = crash1.id
    -> --      left join crash2 on
    -> --              uni.t = 'crash2'
    -> --              and uni.id = crash2.id
    -> where
    ->         uni.t = 'crash1';
+----------+--------+------------+
| uni_type | uni_id | crash1_alt |
+----------+--------+------------+
| crash1   |      1 |          1 |
| crash1   |      2 |          2 |
| crash1   |      3 |          3 |
+----------+--------+------------+
3 rows in set (0.01 sec)

mysql> select
    ->         uni.t as uni_type,
    ->         uni.id as uni_id,
    ->         crash1.alt as crash1_alt
    ->       , crash2.alt as crash2_alt
    -> from
    ->         (select 'crash1' as t,
    ->                 crash1.id as id
    ->          from crash1
    ->          union
    ->          select 'crash2' as t,
    ->                 crash2.id as id
    ->          from crash2
    ->         ) as uni
    ->         left join crash1 on
    ->                 uni.t = 'crash1'
    ->                 and uni.id = crash1.id
    ->       left join crash2 on
    ->               uni.t = 'crash2'
    ->               and uni.id = crash2.id
    -> where
    ->         uni.t = 'crash1';
+----------+--------+------------+------------+
| uni_type | uni_id | crash1_alt | crash2_alt |
+----------+--------+------------+------------+
| crash1   |      1 |          1 |       NULL |
| crash1   |      2 |          2 |       NULL |
| crash1   |      3 |          3 |       NULL |
+----------+--------+------------+------------+
3 rows in set (0.01 sec)

mysql>
Number of processes running now: 0
060212 00:05:08  mysqld restarted

mysql> exit
Bye
openxs@suse:~/dbs/5.0> tail -50 var/suse.err
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=0x428d4a1c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817ba06
0xffffe420
0x16
0x819d860
0x819ea73
0x4004eaa7
0x40249c2e
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 0x8abfa90 = select
        uni.t as uni_type,
        uni.id as uni_id,
        crash1.alt as crash1_alt
      , crash2.alt as crash2_alt
from
        (select 'crash1' as t,
                crash1.id as id
         from crash1
         union
         select 'crash2' as t,
                crash2.id as id
         from crash2
        ) as uni
        left join crash1 on
                uni.t = 'crash1'
                and uni.id = crash1.id
      left join crash2 on
              uni.t = 'crash2'
              and uni.id = crash2.id
where
        uni.t = 'crash1'
thd->thread_id=1
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
060212 00:05:08  mysqld restarted
060212  0:05:08  InnoDB: Started; log sequence number 0 734296831
060212  0:05:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=suse-relay-bin' to avoid this problem.
060212  0:05:09 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.19'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

It is a serious bug, indeed.
[1 Mar 2006 13:12] 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/3321
[14 Mar 2006 9:02] 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/3811
[14 Mar 2006 15:12] Evgeny Potemkin
When there is conjunction of conds, the substitute_for_best_equal_field()
will call the eliminate_item_equal() function in loop to build final
expression. But if eliminate_item_equal() finds that some cond will always
evaluate to 0, then that cond will be substituted by Item_int with value ==
0. In this case on the next iteration eliminate_item_equal() will get that 
Item_int and treat it as Item_cond. This is leads to memory corruption and
server crash on cleanup phase.

Fixed in 5.0.20, cset  1.2053.44.1
[14 Mar 2006 18:12] Paul Dubois
Noted in 5.0.20 changelog.

A <literal>LEFT JOIN</literal> with a <literal>UNION</literal>
that selects literal values could crash the server. (Bug
#17366)