Bug #68649 Query with subqueries returns wrong result instead of a syntax error

• Submitted: 12 Mar 2013 9:43
• Modified: 12 Mar 2013 12:34
• Reporter: Olag Ulga
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.6.10, 5.1.40
• OS: Any
• CPU Architecture: Any
• Tags: Subquery syntax error aggregate

[12 Mar 2013 9:43] Olag Ulga

Description:
Under certain conditions a Query returns a wrong result ignoring the where clause instead of a syntax error 

How to repeat:
USE test;

CREATE TABLE `syntaxtest` (
  `id` int NOT NULL AUTO_INCREMENT,
  `f1` varchar(10) NOT NULL,
  `f2` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `test`.`syntaxtest`(`f1`,`f2`)VALUES('A', 'A');
INSERT INTO `test`.`syntaxtest`(`f1`,`f2`)VALUES('A', 'A');

INSERT INTO `test`.`syntaxtest`(`f1`,`f2`)VALUES('B', 'A');
INSERT INTO `test`.`syntaxtest`(`f1`,`f2`)VALUES('B', 'A');

INSERT INTO `test`.`syntaxtest`(`f1`,`f2`)VALUES('C', 'A');

-- Q1: This Query should fail beacause of the subquery, but returns all Rows ignoring the where clause
SELECT id, CONCAT(f1,f2) AS C
   FROM syntaxtest
   WHERE CONCAT(f1,f2) IN (SELECT CONCAT(f1,f2) /* syntax error f1 and f2 are not in the next subquery with the name tmp */
				   FROM (SELECT CONCAT(f1,f2), COUNT(1) AS cnt
						   FROM syntaxtest
						   GROUP BY CONCAT(f1,f1)
						   HAVING cnt > 1
						) AS tmp
				)
ORDER BY C;

-- Q2: The Subquery from Q1 behaves correct and fails
SELECT CONCAT(f1,f2) /* syntax error */
   FROM (SELECT CONCAT(f1,f2), COUNT(1) AS cnt
		   FROM syntaxtest
		   GROUP BY CONCAT(f1,f1)
		   HAVING cnt > 1
		) AS tmp;

Suggested fix:
The Query should not return a result. It should fail.

[12 Mar 2013 12:34] Olag Ulga

Not a Bug. 

Q4:
SELECT concat(f1,f2)
   FROM syntaxtest
   WHERE CONCAT(f1,f2) IN (SELECT concat(f1 /* refers to tmp.f1 if exists otherwise to syntaxtest.f1 */ ,f2)
	  FROM (SELECT f1 /* as x */, COUNT(1) AS cnt
									 FROM syntaxtest
									 GROUP BY f1
									 HAVING COUNT(1) > 1
								  ) AS tmp
   )

f1 in the first subquery refers to tmp.f1 if it exists otherwise ( if you uncomment /* as x */) it refers to syntaxtest.f1. 

means f1 from the ne