maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: EXISTS to IN transformation
CREATION DATE..: Wed, 07 Dec 2011, 12:51
SUPERVISOR.....: 
IMPLEMENTOR....: 
COPIES TO......: 
CATEGORY.......: Client-BackLog
TASK ID........: 245 (http://askmonty.org/worklog/?tid=245)
VERSION........: Benchmarks-3.0
STATUS.........: Un-Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:



DESCRIPTION:

Transform queries like:

... WHERE EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 
2*outer_table.field AND maybe_something_else)...

and

... WHERE NOT EXISTS (SELECT 1 FROM inner_table WHERE inner_table.field = 
2*outer_table.field AND maybe_something_else)...

into

... WHERE 2*outer_table.field IN (SELECT inner_table.field FROM inner_table 
WHERE 1 = 1 AND maybe_something_else)..

... WHERE 2*outer_table.field IS NULL OR 2*outer_table.field IN (SELECT 
inner_table.field FROM inner_table WHERE inner_table.field IS NOT NULL AND 
maybe_something_else)..

To allow optimizations made for IN/ALL/ANY subqueries.


Conversion is possible only if:
1)real NULL is not important (top element of WHERE/ON AND/OR list, i.e. NULL 
equal to FALSE)
2)the subquery has the only dependence which we bring out of it
3)the subquery is simple (has no aggregate function, GROUp BY, ORDER BY, LIMIT 
HAVING and so on)

For NOT EXISTS conversion the subquery should be marked that its left part can't 
be NULL.


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v4.0.0)