Bug #19396 LEFT JOINS on two views kills MySQL server (using MyODBC)
Submitted: 27 Apr 2006 9:15 Modified: 2 Jun 2006 13:20
Reporter: Martijn Starrenburg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0BK/5.1BK OS:Windows (Windows XP/Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[27 Apr 2006 9:15] Martijn Starrenburg 
Description:
I have created two catalogs:
One full catalogs called cap_full (contains three tables tbl_orderkop, tbl_orderregel, tbl_spoedorders with "all" data)
One mini catalog called cap_mini contains only 3 views to the tables in cap_full catalog. Something like:

CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cap_mini`.`tbl_orderkop` AS (select `cap_full`.`tbl_orderkop`.`Ordernummer` AS `Ordernummer`,`cap_full`.`tbl_orderkop`.`Entiteit` AS `Entiteit`,`cap_full`.`tbl_orderkop`.`Afdeling` AS `Afdeling`,`cap_full`.`tbl_orderkop`.`ReserveringsNummer` AS `ReserveringsNummer`,`cap_full`.`tbl_orderkop`.`Ordersoort` AS `Ordersoort`,`cap_full`.`tbl_orderkop`.`OrderDatum` AS `OrderDatum`,`cap_full`.`tbl_orderkop`.`KlantNummer` AS `KlantNummer`,`cap_full`.`tbl_orderkop`.`KlantOrderNummer` AS `KlantOrderNummer`,`cap_full`.`tbl_orderkop`.`KlantNaam` AS `KlantNaam`,`cap_full`.`tbl_orderkop`.`KlantAdres` AS `KlantAdres`,`cap_full`.`tbl_orderkop`.`KlantAdres2` AS `KlantAdres2`,`cap_full`.`tbl_orderkop`.`KlantPostcode` AS `KlantPostcode`,`cap_full`.`tbl_orderkop`.`KlantPlaats` AS `KlantPlaats`,`cap_full`.`tbl_orderkop`.`Transport` AS `Transport`,`cap_full`.`tbl_orderkop`.`Route` AS `Route`,`cap_full`.`tbl_orderkop`.`Project` AS `Project`,`cap_full`.`tbl_orderkop`.`TimeStamp` AS `TimeStamp`,`cap_full`.`tbl_orderkop`.`RouteSector` AS `RouteSector`,`cap_full`.`tbl_orderkop`.`GebruikersId` AS `GebruikersId`,`cap_full`.`tbl_orderkop`.`ExportDatumTijd` AS `ExportDatumTijd`,`cap_full`.`tbl_orderkop`.`LeverDatum` AS `LeverDatum` from `cap_full`.`tbl_orderkop` where (`cap_full`.`tbl_orderkop`.`LeverDatum` > _latin1'2006-04-25'));

.....

DROP VIEW IF EXISTS `tbl_spoedorders`;
CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cap_mini`.`tbl_spoedorders` AS select `cap_full`.`tbl_spoedorders`.`Ordernummer` AS `Ordernummer`,`cap_full`.`tbl_spoedorders`.`OrderRegel` AS `OrderRegel`,`cap_full`.`tbl_spoedorders`.`BerekendeLeverDatum` AS `BerekendeLeverDatum`,`cap_full`.`tbl_spoedorders`.`LeverDatum` AS `LeverDatum`,`cap_full`.`tbl_spoedorders`.`OK` AS `OK` from (`cap_full`.`tbl_spoedorders` join `cap_mini`.`tbl_orderkop` on((`tbl_orderkop`.`Ordernummer` = `cap_full`.`tbl_spoedorders`.`Ordernummer`)));

When I "link" the views to MSACCESS 2003 using the MyODBC driver everything works great! Even when I add a dummy index (like CREATE UNIQUE INDEX index1 ON tbl_OrderKop (Ordernummer)) the views are even updateable!

There is only one problem:
When I create a query with a LEFT JOIN on two views something like:

SELECT tbl_OrderRegel.Ordernummer, tbl_OrderRegel.OrderRegel, tbl_spoedorders.OK
FROM tbl_OrderRegel LEFT JOIN tbl_spoedorders ON (tbl_OrderRegel.OrderRegel = tbl_spoedorders.OrderRegel) AND (tbl_OrderRegel.Ordernummer = tbl_spoedorders.Ordernummer)
GROUP BY tbl_OrderRegel.Ordernummer, tbl_OrderRegel.OrderRegel, tbl_spoedorders.OK;

MSACCESS and MySQL server will crash!

If tried various versions of MySQL (5.0.20a, 5.1.7Beta) and MyODBC (3.51-11/12/13 even MyODBC 5.0.0alpha) But the result is the same.

INNER JOIN queries are no problem!

SELECT tbl_OrderRegel.Ordernummer, tbl_OrderRegel.OrderRegel, tbl_spoedorders.OK
FROM tbl_OrderRegel INNER JOIN tbl_spoedorders ON (tbl_OrderRegel.OrderRegel = tbl_spoedorders.OrderRegel) AND (tbl_OrderRegel.Ordernummer = tbl_spoedorders.Ordernummer)
GROUP BY tbl_OrderRegel.Ordernummer, tbl_OrderRegel.OrderRegel, tbl_spoedorders.OK;

When I run the LEFT JOIN query from MySQL query browser on the cap_mini catalog the result is OK! So it seems to be a MyODBC problem.

All catalogs/data and a small msaccess application are available for reproducing this bug!

How to repeat:
Just run the query:

SELECT tbl_OrderRegel.Ordernummer, tbl_OrderRegel.OrderRegel, tbl_spoedorders.OK
FROM tbl_OrderRegel LEFT JOIN tbl_spoedorders ON (tbl_OrderRegel.OrderRegel = tbl_spoedorders.OrderRegel) AND (tbl_OrderRegel.Ordernummer = tbl_spoedorders.Ordernummer)
GROUP BY tbl_OrderRegel.Ordernummer, tbl_OrderRegel.OrderRegel, tbl_spoedorders.OK;
[27 Apr 2006 13:56] Valeriy Kravchuk 
Thank you for a problem report. Have you tried to execute that problematic query from mysql command line client?

If it was a server crash, what is in server's error log?
[28 Apr 2006 6:47] Martijn Starrenburg 
Error message after 5 minutes

Attachment: error.doc (application/msword, text), 39.00 KiB.
[28 Apr 2006 6:55] Martijn Starrenburg 
Running the query from the CLI or Query browser doesn't kill the server. Only when I run the query from msaccess with linked views using MyODBC.
Maybe a stupid question but where can I find de server error log file?
[1 May 2006 13:14] Martijn Starrenburg 
hostname.err file

Attachment: lap100171.err (application/octet-stream, text), 33.10 KiB.
[9 May 2006 17:39] MySQL Verification Team 
Thank you for the feedback. Could you please provide the dump of your
test database, Access's mdb file and if you are able to test again please
start the server with option --log if you are starting it from prompt, i.e:

c:\mysql\bin>mysqld-nt --standalone --console --log
060509 14:32:50  InnoDB: Started; log sequence number 0 43655
060509 14:32:50 [Note] mysqld-nt: ready for connections.

or just add the log to your [mysqld] my.ini group:

[mysqld]
log

That option will create a file on your data directory like
host_name.log, then you can see the queries performed
by the server. When the server crash please see the last
query.

Thanks in advance.
[10 May 2006 7:21] Martijn Starrenburg 
Hereby the log file

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-max-nt, Version: 5.0.21-community-max-nt-log. started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
060510  8:58:33	      1 Connect     root@127.0.0.1 on cap_ccp
		      1 Query       SET SESSION interactive_timeout=1000000
		      1 Query       SELECT @@sql_mode
		      1 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		      1 Query       SET NAMES utf8
060510  8:58:34	      2 Connect     root@127.0.0.1 on cap_ccp
		      2 Query       SET SESSION interactive_timeout=1000000
		      2 Query       SELECT @@sql_mode
		      2 Query       SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
		      2 Query       SET NAMES utf8
		      2 Quit       
060510  8:58:39	      1 Query       SELECT DISTINCT User FROM mysql.user ORDER BY User
060510  8:58:40	      1 Query       SHOW FULL PROCESSLIST
060510  8:58:42	      1 Query       SHOW VARIABLES LIKE 'datadir'
		      1 Query       SHOW VARIABLES LIKE 'log_error'
		      1 Query       SHOW VARIABLES LIKE 'basedir'
060510  8:59:06	      3 Connect     root@127.0.0.1 on cap_mini
		      3 Query       SELECT Config, nValue FROM MSysConf
		      3 Field List  tbl_OrderKop 
		      3 Field List  tbl_OrderKop 
		      3 Query       SHOW KEYS FROM `tbl_orderkop`
		      3 Field List  tbl_OrderRegel 
060510  8:59:07	      3 Field List  tbl_OrderRegel 
		      3 Query       SHOW KEYS FROM `tbl_orderregel`
		      3 Field List  tbl_SpoedOrders 
		      3 Field List  tbl_SpoedOrders 
		      3 Query       SHOW KEYS FROM `tbl_spoedorders`
060510  8:59:29	      3 Query       SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-max-nt, Version: 5.0.21-community-max-nt-log. started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
060510  9:06:16	      1 Connect     root@127.0.0.1 on 
060510  9:06:20	      1 Query       SELECT DATABASE()
060510  9:06:24	      1 Query       SELECT DATABASE()
		      1 Init DB     cap_mini
060510  9:06:33	      1 Query       SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-max-nt, Version: 5.0.21-community-max-nt-log. started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
060510  9:11:15	      1 Connect     root@127.0.0.1 on 
060510  9:11:21	      1 Query       SELECT DATABASE()
		      1 Init DB     cap_mini
060510  9:11:53	      1 Query       SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`
060510  9:13:29	      1 Query       SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`

I think I found the problem.

Please look at the last two queries:

The last query is ok. The one before the last query kills the server. The syntax  contains {oj ......} near the FROM. I manualy removed the {oj....} part and runned the query from the cli. It workt. Running the query with the {oj...} from the cli will kill the server as well.

What does {oj...} mean. Dos it mean something?
[10 May 2006 12:48] MySQL Verification Team 
Thank you for the feedback. That synatx I guess it is created by Access,
I remember similar syntax with CrystaLreport, however I tried to run that
query and I got a syntax error as expected:

mysql> SELECT `tbl_orderregel`.`OrderRegel`
    -> ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT JOIN `tbl_spoedorders`
    -> ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND
    -> (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY
    -> `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your

However you commented you was able to crash the server with mysql client
as well. Were your original tables created with previous version than 5.0.21?
and you can test creating the tables with same dump file you provided?

Thanks in advance.
[10 May 2006 13:11] Martijn Starrenburg 
Same here!
If I use the next syntax I get an warning that my syntax is not correct:

SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;

But if you change LEFT JOIN to LEFT OUTER JOIN (like access/MyODBC did) it will kill my server!

So please try

SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;

Could you please confirm this?

By the way my table are created by MySQL 4.1
[10 May 2006 13:26] MySQL Verification Team 
Thank you for the feedback. I was able to repeat the crash with your
last query:

mysql> SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj
    -> `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON
    -> ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` )
    -> AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) )
    -> } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

I will test on Linux and Windows with current source. Since it is a server
crash I changed the Category to Server.
[10 May 2006 13:47] MySQL Verification Team 
Thank you for the bug report. Also repeatable on Linux with that query
syntax:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/bug19396/TestDatabases_cap_full_and_cap_mini.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj
    -> `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON
    -> ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND
    -> (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY
    -> `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;
ERROR 2013 (HY000): Lost connection to MySQL server during query

060511 10:33:06 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.22-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 5912)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 5912)]
0x082490da in simplify_joins (join=0x8eada30, join_list=0x8e6d5c0, conds=0x8ea8538, top=true) at sql_select.cc:7590
7590            table->dep_tables&= ~table->table->map;
(gdb) bt full
#0  0x082490da in simplify_joins (join=0x8eada30, join_list=0x8e6d5c0, conds=0x8ea8538, top=true) at sql_select.cc:7590
        used_tables = 12
        not_null_tables = 2
        prev_table = (TABLE_LIST *) 0x0
        _db_func_ = 0x86cdb34 "alloc_root"

miguel@hegel:~/dbs/5.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/bug19396/TestDatabases_cap_full_and_cap_mini.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj
    -> `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON
    -> ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND
    -> (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY
    -> `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[13 May 2006 18:56] 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/6342
[13 May 2006 21:07] Igor Babaev 
ChangeSet
  1.2136 06/05/13 11:56:05 igor@rurik.mysql.com +3 -0
  Fixed bug #19396: a crash for a an outer join operation 
  over two views when using syntax with curly braces.
  Each outer join operation must be placed in a separate
  nest. This was not done when the syntax with curly braces
  was used. In some cases, in particular, for queries with outer
  join operation over views it could cause a crash.

The fix will appear in 5.0.22 and 5.1.10
[15 May 2006 19:36] Paul DuBois 
Noted in 5.0.22, 5.1.10 changelogs.

An outer join of two views that was written using <literal>{
OJ ... }</literal> syntax could cause a server crash.
[29 May 2006 7:45] Martijn Starrenburg 
I just installed version 5.0.22. But the bug still exists.
I think this patch didn't make it in release 5.0.22.
Am I right?

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-max-nt, Version: 5.0.22-community-max-nt-log. started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
060529  9:38:18	      1 Connect     root@127.0.0.1 on cap_mini
060529  9:38:19	      1 Query       SELECT Config, nValue FROM MSysConf
		      1 Field List  tbl_OrderKop 
		      1 Field List  tbl_OrderKop 
		      1 Query       SHOW KEYS FROM `tbl_orderkop`
		      1 Field List  tbl_OrderRegel 
		      1 Field List  tbl_OrderRegel 
		      1 Query       SHOW KEYS FROM `tbl_orderregel`
060529  9:38:20	      1 Field List  tbl_SpoedOrders 
		      1 Field List  tbl_SpoedOrders 
		      1 Query       SHOW KEYS FROM `tbl_spoedorders`
060529  9:38:22	      1 Query       SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) } GROUP BY `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`
[2 Jun 2006 12:46] Martijn Starrenburg 
It seems to be that version 5.0.22 is a security fix release only.
I just checked the changelog for version 5.0.23. and it seems to be that the bug will be fixed in release 5.0.23.
I think I need to wait a little longer.
[2 Jun 2006 13:20] MySQL Verification Team 
Yes the current 5.0.23 version is fixed:

mysql> SELECT `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`  FROM {oj
    -> `tbl_orderregel` LEFT OUTER JOIN `tbl_spoedorders` ON
    -> ((`tbl_orderregel`.`Ordernummer` = `tbl_spoedorders`.`Ordernummer` ) AND
    -> (`tbl_orderregel`.`OrderRegel` = `tbl_spoedorders`.`OrderRegel` ) ) }
    -> GROUP BY
    -> `tbl_orderregel`.`OrderRegel` ,`tbl_spoedorders`.`OK`;
+------------+------+
| OrderRegel | OK   |
+------------+------+
|          1 | NULL | 
|          2 | NULL | 
|          3 | NULL | 
|          4 | NULL | 
|          5 | NULL | 
|          6 | NULL | 
|          7 | NULL | 
|          8 | NULL | 
|          9 | NULL | 
|         10 | NULL | 
|         11 | NULL | 
|         12 | NULL | 
|         13 | NULL | 
|         14 | NULL | 
|         15 | NULL | 
|         16 | NULL | 
|         17 | NULL | 
|         18 | NULL | 
|         19 | NULL | 
|         20 | NULL | 
|         21 | NULL | 
|         22 | NULL | 
+------------+------+
22 rows in set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.23-debug | 
+--------------+
1 row in set (0.00 sec)

mysql>