Bug #65360 Update does not work with double-nested queries without default database

• Submitted: 18 May 2012 14:12
• Modified: 18 May 2012 14:46
• Reporter: Alex Bolenok
• Status: Verified
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.1.61, 5.5.23
• OS: Any
• CPU Architecture: Any
• Tags: UPDATE

[18 May 2012 14:12] Alex Bolenok

Description:
If double-nested queries are using in a multiple-table update, and not database is selected, the statement does not parse with

ERROR 1046 (3D000): No database selected

How to repeat:
-- Run this with no default database selected

CREATE DATABASE IF NOT EXISTS a;

CREATE TABLE IF NOT EXISTS a.target (id INT NOT NULL PRIMARY KEY, value INT NOT NULL);

DELETE
FROM    a.target;

INSERT
INTO    a.target
VALUES  (1, 1);

-- Update 1

UPDATE  a.target t
JOIN    (
        SELECT  1 AS id, 1 AS value
        ) q
USING   (id)
SET     t.value = t.value + q.value;

-- Update 2

UPDATE  a.target t
JOIN    (
        SELECT  *
        FROM    (
                SELECT  1 AS id, 1 AS value
                ) q
        ) q
USING   (id)
SET     t.value = t.value + q.value;

USE a;

-- Update 1

UPDATE  a.target t
JOIN    (
        SELECT  1 AS id, 1 AS value
        ) q
USING   (id)
SET     t.value = t.value + q.value;

-- Update 2

UPDATE  a.target t
JOIN    (
        SELECT  *
        FROM    (
                SELECT  1 AS id, 1 AS value
                ) q
        ) q
USING   (id)
SET     t.value = t.value + q.value;

[18 May 2012 14:46] Valeriy Kravchuk

Thank you for the bug report. Verified with 5.1.61 also:

macbook-pro:5.1 openxs$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE IF NOT EXISTS a;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> CREATE TABLE IF NOT EXISTS a.target (id INT NOT NULL PRIMARY KEY, value INT NOT NULL);
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> DELETE
    -> FROM    a.target;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT
    -> INTO    a.target
    -> VALUES  (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> -- Update 1
mysql> 
mysql> UPDATE  a.target t
    -> JOIN    (
    ->         SELECT  1 AS id, 1 AS value
    ->         ) q
    -> USING   (id)
    -> SET     t.value = t.value + q.value;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> -- Update 2
mysql> 
mysql> UPDATE  a.target t
    -> JOIN    (
    ->         SELECT  *
    ->         FROM    (
    ->                 SELECT  1 AS id, 1 AS value
    ->                 ) q
    ->         ) q
    -> USING   (id)
    -> SET     t.value = t.value + q.value;
ERROR 1046 (3D000): No database selected
...

So, this is not a recent regression.

[18 May 2012 14:48] MySQL Verification Team

see also http://bugs.mysql.com/bug.php?id=61376

[15 Aug 2014 15:53] Flavian C

Any solution for this BUG?  This topic is of 2012 and we are in 2014.  This bug is still there in 5.5, 5.6.21 latests version also