This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 EXISTS to IN transformation
Title
Task ID245
Queue
Version
Status
Priority
Copies to
Created bySanja07 Dec 2011Done
Supervisor   
Lead Architect    
Architecture Review  
Implementor  
Code Review  
QA  
Documentation  
 High-Level 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 NOT( 2*outer_table.field IS NOT NULL AND NOT 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.
 Task Dependencies
Others waiting for Task 245Task 245 is waiting forGraph
 
 High-Level Specification
 Low-Level Design
 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Total0 
 Hrs WorkedProgressCurrentOriginal
Total000
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Sanja - Tue, 03 Jan 2012, 11:30
    
High Level Description modified.
--- /tmp/wklog.245.old.13522	2012-01-03 11:30:19.000000000 +0000
+++ /tmp/wklog.245.new.13522	2012-01-03 11:30:19.000000000 +0000
@@ -13,9 +13,10 @@
 ... 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 
+... WHERE NOT( 2*outer_table.field IS NOT NULL AND NOT 2*outer_table.field IN 
+(SELECT 
 inner_table.field FROM inner_table WHERE inner_table.field IS NOT NULL AND 
-maybe_something_else)..
+maybe_something_else)...
 
 To allow optimizations made for IN/ALL/ANY subqueries.
 


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB