Bug #82189 DELETE via the alias of fully qualified table reference fails with ERROR 1046

• Submitted: 11 Jul 2016 21:19
• Modified: 12 Jul 2016 9:15
• Reporter: Daniel Fisher
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.12 MySQL Community Server (GPL), 5.6.31, 5.7.13
• OS: Any
• CPU Architecture: Any
• Tags: ERROR 1046, No database selected

[11 Jul 2016 21:19] Daniel Fisher

Description:
A DELETE FROM ... USING ... JOIN ... WHERE ... statement that targets a table via an alias established in a USING . . . JOIN clause fails with ERROR 1046. This occurs in a session in which no default database has beenestalbished. The equivalent DELETE ... FROM ... JOIN ... WHERE ... produces the same error.

How to repeat:
Create the following databse and tables:

CREATE DATABASE test;

CREATE TABLE test.testA (
    colA INT PRIMARY KEY
);

CREATE TABLE test.testB (
    colB INT PRIMARY KEY,
    colA INT NOT NULL,
    deleteMe INT NOT NULL,
    CONSTRAINT fk_B_A FOREIGN KEY (colA) REFERENCES test.testA (colA)
);

In a fresh session in which no default database has been established, either of these DELETE statements wil produce ERROR 1046: 

DELETE FROM B
USING test.testA A
JOIN test.testB B ON A.colA = B.colB
WHERE B.deleteMe <> 0;
# ERROR 1046

DELETE B
FROM test.testA A
JOIN test.testB B ON A.colA = B.colB
WHERE B.deleteMe <> 0;
# ERROR 1046

The error is no longer thrown if one precedes the DELETE with any proper USE database statement. However, if the default database is dropped, the error will occur again:

CREATE DATABASE FOO;

USE FOO:

DELETE FROM B
USING test.testA A
JOIN test.testB B ON A.colA = B.colB
WHERE B.deleteMe <> 0;
# This works

DROP DATABASE FOO;

DELETE FROM B
USING test.testA A
JOIN test.testB B ON A.colA = B.colB
WHERE B.deleteMe <> 0;
# ERROR 1046

[11 Jul 2016 21:23] Daniel Fisher

In the how to repeat section, the "USE FOO:" line should read "USE FOO;". This mistake occurred in entering the report and not in reproducing the problem. Sorry.

[12 Jul 2016 9:15] MySQL Verification Team

Hello Daniel Fisher,

Thank you for the report and test case.
Observed with 5.7.13/5.6.31 builds.

Thanks,
Umesh