Bug #17881 SubQuery does not return an error when row does not exist
Submitted: 3 Mar 2006 0:32 Modified: 3 Mar 2006 8:49
Reporter: Christopher Fulton Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.17 OS:Linux (RHEL3)
Assigned to: CPU Architecture:Any

[3 Mar 2006 0:32] Christopher Fulton 
Description:
I searched for similar bugs, but could not find one.  I may be missing one though.  When doing a subquery with an =, mysql does not report an error when the row does not exist in a query.  

How to repeat:
Run this query....(Build does not have a BuildGroup column, so i would think that it should return an error)
SELECT Build.BuildId FROM Build, BuildGroup WHERE Build.BuildId = BuildGroup.BuildId AND  BuildGroup.BuildGroup = (SELECT BuildGroup FROM Build WHERE BuildId = '129414') 

Using these two tables...(i'm only showing the relavent information...i can send the whole SHOW CREATE TABLE if it would be useful
Build:
       BuildId - int auto-increment

BuildGroup
       BuildGroup - int auto-increment
       BuildId - int
[3 Mar 2006 0:33] Christopher Fulton 
I forgot to put my MySQL version...I'm using 5.0.17.
[3 Mar 2006 8:49] Hartmut Holzgraefe 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

As you didn't specify a table name with BuildGroup in the subquery it
picks BuildGroup.BuildGroup from the outer query for that field, your
query is equvalent to 

SELECT Build.BuildId 
  FROM Build, BuildGroup 
 WHERE Build.BuildId = BuildGroup.BuildId 
   AND BuildGroup.BuildGroup = (SELECT BuildGroup.BuildGroup FROM Build AS B2 WHERE B2.BuildId = '129414') 

whereas you have expected it to be like

SELECT Build.BuildId 
  FROM Build, BuildGroup 
 WHERE Build.BuildId = BuildGroup.BuildId 
   AND BuildGroup.BuildGroup = (SELECT B2.BuildGroup FROM Build AS B2 WHERE B2.BuildId = '129414') 

which does indeed produce an error