Pages

Your Ad Here

This Blog is not to read or go through

because, I have never been such a mess


Search the blog instead

Sunday, March 9, 2008

Error:1235- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

If you try the following you will find it fails in mysql version 4.1,

SELECT
COUNT(*)
FROM
TABLE_1
WHERE
ROW (PK_PART_1, PK_PART_2) IN
(
SELECT
PK_PART_1, PK_PART_2
FROM
TABLE_2
LIMIT
10 -- This is the basis of the whole idea
)
AND
Whatever;

The above fails with the warning ...

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

However, you can get the desired behaviour with a slight rewrite...

SELECT
COUNT(*)
FROM
TABLE_1
INNER JOIN
(
SELECT
PK_PART_1, PK_PART_2
FROM
TABLE_2
LIMIT
10 -- HEY HEY!
) AS VIRTUAL_TABLE_2
ON
TABLE_1.PK_PART_1 = TABLE_2.PK_PART_1 AND
TABLE_1.PK_PART_2 = TABLE_2.PK_PART_2
WHERE
Whatever;

And it works like a charm!

1 comment:

Unknown said...

It works more than just a charm :) Big UP

Your Ad Here