A more general way to do it
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.
Subject
Views
Written By
Posted
14651
November 27, 2006 05:50PM
3896
November 28, 2006 12:57AM
3546
November 28, 2006 03:09AM
3484
November 28, 2006 06:29PM
4258
November 30, 2006 05:39PM
3162
December 01, 2006 09:35AM
2869
December 03, 2006 06:21PM
2884
December 03, 2006 08:18PM
2895
December 05, 2006 06:11AM
A more general way to do it
4186
December 05, 2006 08:43AM
3935
December 07, 2006 06:26AM