Bug #6276 A SELECT that does a NATURAL JOIN of view and table crashes server
Submitted: 27 Oct 2004 6:51 Modified: 25 Aug 2005 9:42
Reporter: Sergei Glukhov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Linux (Linux)
Assigned to: Timour Katchaounov CPU Architecture:Any

[27 Oct 2004 6:51] Sergei Glukhov
Description:
The following SELECT crashes server:
select * from v2 natural left join a2;

where v2 - view, a2 - table

How to repeat:
create table a1(a1 int);
insert into a1 values(1),(2),(3),(4),(5),(6);
create table a2(a2 int);
insert into a2 values(1),(2),(3),(4),(5),(6);
create view v2 (c) as select a1 from a1;
select * from v2 natural left join a2;
drop table v2, a1, a2;

The same problem with NATURAL RIGHT.
[27 Oct 2004 10:20] Victoria Reznichenko
Verified with 5.0.2-alpha-debug-log

Back trace:
(gdb) bt
#0  0x081b4b23 in make_cond_for_table (cond=0x0, tables=13835058055282163713, used_table=1)
    at sql_select.cc:9925
#1  0x081ab36e in make_join_select (join=0x87b6780, select=0x87bd5e0, cond=0x0)
    at sql_select.cc:5395
#2  0x081a1774 in JOIN::optimize (this=0x87b6780) at sql_select.cc:705
#3  0x081a49f4 in mysql_select (thd=0x87a8be0, rref_pointer_array=0x87a8ef0, tables=0x87b59d8,
    wild_num=1, fields=@0x87a8e3c, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=8669696, result=0x87b6770, unit=0x87a8c24,
    select_lex=0x87a8dd0) at sql_select.cc:2015
#4  0x081a05ea in handle_select (thd=0x87a8be0, lex=0x87a8c18, result=0x87b6770)
    at sql_select.cc:229
#5  0x08175e2d in mysql_execute_command (thd=0x87a8be0) at sql_parse.cc:2069
#6  0x0817bc09 in mysql_parse (thd=0x87a8be0,
    inBuf=0x87b58d8 "select * from v2 natural left join a2", length=37) at sql_parse.cc:4626
#7  0x08174b02 in dispatch_command (command=COM_QUERY, thd=0x87a8be0,
    packet=0x87a04f9 "select * from v2 natural left join a2", packet_length=38)
    at sql_parse.cc:1518
#8  0x081743e7 in do_command (thd=0x87a8be0) at sql_parse.cc:1298
#9  0x0817394c in handle_one_connection (arg=0x87a8be0) at sql_parse.cc:1034
#10 0xb7e4714b in pthread_start_thread () from /lib/libpthread.so.0
#11 0xb7e471df in pthread_start_thread_event () from /lib/libpthread.so.0
#12 0xb7d7a50a in clone () from /lib/libc.so.6
[3 Feb 2005 10:41] Sergey Petrunya
The problem is that natural outer join turns into outer join with no ON condition when joined tables don't have any common fields. 
This can be reproduced without views: 

select * from a1 natural left join a2;
[4 Feb 2005 23:09] Sergey Petrunya
Initial fix rejected, working on a new one
[2 Mar 2005 17:03] Timour Katchaounov
This bug requires considerable work to transform such queries into inner joins.
The bug will be fixed when such transformation is implemented in an upcoming
MySQL version.
[18 Apr 2005 18:42] Trudy Pelzer
See also Bug#9978.
[23 Aug 2005 17:03] 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/28701
[23 Aug 2005 20:50] Timour Katchaounov
Pushed into 5.0.12.

The fix is based on WL#2486 - natural and using join according to SQL:2003.
[24 Aug 2005 7:51] 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/28740
[25 Aug 2005 9:42] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix documented in 5.0.12 changelog. Closed.