Status for 27.12.2017

This project can be implemented in the similar way as MDEV-9197 and some of the procedures from MDEV-9197 can be reused.

The procedure for the push down conditions in the materialized IN-subqueries should work this way:

Let's look on the example:

select * from t1 

where (a>4) and (b<200) and (x>1)

  (a,b) in (

     select c,max(d) 

     from t2 

     where (c<10) 

     group by c)

;

1. Condition that can be pushed in the IN-subquery should be extracted. So it should be condition that is defined only with the fields from the left part of the IN subquery (fields (a,b))

extracted_cond:

(a>4) and (b<200)

2. Than this condition should be transformed into the condition that is defined only with the fields from the projection list from the right part of the IN-subquery.
(as a=c and b=max(d))

subq_extracted_cond:

(c>4) and (max(d)<200)

3. Than condition which depends only on the fields from the group by of the IN subquery should be extracted from subq_extracted_cond (there procedures from MDEV-9197 can be reused)

subq_extracted_cond_for_group_fields:

 (c>4)

4. Now subq_extracted_cond_for_group_fields can be pushed into the WHERE part of the IN subquery. Also subq_extracted_cond_for_group_fields should be deleted from the subq_extracted_cond.

select * from t1 

where (a>4) and (b<200) and (x>1)

  (a,b) in (

     select c,max(d) 

     from t2 

     where (с>4) and (c<10) 

     group by c)

;

5. On the last stage subq_extracted_cond can be pushed into the HAVING part of the IN subquery.

select * from t1 

where (a>4) and (b<200) and (x>1)

  (a,b) in (

     select c,max(d) 

     from t2 

     where (с>4) and (c<10)

     having (max(d)<200)

     group by c)

;

Now everything except the 2 step is done. Procedures for the transformations in the 2 step are also written but they should be corrected.
Also some tests are written.

There was a problem with the 2 step because in the MDEV-9197 all transformations were made after defining what condition can be pushed in the WHERE part of the materialized derived table/view (the 4 step there). There it should be made before (on the 2 step) and also all procedures should be rewritten in the way that they can work with the subquries.

Status for 09.01.2018

It was found that the implementation scheme in the comment above is wrong. The procedure should work this way:

select * from t1 

where (a>4) and (b<200) and (x>1)

  (a,b) in (

     select c,max(d) 

     from t2 

     where (c<10) 

     group by c)

;

1 step remains the same. So we get:

extracted_cond:

(a>4) and (b<200)

2. The condition which depends only on the fields from the group by of the IN subquery should be extracted from extracted_cond

extracted_cond_for_group_fields:

(a>4)

3. Than extracted_cond_for_group_fields should be trasformed into the condition that is defined only with the fields from the projection list from the right part of the IN-subquery (there a=c)

subq_extracted_cond_for_group_fields:

 (c>4)

4. Now subq_extracted_cond_for_group_fields can be pushed into the WHERE part of the IN subquery. Also extracted_cond_for_group_fields should be deleted from the extracted_cond.

extracted_cond:

(b<200)

5. Than extracted_cond should be trasformed into the condition that is defined only with the fields from the projection list from the right part of the IN-subquery (there b=max(d))

subq_extracted_cond:

(max(d)<200)

6. Now subq_extracted_cond can be pushed into the HAVING part of the IN subquery.

select * from t1 

where (a>4) and (b<200) and (x>1)

  (a,b) in (

     select c,max(d) 

     from t2 

     where (с>4) and (c<10)

     having (max(d)<200)

     group by c)

;

Now the working prototype is made. Btw it is needed to rewrite setup_jtbm_semi_joins procedure in the way that it will work in this case (to get equalities between left and right parts of IN subquery after pushdown is done). This remarks are done but needs to be tested.

Pushed in 10.4

Documentation is being worked on here: https://mariadb.com/kb/en/library/condition-pushdown-into-in-subqueries/