Description:
Table aliases in multi table deletes don't seem to work correctly if no default database is set. They do work correctly if an unrelated database is set as default.
How to repeat:
mysql 8.0.22 > create database syslog;
Query OK, 1 row affected (0.03 sec)
mysql 8.0.22 > create table syslog.foo (
-> id int,
-> constraint uk_id primary key(id)
-> );
Query OK, 0 rows affected (0.19 sec)
mysql 8.0.22 >
mysql 8.0.22 > insert into syslog.foo values(1);
Query OK, 1 row affected (0.03 sec)
mysql 8.0.22 > insert into syslog.foo values(2);
Query OK, 1 row affected (0.05 sec)
mysql 8.0.22 >
mysql 8.0.22 > create table syslog.bar (
-> id int,
-> constraint uk_id primary key(id)
-> );
Query OK, 0 rows affected (0.13 sec)
mysql 8.0.22 >
mysql 8.0.22 > insert into syslog.bar values(1);
Query OK, 1 row affected (0.01 sec)
mysql 8.0.22 > insert into syslog.bar values(2);
Query OK, 1 row affected (0.02 sec)
mysql 8.0.22 > delete from a1
-> using syslog.foo as a1 inner join syslog.bar as b1
-> where a1.id=b1.id and b1.id=2;
ERROR 1046 (3D000): No database selected
mysql 8.0.22 > select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql 8.0.22 > use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql 8.0.22 > delete from a1 using syslog.foo as a1 inner join syslog.bar as b1 where a1.id=b1.id and b1.id=2;
Query OK, 1 row affected (0.01 sec)
With no default database and without aliases:
mysql 8.0.22 > delete from syslog.foo using syslog.foo inner join syslog.bar where syslog.foo.id=syslog.bar.id and syslog.bar.id=2;
Query OK, 0 rows affected (0.00 sec)
Description: Table aliases in multi table deletes don't seem to work correctly if no default database is set. They do work correctly if an unrelated database is set as default. How to repeat: mysql 8.0.22 > create database syslog; Query OK, 1 row affected (0.03 sec) mysql 8.0.22 > create table syslog.foo ( -> id int, -> constraint uk_id primary key(id) -> ); Query OK, 0 rows affected (0.19 sec) mysql 8.0.22 > mysql 8.0.22 > insert into syslog.foo values(1); Query OK, 1 row affected (0.03 sec) mysql 8.0.22 > insert into syslog.foo values(2); Query OK, 1 row affected (0.05 sec) mysql 8.0.22 > mysql 8.0.22 > create table syslog.bar ( -> id int, -> constraint uk_id primary key(id) -> ); Query OK, 0 rows affected (0.13 sec) mysql 8.0.22 > mysql 8.0.22 > insert into syslog.bar values(1); Query OK, 1 row affected (0.01 sec) mysql 8.0.22 > insert into syslog.bar values(2); Query OK, 1 row affected (0.02 sec) mysql 8.0.22 > delete from a1 -> using syslog.foo as a1 inner join syslog.bar as b1 -> where a1.id=b1.id and b1.id=2; ERROR 1046 (3D000): No database selected mysql 8.0.22 > select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql 8.0.22 > use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql 8.0.22 > delete from a1 using syslog.foo as a1 inner join syslog.bar as b1 where a1.id=b1.id and b1.id=2; Query OK, 1 row affected (0.01 sec) With no default database and without aliases: mysql 8.0.22 > delete from syslog.foo using syslog.foo inner join syslog.bar where syslog.foo.id=syslog.bar.id and syslog.bar.id=2; Query OK, 0 rows affected (0.00 sec)