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
Triage: Needs Triage: D4 (Minor)

[14 Aug 2014 17:56] Flavian C
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
[14 Aug 2014 18:00] Sveta Smirnova
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.
[14 Aug 2014 18:28] Flavian C
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
[14 Aug 2014 18:35] Sveta Smirnova
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'
[14 Aug 2014 18:44] Flavian C
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
[14 Aug 2014 18:54] Flavian C
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.
[14 Aug 2014 19:04] Sveta Smirnova
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'
[14 Aug 2014 19:11] Flavian C
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.
[15 Aug 2014 15:49] Flavian C
Sveta Smirnova. Any update on it?
[7 Apr 2016 13:54] damien goor
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.