MySQL (select_paren) union_order_or_limit 行为

MySQL源码版本5.6.2

MySQL Syntax(sql_yacc.yy)

select:
      select_init

select_init:
    SELECT_SYM select_init2
    | '(' select_paren ')' union_opt
 ;
union_opt:
    /* Empty */ { $$= 0; }
      | union_list { $$= 1; }
      | union_order_or_limit { $$= 1; }
  ;
union_order_or_limit:
    order_or_limit:

order_or_limit:
    order_clause opt_limit_clause_init
    | limit_clause

limit_clause:
    LIMIT limit_options

处理逻辑

LIMIT

limit_options以limit_option ',' limit_option为例

      | limit_option ',' limit_option
      {
        SELECT_LEX *sel= Select;//Select表示current_select
        sel->select_limit= $3;
        sel->offset_limit= $1;
        sel->explicit_limit= 1;
      }

由该逻辑可以看出:

(select * from t1 [order by x | limit num]) limit off_x, count_y;//current_select不变

相当于

select * from t1 [order by x] limit off_x, count_y;

Order by

order_clause:
      ORDER_SYM BY
      {
        LEX *lex=Lex;
        SELECT_LEX *sel= lex->current_select;
        SELECT_LEX_UNIT *unit= sel-> master_unit();
        if (sel->linkage != GLOBAL_OPTIONS_TYPE &&
            sel->olap != UNSPECIFIED_OLAP_TYPE &&
            (sel->linkage != UNION_TYPE || sel->braces))
        {
          my_error(ER_WRONG_USAGE, MYF(0),
                   "CUBE/ROLLUP", "ORDER BY");
          MYSQL_YYABORT;
        }
        if (lex->sql_command != SQLCOM_ALTER_TABLE && !unit->fake_select_lex)
        {
          /*
            A query of the of the form (SELECT ...) ORDER BY order_list is
            executed in the same way as the query
            SELECT ... ORDER BY order_list
            unless the SELECT construct contains ORDER BY or LIMIT clauses.
            Otherwise we create a fake SELECT_LEX if it has not been created
            yet.
          */
          SELECT_LEX *first_sl= unit->first_select();
          if (!unit->is_union() &&
              (first_sl->order_list.elements ||
               first_sl->select_limit) &&
              unit->add_fake_select_lex(lex->thd))
            MYSQL_YYABORT;
        }
      }
      order_list
    ;

从上面可以看出当select_paren中没有order和limit的时候，current_select不会改变，有以下等价方式：

(select no_order_or_limit) order by xx [limit x];

等价于

select no_order_or_limit order by xx limit x;

当存在order或者limit的时候，MySQL会创建GLOBAL_OPTIONS_TYPE的fake_select_lex，这个fake_select_lex作为OPTIONS存在。

同时将current_select指向fake_select_lex。

  fake_select_lex->linkage= GLOBAL_OPTIONS_TYPE;
  if (!is_union())
  {
    /* 
      This works only for 
      (SELECT ... ORDER BY list [LIMIT n]) ORDER BY order_list [LIMIT m],
      (SELECT ... LIMIT n) ORDER BY order_list [LIMIT m]
      just before the parser starts processing order_list
    */ 
    global_parameters= fake_select_lex;
    fake_select_lex->no_table_names_allowed= 1;
    thd_arg->lex->current_select= fake_select_lex;
  }

因此当select_paren中存在order或者limit的时候，curren_select会指向fake_select，添加order by[limit]。我们可以认为其等价方式是：

(select xxx  order by xx limit x) order by yy[limit y];

等价于

select * from (select xxx order by xx limit x) ta order by yy limit y;

实验结果

mysql> create table t1(c1 int primary key);

mysql> insert into t1 values(1), (2),(3), (4),(5);

//limit option测试   
mysql> (select * from t1 limit 1) limit 5;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
 //等价:select * from t1 limit 5;

mysql> (select * from t1 order by c1 desc limit 1) limit 4;
+----+
| c1 |
+----+
|  5 |
|  4 |
|  3 |
|  2 |
+----+
//等价：select * from t1 order by c1 desc limit 4;

//order by[limit] option测试
mysql> (select * from t1) order by c1 desc limit 2;
+----+
| c1 |
+----+
|  5 |
|  4 |
+----+
//等价：select * from t1 order by c1 desc limit 2;

mysql> create table t(c1 int);

mysql> insert into t values(1), (1), (2), (2);

mysql> (select * from t group by(c1)) order by c1 limit 3;
+------+
| c1   |
+------+
|1 |
|2 |
+------+
//等价于select * from t group by (c1) order by c1 limit 3;

mysql> (select * from t1 limit 3) order by c1 desc limit 1;
+----+
| c1 |
+----+
|  3 |
+----+

//等价于:select * from (select * from t1 limit 3) ta order by c1 desc limit 1;

mysql> (select * from t1 order by c1 desc limit 2) order by c1;
+----+
| c1 |
+----+
|  4 |
|  5 |
+----+
//等价于：select * from (select * from t1 order by c1 desc limit 2) ta order by c1;

mysql> (select * from t1 order by c1 desc limit 2) order by c1 limit 1;
+----+
| c1 |
+----+
|  4 |
+----+   
//等价于:select * from (select * from t1 order by c1 desc limit 2) ta order by c1 limit 1;

mysql> insert into t values(3, 3);
mysql> (select * from t group by (c1) order by c1 desc limit 2) order by c1 limit 1;
+------+
| c1   |
+------+
|2 |
+------+
//等价于： select * from (select * from t group by (c1) order by c1 desc limit 2) ta order by c1 limit 1;

总结

MySQL语法 (select_paren) union_order_or_limt等价方式如下：

1、limit as option

(select xxx) limit yy;

等价于：

select xxx_no_limit limit yy;

2、order by [limit] as option

2.1、select_paren without order or limit

(select no_order_or_limit) order by yyy [limit y];

等价于：

select no_order_or_limit order by yyy [limit y];

2.2、select_paren with order or limit

(select xxx [order by xx | limit x]) order by yyy [limit y];

等价于：

select * from (select xxx [order by xx | limit x]) ta order by yyy [limit y];