MySQL Bugs: #3664: Using subqueries for comparisons

Bug #3664	Using subqueries for comparisons
Submitted:	5 May 2004 16:15	Modified:	5 Jun 2004 7:31
Reporter:	ANDREA LANFRANCHI	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Windows (Windows 2000 Server)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Despite what the manual say comparisons among subqueries have a strange behavior (at least in my environment 4.1/Win).

select f1 from t1 where f1 = ANY ( select f1 from t2 ) -> Ok
select f1 from t1 where f1 <> ANY ( select f1 from t2 ) -> Ko
select f1 from t1 where f1 IN ( select f1 from t2 ) -> Ok
select f1 from t1 where f1 NOT IN ( select f1 from t2 ) -> Ok

As you can see there is a workaround but the "<> ANY" clause does not work at all.

How to repeat:
Simply perform a selection within a range of a subquery or around a negation of it using those two different dialects.

I was not able to reproduce incorrect behaviour of "<> ANY", can you provide 
full test suite (with data of tables)? 
 
(BTW, "<> ANY" is not equal to "NOT IN", which I decided looking on your 
subqueries set first time, which was cause of I puting 'verified' on this 
bugreport)

In the meanwhile I did go a little further in the analisys of the problem and well ... ermhh ... think there is some confusion.
I started here (http://dev.mysql.com/doc/mysql/en/ANY_IN_SOME_subqueries.html) where the statment "The word IN is an alias for = ANY. Thus these two statements are the same" brought me to the logical conclusion that "NOT IN" could be compared to "<> ANY". On the other hand I did miss the next chapter discussing about "ALL" clause which seems to fit my original intentions.

Anyway ... in my mind i thught "f1 <> ANY(..)" should return true when f1 differs from ANY of the selected values in the subquery assuming "any" as synonym for "each one". And that was my mistake.

Finally I realized that while "=ANY" is a synonym for "IN" the real synonym for "NOT IN" is "<>ALL". I think you'd better specify it in the help guide.

Thank you anyway for your support.

Thank you for bug report! I have passed your note to our documentation team.

I am using 4.0.18-max-debug  version on Windows Server 2000. 

I am using following query:

SELECT * FROM StateDemographics WHERE StDemo_ID IN 
  ( SELECT StDemo_ID FROM Ante_Visits WHERE mvisit_date BETWEEN '2000-12-31' AND '2004-06-03'
     Union  SELECT StDemo_ID FROM Ultra_94_Visits WHERE mvisit_date BETWEEN '2000-12-31' AND '2004-06-03'  )
  ORDER BY Last_Name, First_Name ; --does not work Error

I am getting following error.

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '( SELECT StD
emo_ID FROM Ante_Visits WHERE mvisit_date BETWEEN '

UNION clause works okay. Individual select statements of the subquery also work okay. Error is generated only when I use SELECT clause in a subquery for IN.

What do I need to do?

The table structures:

CREATE TABLE `ante_visits` (
  `visit_ID` int(11) NOT NULL auto_increment,
  `StDemo_ID` decimal(18,0) default NULL,
  `visit_no` decimal(18,0) NOT NULL default '1',
  `visit_date` varchar(50) NOT NULL default '',
  `afv_test` varchar(50) default NULL,
  `bpp_test` varchar(50) default NULL,
  `no_of_fetus` decimal(18,0) NOT NULL default '1',
  `nst_test` varchar(50) default NULL,
  `oct_test` varchar(50) default NULL,
  `mVisit_date` date default NULL,
  `AddedBy` varchar(50) NOT NULL default 'user_name()',
  `AddedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`visit_ID`)
) TYPE=InnoDB

CREATE TABLE `statedemographics` (
  `StDemo_ID` decimal(18,0) NOT NULL default '0',
  `File_Folder_ID` varchar(50) NOT NULL default '',
  `Medical_Record_Number` varchar(50) default NULL,
  `Current_Record_Number` varchar(50) default NULL,
  `Serial_Number` varchar(50) default '1',
  `First_Name` varchar(50) default NULL,
  `Last_Name` varchar(50) default NULL,
  `Address_Line_1` varchar(50) default NULL,
  `Address_City` varchar(50) default NULL,
  `Address_State` varchar(10) default NULL,
  `Address_ZipCode` varchar(10) default NULL,
  `Phone_Number` varchar(50) default NULL,
  `DOB` varchar(50) default NULL,
  `SSN` varchar(50) default NULL,
  `mDOB` date default NULL,
  `AddedBy` varchar(50) NOT NULL default 'user_name()',
  `AddedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`StDemo_ID`)
) TYPE=InnoDB

---------

CREATE TABLE `ultra_94_visits` (
  `Visit_ID` int(11) NOT NULL auto_increment,
  `StDemo_ID` decimal(18,0) NOT NULL default '0',
  `visit_no` decimal(18,0) NOT NULL default '0',
  `visit_date` varchar(50) NOT NULL default '',
  `no_of_fetus` decimal(18,0) NOT NULL default '1',
  `AVG_USGA` varchar(50) default NULL,
  `ASGA` varchar(50) default NULL,
  `BASED` varchar(50) default NULL,
  `USID` varchar(50) default NULL,
  `mVisit_date` date default NULL,
  `AddedBy` varchar(50) NOT NULL default 'user_name()',
  `AddedDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`Visit_ID`)
) TYPE=InnoDB

Thank you very much in advance.

Subquery support added in 4.1 version.