Bug #21002 Derived table not selecting from a "real" table fails in JOINs
Submitted: 12 Jul 2006 15:18 Modified: 2 Aug 2006 19:00
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.0.23,5.1 OS:Any (any)
Assigned to: Konstantin Osipov CPU Architecture:Any

[12 Jul 2006 15:18] Beat Vontobel 
Description:
Yet another new bug introduced in 5.0.23 and breaking our existing applications: Derived tables not selecting from any "real" underlying tables used in a JOIN with a fully specified "real" table fail with "ERROR 1046 (3D000): No database selected" if no default database is selected.

How to repeat:
mysql1:~ # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4628 to server version: 5.0.23-standard-log

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

mysql1:localhost-(none) [root]> use test    
Database changed
mysql1:localhost-test [root]> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.00 sec)

mysql1:localhost-test [root]> INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql1:localhost-test [root]> quit
Bye

mysql1:~ # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4628 to server version: 5.0.23-standard-log

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

mysql1:localhost-(none) [root]> SELECT * FROM t INNER JOIN ( SELECT 1 AS i ) AS t2 USING (i);
ERROR 1046 (3D000): No database selected

Suggested fix:
Restore correct behaviour from 5.0.22 and before.
[12 Jul 2006 15:45] MySQL Verification Team 
Behaviour was changed between 5.0.22 and 5.0.23 and I don't see anything related in 'Changes in release' section.

This is how it works in 5.0.22:

mysql> SELECT * FROM test.t INNER JOIN ( SELECT 1 AS i ) AS t2 USING (i);
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.22-community-nt-log |
+-------------------------+
1 row in set (0.01 sec)

and result from latest 5.0:

mysql> SELECT * FROM test.t INNER JOIN ( SELECT 1 AS i ) AS t2 USING (i);
ERROR 1046 (3D000): No database selected
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.24-debug-log |
+------------------+
1 row in set (0.00 sec)
[12 Jul 2006 23:18] Konstantin Osipov 
This is caused by the fix for Bug#19022.
[14 Jul 2006 22:49] 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/9185
[19 Jul 2006 10: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/9330
[19 Jul 2006 14:36] Tomash Brechko 
Approved by e-mail with minor suggestion.
[19 Jul 2006 18:32] 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/9345
[19 Jul 2006 21:32] Konstantin Osipov 
Pushed into the release clone of 5.0.24
[20 Jul 2006 17:31] Paul DuBois 
Noted in 5.0.24 changelog.

A SELECT that used a subquery in the FROM clause that did not select
from a table failed when the subquery was used in a join.

Setting bug report back to NDI pending push of fix into 5.1.
[2 Aug 2006 15:58] Konstantin Osipov 
Fixed in 5.1.12
[2 Aug 2006 19:00] Paul DuBois 
Noted in 5.1.12 changelog.