MySQL Bugs: #73590: MySQL returns "ERROR 1046 (3D000): No database selected" when executing "Update

Bug #73590	MySQL returns "ERROR 1046 (3D000): No database selected" when executing "Update
Submitted:	14 Aug 2014 17:56	Modified:	15 Aug 2014 15:49
Reporter:	Flavian C	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.5.26 5.6.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If we use multi-join inside a join the update query asks for DB name ERROR 1046 (3D000): No database selected

How to repeat:
Im getting no database select when using JOIN inside a JOIN.

UPDATE 
DB1.TABLE1 as a
JOIN (SELECT * FROM DB1.TABLE2 as b,(SELECT * DB1.TABLE2) as c WHERE b.col1 = c.col1) ea
JOIN (SELECT SUM(d.size) FROM DB1.TABLE2 as d WHERE d.col1=xx) ef
ON a.col1=ea.col1 and a.col1=ef.col1
set
xxxx

Also if ea alias join if we convert the inside join to subquery it works properly but takes a performance hit.

i.e
UPDATE 
DB1.TABLE1 as a
JOIN (SELECT * FROM DB1.TABLE2 as b WHERE b.col1 = (SELECT c.col1 DB1.TABLE2 as c)) ea
JOIN (SELECT SUM(d.size) FROM DB1.TABLE2 as d WHERE d.col1=xx) ef
ON a.col1=ea.col1 and a.col1=ef.col1
set
xxxx

Thank you for the report.

Please specify which exact minor version of MySQL server you use and provide output of SHOW CREATE TABLE for all involved tables.

Just for test case you can do:  Do not do " use dbname "

login into mysql : execute the following

 CREATE TABLE flavian.`test1` (
  `col1` int(3) DEFAULT NULL,
  `col2` int(3) DEFAULT NULL,
  `col3` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 CREATE TABLE flavian.`test2` (
  `col1` int(3) DEFAULT NULL,
  `col2` int(3) DEFAULT NULL,
  `col3` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

mysql> update flavian.test1 a JOIN (select * from flavian.test2 b, (select * from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.col3 where a.col1=1 and a.col2=2;
ERROR 1046 (3D000): No database selected

mysql> update flavian.test1 a JOIN (select * from flavian.test2 b where b.col1=(select c.col1 from flavian.test2 c where c.col1=1 limit 1)) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected

Thank you for the report.

Verified as described. If run first query with default database correct error is returned:

mysql> update flavian.test1 a JOIN (select * from flavian.test2 b,
    -> (select * from flavian.test2 c where c.col1=1 limit 1) d where
    -> b.col1=d.col1) ea JOIN (select sum(e.col2) from flavian.test2 e where
    -> e.col1=1) ef set a.col2=ea.col2, a.col3=ef.col3 where a.col1=1 and
    -> a.col2=2;
ERROR 1060 (42S21): Duplicate column name 'col1'

Sorry try this: But dont use default db name. 

mysql -uroot
mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b, (select c.col1 from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
ERROR 1046 (3D000): No database selected
mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b where b.col1=(select c.col1 from flavian.test2 c where c.col1=1 limit 1)) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected, 1 warning (0.15 sec)
Rows matched: 0  Changed: 0  Warnings: 0

if you do " use flavian " the query executes
mysql -uroot
mysql> use flavian;
mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b, (select c.col1 from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected
mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b where b.col1=(select c.col1 from flavian.test2 c where c.col1=1 limit 1)) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected

As you can see the 1st query is using join inside a join even though dbname was mentioned in all table still got " ERROR 1046 (3D000): No database selected "

2nd query is converted to a subquery, this executes without any error.

If you specify " use dbname " both the query executes properly.

mysql> update flavian.test1 a JOIN (select * from flavian.test2 b, (select * from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.col3 where a.col1=1 and a.col2=2;
ERROR 1046 (3D000): No database selected
mysql> use flavian;                                                                                                                                                    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> update flavian.test1 a JOIN (select * from flavian.test2 b, (select * from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.col3 where a.col1=1 and a.col2=2;
ERROR 1060 (42S21): Duplicate column name 'col1'

Sveta Smirnova Please try the test case provided below. 

mysql -uroot
mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b, (select c.col1 from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
ERROR 1046 (3D000): No database selected

mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b where b.col1=(select c.col1 from flavian.test2 c where c.col1=1 limit 1)) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected, 1 warning (0.15 sec)
Rows matched: 0  Changed: 0  Warnings: 0

if you do " use flavian " the query executes
mysql -uroot
mysql> use flavian;
mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b, (select c.col1 from flavian.test2 c where c.col1=1 limit 1) d where b.col1=d.col1) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected

mysql> update flavian.test1 a JOIN (select b.col2 from flavian.test2 b where b.col1=(select c.col1 from flavian.test2 c where c.col1=1 limit 1)) ea JOIN (select sum(e.col2) size1 from flavian.test2 e where e.col1=1) ef set a.col2=ea.col2, a.col3=ef.size1 where a.col1=1 and a.col2=2;
Query OK, 0 rows affected

As you can see the 1st query is using join inside a join even though dbname was mentioned in all table still got " ERROR 1046 (3D000): No database selected "

2nd query is converted to a subquery, this executes without any error.

If you specify " use dbname " both the query executes properly.

Sveta Smirnova. Any update on it?

Same issue in this update query:

SET @odo = 0;
SET @ship = '';
UPDATE trans_dm.deliveries,
  (
    SELECT StopNum,
      IF(@ship = SHIPMENTNUM, IF(@odo = 0, 0, IF(Odometer = Distance, 0, Odometer - @odo)), 0) AS DistDG,
      @odo:= Odometer AS Odometer,
      @ship:=SHIPMENTNUM AS SHIPMENTNUM, 
      Distance
    FROM (
      SELECT SHIPMENTNUM, 
        STOP_NUM AS StopNum,
        STOP_ODO_READING AS Odometer,
        SUM(STOP_DISTANCE) AS Distance
      FROM trans_dm.deliveries
      WHERE FLAG = 'Actual' AND
        STOP_ODO_READING>0
      GROUP BY SHIPMENTNUM, STOP_NUM, STOP_ODO_READING
      ORDER BY 1, 2
    ) AS _tmp0
  ) AS _tmp1
SET CalcDist = _tmp1.DistDG
WHERE deliveries.SHIPMENTNUM = _tmp1.SHIPMENTNUM AND deliveries.STOP_NUM = _tmp1.StopNum AND deliveries.FLAG = 'Actual';

I believe it comes from the "FROM (" subquery. If I issue the "SELECT DBNAME" statement it works fine.