MySQL Bugs: #9995: Subselects across DBS

Bug #9995	Subselects across DBS
Submitted:	19 Apr 2005 9:16	Modified:	19 Apr 2005 17:41
Reporter:	Michael Cole	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Windows (Win32 / Linux)
Assigned to:		CPU Architecture:	Any

Description:
I have been running 4.1.1alpha-max-nt for months and we have many queries which have may sub selects and when we upgraded they broke.. No longer working.. Our SubSelects are across Mutiple Databases. Which is where i think the problem is occuring.

select codes1.Code,returns1.ReturnCode, year(returns1.date)as years, week(returns1.date) as weeks, returns1.ACode,(case when returns1.acode is not null then count(1) else 0 end) as counter FROM verification.codes as codes1 LEFT JOIN ((select * from `verification`.`returns` where year(date)='2005' and week(date)='16')returns1 ) on (codes1.Code = returns1.RemarksCodeMain) and (codes1.subcode=returns1.remarkscodesub) GROUP BY codes1.Code, weeks , returns1.ACode having weeks='16' and years='2005' 

We have 19 Different databases which we cross correlate the information at different times. 

This is a small query above. Some of our others cross many databases to provide the information we require on a realtime basis. 

If we have done this incorrectly is there a work around?

How to repeat:
Create two dbs with some columns then try to join the data from each one...

select * from testing join on test2.testing2 on test=test3

Error output from MYSQLCC...
[Localtestserver] ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on test2.testing2 on test=test3' at line 1

OK i submitted the wrong code to simulate the problem..

Maybe it has to do with the fact that we are connecting to one DB then running code which connects to a secondary DB?...

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'returns1 ) on (codes1.Code = returns1.RemarksCodeMain) and
(codes1.subcode=retur' at line 5

This is from the command line client..   Any ideas?

Hi Michael,

Problem lays in the brackets around your sub select in your join. Manual doesn't show that their should be () around, so I go for 'expected behaviour'.
This worked in 4.1.10 and not in 4.1.11. There were some bugfixes regarding sub selects/parser. I'll see if this is documented and/or expected.

mysql-4.1.10a-standard-log> select * from cr1 left join (select * from cross2.cr2 ) cr2 using (id);
mysql-4.1.10a-standard-log> select * from cr1 left join ((select * from cross2.cr2 ) cr2 ) using (id);
mysql-4.1.11-standard-log> select * from cr1 left join ((select * from cross2.cr2 ) cr2 ) using (id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cr2 ) using (id)' at line 1

Best regards,

Geert

select codes1.Code,returns1.ReturnCode, year(returns1.date)as years,
week(returns1.date) as weeks, returns1.ACode,(case when returns1.acode is not
null then count(1) else 0 end) as counter FROM verification.codes as codes1 LEFT
JOIN (select * from `verification`.`returns` where year(date)='2005' and
week(date)='15')returns1  on (codes1.Code = returns1.RemarksCodeMain) and
(codes1.subcode=returns1.remarkscodesub) GROUP BY codes1.Code, weeks ,
returns1.ACode having weeks='15' and years='2005' 

Thanks alot did not see that it is an easy fix from now on i think...
will edit the problem scripts....

Works on my 4.1.11 max Linux.. Mandrake...

Not a problem with DBs, but with brackets, the below query works up 4.1.10.

 select 1 from (select 2 a) x left join ( ( select 1 b) x1) on a=b;

and this works, on all including 4.1.11
 select 1 from (select 2 a) x left join   ( select 1 b) x1 on a=b;

sorry, somee cache on my end,, I saw only the originale