Description:
The problems I am having are with a subquery (i.e. a nested SELECT statement) using "IN" and "ANY" keywords and it is always reproducible. The queries I supply in the "How To Repeat" section should all be valid ANSI compliant SQL strings and they do work in other database engines such as Oracle. In addition, the SELECT statements all work independently, but they do not work when one SELECT is a subquery of another using the "IN" and "ANY" keywords. The best way to go about this is follow all of the steps in the "How to repeat" section.
I have downloaded the latest MySQL v4.0.16 and tested it in Windows 2000/XP and
Red Hat Linux.
This is related to the MySQL documentation here:
http://www.mysql.com/doc/en/ANY_IN_SOME_subqueries.html
And the SQL syntax is stated by your documentation as the following:
<operand> <comparison operator> ANY (<subquery>)
<operand> IN (<subquery>)
<operand> <comparison operator> SOME (<subquery>)
How to repeat:
Please execute the following queries:
(1) create a test table:
CREATE TABLE ZZZTEST246 (MYID CHAR(20) PRIMARY KEY, SECID CHAR(20), USERNAME CHAR(40));
(2) add some test values:
INSERT INTO ZZZTEST246 (MYID,SECID,USERNAME) VALUES ('1000_A', '1000', 'mickey');
INSERT INTO ZZZTEST246 (MYID,SECID,USERNAME) VALUES ('1000_B', '1000', 'mickey');
INSERT INTO ZZZTEST246 (MYID,SECID) VALUES ('1000_C', '1000');
(3) none of the following queries work:
(i receive the following error message: "SYNTAX ERROR OR ACCESS VIOLATION: YOU HAVE AN ERROR IN YOUR SQL SYNTAX. CHECK THE MANUAL...")
SELECT MYID FROM ZZZTEST246 WHERE SECID IN (SELECT SECID FROM ZZZTEST246);
SELECT MYID FROM ZZZTEST246 WHERE SECID <> ANY (SELECT SECID FROM ZZZTEST246 WHERE MYID = '1000_A');
SELECT MYID FROM ZZZTEST246 WHERE SECID ANY (SELECT SECID FROM ZZZTEST246 WHERE MYID = '1000_A');
SELECT MYID FROM ZZZTEST246 WHERE SECID = ANY (SELECT SECID FROM ZZZTEST246 WHERE MYID = '1000_A');
SELECT MYID FROM ZZZTEST246 WHERE SECID IN (SELECT SECID FROM ZZZTEST246 WHERE USERNAME IS NOT NULL);
(4) At this point, I expect to see some type of data... instead, i just get the error message in (3).
Suggested fix:
There are no other alternative logics to solve this problem.
Description: The problems I am having are with a subquery (i.e. a nested SELECT statement) using "IN" and "ANY" keywords and it is always reproducible. The queries I supply in the "How To Repeat" section should all be valid ANSI compliant SQL strings and they do work in other database engines such as Oracle. In addition, the SELECT statements all work independently, but they do not work when one SELECT is a subquery of another using the "IN" and "ANY" keywords. The best way to go about this is follow all of the steps in the "How to repeat" section. I have downloaded the latest MySQL v4.0.16 and tested it in Windows 2000/XP and Red Hat Linux. This is related to the MySQL documentation here: http://www.mysql.com/doc/en/ANY_IN_SOME_subqueries.html And the SQL syntax is stated by your documentation as the following: <operand> <comparison operator> ANY (<subquery>) <operand> IN (<subquery>) <operand> <comparison operator> SOME (<subquery>) How to repeat: Please execute the following queries: (1) create a test table: CREATE TABLE ZZZTEST246 (MYID CHAR(20) PRIMARY KEY, SECID CHAR(20), USERNAME CHAR(40)); (2) add some test values: INSERT INTO ZZZTEST246 (MYID,SECID,USERNAME) VALUES ('1000_A', '1000', 'mickey'); INSERT INTO ZZZTEST246 (MYID,SECID,USERNAME) VALUES ('1000_B', '1000', 'mickey'); INSERT INTO ZZZTEST246 (MYID,SECID) VALUES ('1000_C', '1000'); (3) none of the following queries work: (i receive the following error message: "SYNTAX ERROR OR ACCESS VIOLATION: YOU HAVE AN ERROR IN YOUR SQL SYNTAX. CHECK THE MANUAL...") SELECT MYID FROM ZZZTEST246 WHERE SECID IN (SELECT SECID FROM ZZZTEST246); SELECT MYID FROM ZZZTEST246 WHERE SECID <> ANY (SELECT SECID FROM ZZZTEST246 WHERE MYID = '1000_A'); SELECT MYID FROM ZZZTEST246 WHERE SECID ANY (SELECT SECID FROM ZZZTEST246 WHERE MYID = '1000_A'); SELECT MYID FROM ZZZTEST246 WHERE SECID = ANY (SELECT SECID FROM ZZZTEST246 WHERE MYID = '1000_A'); SELECT MYID FROM ZZZTEST246 WHERE SECID IN (SELECT SECID FROM ZZZTEST246 WHERE USERNAME IS NOT NULL); (4) At this point, I expect to see some type of data... instead, i just get the error message in (3). Suggested fix: There are no other alternative logics to solve this problem.