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

[11 Nov 2004 8:17] Adrian Benko
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 ???
[11 Nov 2004 14:44] Hartmut Holzgraefe
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?
[12 Nov 2004 8:55] Adrian Benko
I wanted to upload data, but zipped data has 14 MB
[12 Nov 2004 8:56] Adrian Benko
I uploaded only script for create tables
[12 Nov 2004 14:46] MySQL Verification Team
You can upload your data to  ftp://ftp.mysql.com/pub/mysql/upload/
[15 Nov 2004 8:14] Adrian Benko
I tried it, but I had no permission
[19 Nov 2004 13:55] MySQL Verification Team
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?
[20 Nov 2004 8:34] Adrian Benko
How can I log to this ftp ?
[20 Nov 2004 17:00] MySQL Verification Team
this is doable with ftp client.

You will not see that directory, but upload should work.
[21 Nov 2004 9:08] Adrian Benko
I cannot upload to your ftp.

I uploaded to our ftp:

ftp://www.solveit.sk
user: mysql
password: mysql123
[13 Dec 2004 20:49] MySQL Verification Team
Sorry for delayed reply.
On the ftp://www.solveit.sk/ I see only one file 1.4.0.zip. Is this the uploaded data?
[14 Dec 2004 8:04] Adrian Benko
yes, this is zipped data
[14 Dec 2004 12:00] MySQL Verification Team
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?
[14 Feb 2005 22:54] Bugs System
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".