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