MySQL Bugs: #6570: Subquery not connected to query in select statement

Bug #6570	Subquery not connected to query in select statement
Submitted:	11 Nov 2004 8:17	Modified:	14 Jan 2005 12:00
Reporter:	Adrian Benko	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.7	OS:	Windows (MS Windows)
Assigned to:		CPU Architecture:	Any

Description:
when I use subqueries in select statement:
select TOBJECT_ID, 
count(distinct OBJECT_ID) as COUNT_OBJ,  
if(MLOC_VALUE is null,TOBJECT_LONGNAME,MLOC_VALUE) as NAME,  
if(MLOC_VALUE_2 is null,TOBJECT_LONGNAME2,MLOC_VALUE_2) as SECTION, 
(select RMASK_ID 
 from RESULT_MASK 
 where RMASK_TOBJECT_ID = TOBJECT_ID // !!!!! this works
   and RMASK_USER_ID in (2,52)
 order by RMASK_DEFAULT desc, RMASK_USER_ID desc, RMASK_TOBJECT_ID desc 
 limit 1) 
as RMASK
from OBJECT, TYPE_OBJECT left outer join MASK_LOCALIZATION  on (MLOC_KEY = TOBJECT_ID and MLOC_LANG_ID = 1 and MLOC_TYPE = 0), AVALUES v0 
where OBJECT_TOBJECT_ID = TOBJECT_ID 
and v0.AVALUES_OBJECT_ID = OBJECT_ID and v0.AVALUES_VALUE like 'FA0%' 
group by OBJECT_TOBJECT_ID 
order by 3;

but 

select TOBJECT_ID, 
count(distinct OBJECT_ID) as COUNT_OBJ,  
if(MLOC_VALUE is null,TOBJECT_LONGNAME,MLOC_VALUE) as NAME,  
if(MLOC_VALUE_2 is null,TOBJECT_LONGNAME2,MLOC_VALUE_2) as SECTION, 
(select RMASK_ID 
 from RESULT_MASK 
 where RMASK_TOBJECT_ID in (106,150,163,178) // !!!!! this shutdown server
   and RMASK_USER_ID in (2,52)
 order by RMASK_DEFAULT desc, RMASK_USER_ID desc, RMASK_TOBJECT_ID desc 
 limit 1) 
as RMASK
from OBJECT, TYPE_OBJECT left outer join MASK_LOCALIZATION  on (MLOC_KEY = TOBJECT_ID and MLOC_LANG_ID = 1 and MLOC_TYPE = 0), AVALUES v0 
where OBJECT_TOBJECT_ID = TOBJECT_ID 
and v0.AVALUES_OBJECT_ID = OBJECT_ID and v0.AVALUES_VALUE like 'FA0%' 
group by OBJECT_TOBJECT_ID 
order by 3;

second command everytime shutdown server with Microsoft Windows error (mysqld-nt.exe has encountered a problem and needs to close.  We are sorry for the inconvenience.)

How to repeat:
select TOBJECT_ID, 
count(distinct OBJECT_ID) as COUNT_OBJ,  
if(MLOC_VALUE is null,TOBJECT_LONGNAME,MLOC_VALUE) as NAME,  
if(MLOC_VALUE_2 is null,TOBJECT_LONGNAME2,MLOC_VALUE_2) as SECTION, 
(select RMASK_ID 
 from RESULT_MASK 
 where RMASK_TOBJECT_ID in (106,150,163,178) // !!!!! this shutdown server
   and RMASK_USER_ID in (2,52)
 order by RMASK_DEFAULT desc, RMASK_USER_ID desc, RMASK_TOBJECT_ID desc 
 limit 1) 
as RMASK
from OBJECT, TYPE_OBJECT left outer join MASK_LOCALIZATION  on (MLOC_KEY = TOBJECT_ID and MLOC_LANG_ID = 1 and MLOC_TYPE = 0), AVALUES v0 
where OBJECT_TOBJECT_ID = TOBJECT_ID 
and v0.AVALUES_OBJECT_ID = OBJECT_ID and v0.AVALUES_VALUE like 'FA0%' 
group by OBJECT_TOBJECT_ID 
order by 3;

Suggested fix:
I think that problem is: 
subquery is not connected to query ???

can you please add CREATE TABLE statements for the tables involved (or even better: upload table dumps) so that we can try to reproduce the problem using your query?

I wanted to upload data, but zipped data has 14 MB

I uploaded only script for create tables

You can upload your data to  ftp://ftp.mysql.com/pub/mysql/upload/

I tried it, but I had no permission

I can't repeat it with my test data.

It's a strange that you got error. You should be able to upload files to ftp://ftp.mysql.com/pub/mysql/upload/

Could you try upload data again?

How can I log to this ftp ?

this is doable with ftp client.

You will not see that directory, but upload should work.

I cannot upload to your ftp.

I uploaded to our ftp:

ftp://www.solveit.sk
user: mysql
password: mysql123

Sorry for delayed reply.
On the ftp://www.solveit.sk/ I see only one file 1.4.0.zip. Is this the uploaded data?

yes, this is zipped data

I've found several .sql files in the DATA directory and restored tables and data, but both SELECTs worked fine and returned empty result set.

mysql> select TOBJECT_ID,
    -> count(distinct OBJECT_ID) as COUNT_OBJ,
    -> if(MLOC_VALUE is null,TOBJECT_LONGNAME,MLOC_VALUE) as NAME,
    -> if(MLOC_VALUE_2 is null,TOBJECT_LONGNAME2,MLOC_VALUE_2) as SECTION,
    -> (select RMASK_ID
    ->  from RESULT_MASK
    ->  where RMASK_TOBJECT_ID in (106,150,163,178)
    ->    and RMASK_USER_ID in (2,52)
    ->  order by RMASK_DEFAULT desc, RMASK_USER_ID desc, RMASK_TOBJECT_ID desc
    ->  limit 1)
    -> as RMASK
    -> from OBJECT, TYPE_OBJECT left outer join MASK_LOCALIZATION  on (MLOC_KEY =
    -> TOBJECT_ID and MLOC_LANG_ID = 1 and MLOC_TYPE = 0), AVALUES v0
    -> where OBJECT_TOBJECT_ID = TOBJECT_ID
    -> and v0.AVALUES_OBJECT_ID = OBJECT_ID and v0.AVALUES_VALUE like 'FA0%'
    -> group by OBJECT_TOBJECT_ID
    -> order by 3;
Empty set (0.00 sec)

Did I miss something?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".