Re: Liberating Dependent Subqueries

[mnf]

Good idea, but doesn't work for me.

mysql> CALL constify('SELECT MAX(i) FROM shn1 GROUP BY n', @result);
Query OK, 0 rows affected (7.62 sec)

mysql> SELECT * FROM shn1 WHERE i IN ( @result );
+---------+---+------------+---+
| i | n | t | v |
+---------+---+------------+---+
| 7501830 | 1 | 1163553309 | 0 |
+---------+---+------------+---+
1 row in set (0.00 sec)

The problem is @result variable in "WHERE i IN (@result)" part. "IN" sees @result not as a list, For example

mysql> SET @result='1, 2, 3';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @result;
+---------+
| @result |
+---------+
| 1, 2, 3 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM shn1 WHERE i IN ( @result );
+---+---+---+----+
| i | n | t | v |
+---+---+---+----+
| 1 | 1 | 0 | -1 |
+---+---+---+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM shn1 WHERE i IN ( 1, 2, 3 );
+---+---+---+----+
| i | n | t | v |
+---+---+---+----+
| 1 | 1 | 0 | -1 |
| 2 | 2 | 0 | -1 |
| 3 | 3 | 0 | -1 |
+---+---+---+----+
3 rows in set (0.00 sec)

What am I missing?

Thsnks in advance,

Michael