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];