Understanding SELECT_LEX & SELECT_LEX_UNIT

 

Understanding SELECT_LEX & SELECT_LEX_UNIT

Louis Hust

 

0  Preface

SELECT_LEX(st_select_lex)&SELECT_LEX_UNIT(st_select_lex_unit) are two important structs in Optimizer. They are used from yacc grammer to senmantics and to optimizer at last. So they are also the basic of understanding the Optimizer.This article will explain what they stand for, but we will not deep into the concrete elements of each struct.

 

0  Introduction

SELECT_LEX: representing SELECT itself, that is, if there is a 'SELECT' keywork in SQL, there is a corresponding SELECT_LEX struct.

 

SELECT_LEX_UNIT: for grouping several selects in a bunch(in union), also can represent a single select.

 

All of the two structs are stored in LEX struct.There is a SELECT_LEX_UNIT and SELECT_LEX struct by default. If the SQL is a single SELECT or a union of several SELECTs, the default struct in LEX is enough, but if there is a subquery, there must be new SELECT_LEX AND SELECT_LEX_UNIT.

 

0  Examples

 

SELECT * FROM t1;

  unit
   |
   V
 select t1



 SELECT * FROM t1 UNION SELECT * FROM t2;
                unit
                 |
                 V         next
               select t1 --------> select t2



SELECT * FROM t1 where c1 in (select * from t11 union select * from t12);
                
                unit
                 |
                 V
              select t1
                 |
                 V
               unit1.1
                 |
                 V         next
              select t11 ---------> select t12


SELECT * FROM t1 WHERE c1 in (SELECT * FROM t11) and c2 in (SELECT * FROM t12);

               unit1
                |
                V
              select t1
                |
                V         next
                unit1.1  ------> unit1.2
                  |                |
                  V                V
               select t11       select t12

At last, i give a sample in sql_lex.h.        

   select *
     from table1
     where table1.field IN (select * from table1_1_1 union
                            select * from table1_1_2)
     union
   select *
     from table2
     where table2.field=(select (select f1 from table2_1_1_1_1
                                   where table2_1_1_1_1.f2=table2_1_1.f3)
                           from table2_1_1
                           where table2_1_1.f1=table2.f2)
     union
   select * from table3;

                  unit
                   |
                   V
                select table1------------> select table2 ------------>select table3
                   |                             |
                   V                             |__________
                 unit1                                     |
                   |                                       |
                   V                                       |
            select table 1_1_1--->select table 1_1_2       V
                                                          unit2
                                                           |
                                                           V
                                                  select table2_1_1
                                                           |
                                                           V
                                                         unit2.1
                                                           |
                                                           V
                                                   select table2_1_1_1_1

 

There is a funny thing i found that SELECT_LEX_UNIT alternates with SELECT_LEX.

 

0  Code Inspection

What we care about is when the new SELECT_LEX and SELECT_LEX_UNIT will be created. The answer is the same function: mysql_new_select.

 

This function will create a new SELECT_LEX, but SELECT_LEX_UNIT will be created only when the flag 'move_down' is true.

 

if move_down is true, it means that the SQL come into a SUBQUERY; otherwise, the SQL comes into a UNION.

 

References

[1]

Structure Of Complex Select

---

File translated from T_EX by T_TH, version 4.03.
On 16 Jan 2013, 13:06.