A more general way to do it

[Shlomo Swidler]

Here's an even better way to liberate the "dependent" subquery:

mysql> CALL liberateSubquery("DELETE FROM book_tags WHERE tag_id IN (", "SELECT tag_id FROM tags WHERE LOCATE('bunk', tag_text)>0", ")");

What follows is the definition and discussion of the liberateSubquery stored procedure.

Once you have the "constify" stored procedure from the original post, the liberateSubquery stored procedure is defined as follows:

CREATE PROCEDURE `liberateSubquery`(IN outerQueryBegin_ TEXT, IN subQuery_ TEXT, IN outerQueryEnd_ TEXT)
BEGIN
SET @subQuery = subQuery_;
SET @subQueryResult = "";
SET @outerQueryBegin = outerQueryBegin_;
SET @outerQueryEnd = outerQueryEnd_;

CALL constify(@subQuery, @subQueryResult);

IF ISNULL(@subQueryResult) OR LENGTH(@subQueryResult) = 0 THEN
SET @subQueryResult = "NULL";
END IF;
SET @composedQuery = CONCAT(@outerQueryBegin, @subQueryResult, @outerQueryEnd);
PREPARE s1 FROM @composedQuery;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END

liberateSubquery uses constify to transform the subquery into a string, and then uses a prepared statement to compose the simplified outer query.

Note the special handling when the subquery returned no rows. This help us avoid a syntax error in the composed outer query. Otherwise the composed outer query would look like this:
DELETE FROM book_tags WHERE tag_id IN ();
which is clearly illegal.

This all works because the subquery involved here is not really dependent on the outer query, but MySQL thinks it is. For subqueries that really are dependent you can't use liberateSubquery.

When you use liberateSubquery, the subquery that you provide will be treated slightly differently than MySQL would treat a subquery: In MySQL a subquery can return multiple columns. But in liberateSubquery only the first column of the subquery's results are used. This is due to the way constify works (see the original post for more info).

liberateSubquery also lets you perform some types of queries that are not yet supported in MySQL. For example, let’s say we want to delete up to 100 tag associations for the first ten illegal tags. We might try this:

mysql> DELETE FROM book_tags WHERE tag_id IN (SELECT tag_id FROM tags WHERE LOCATE(‘bunk’, tag_text)>0 LIMIT 10) LIMIT 100;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

But you can accomplish this with liberateSubquery:
CALL liberateSubquery(“DELETE FROM book_tags WHERE tag_id IN (”, “SELECT tag_id FROM tags WHERE LOCATE(‘bunk’, tag_text)>0 LIMIT 10”, “) LIMIT 100”);



Edited 1 time(s). Last edit at 12/07/2006 04:28AM by Shlomo Swidler.