From 44a6af65f56ccbcb9a7ca6ba1d021a2b23c07666 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Vicen=C8=9Biu=20Ciorbaru?= Date: Fri, 16 Apr 2021 20:02:10 +0300 Subject: MDEV-25430: ROW | ROWS should be a required keyword after OFFSET start Implemented according to standard OFFSET start { ROW | ROWS}, ROW and ROWS is mandatory after OFFSET. --- mysql-test/main/fetch_first.result | 134 ++++++++++++++++++++++--------------- mysql-test/main/fetch_first.test | 129 +++++++++++++++++++++-------------- sql/sql_lex.cc | 1 + sql/sql_yacc.yy | 13 ++-- 4 files changed, 164 insertions(+), 113 deletions(-) diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result index 88cd65123fa..1e63581e0a5 100644 --- a/mysql-test/main/fetch_first.result +++ b/mysql-test/main/fetch_first.result @@ -15,13 +15,13 @@ insert into t_keyword values (1), (1), (2), (3), (2); # select * from t1 order by a -offset 2; +offset 2 rows; a 2 2 3 select * from t1 -offset 2; +offset 2 rows; a 2 2 @@ -63,27 +63,51 @@ fetch next rows only; a 1 # -# Include offset before fetch clause. +# Row / rows are mandatory after offset # select * from t1 order by a offset 2 fetch first row only; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fetch first row only' at line 4 +select * from t1 +order by a +offset 2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 +select * from t1 +order by a +offset 2 row +fetch first row only; a 2 select * from t1 order by a -offset 2 +offset 2 rows +fetch first row only; +a +2 +# +# Include offset before fetch clause. +# +select * from t1 +order by a +offset 2 rows +fetch first row only; +a +2 +select * from t1 +order by a +offset 2 rows fetch first rows only; a 2 select * from t1 -offset 2 +offset 2 rows fetch next row only; a 2 select * from t1 -offset 2 +offset 2 rows fetch next rows only; a 2 @@ -132,28 +156,28 @@ a # select * from t1 order by a -offset 2 +offset 2 rows fetch first row with ties; a 2 2 select * from t1 order by a -offset 2 +offset 2 rows fetch first rows with ties; a 2 2 select * from t1 order by a -offset 2 +offset 2 rows fetch next row with ties; a 2 2 select * from t1 order by a -offset 2 +offset 2 rows fetch next rows with ties; a 2 @@ -190,7 +214,7 @@ a # select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 row only; a 2 @@ -198,21 +222,21 @@ a 3 select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 rows only; a 2 2 3 select * from t1 -offset 2 +offset 2 rows fetch next 3 row only; a 2 3 2 select * from t1 -offset 2 +offset 2 rows fetch next 3 rows only; a 2 @@ -270,12 +294,12 @@ a # Include offset before fetch clause. # select * from t1 -offset 2 +offset 2 rows fetch first row with ties; ERROR HY000: FETCH ... WITH TIES requires ORDER BY clause to be present select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 row with ties; a 2 @@ -283,7 +307,7 @@ a 3 select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 rows with ties; a 2 @@ -291,7 +315,7 @@ a 3 select * from t1 order by a -offset 2 +offset 2 rows fetch next 3 row with ties; a 2 @@ -299,7 +323,7 @@ a 3 select * from t1 order by a -offset 2 +offset 2 rows fetch next 3 rows with ties; a 2 @@ -389,7 +413,7 @@ id first_name last_name score 6 John Elton 8.1 select * from t1 order by first_name, last_name -offset 1 +offset 1 rows fetch first 3 rows with ties; id first_name last_name score 7 Bob Trasc 9 @@ -397,7 +421,7 @@ id first_name last_name score 6 John Elton 8.1 select * from t1 order by first_name, last_name -offset 1 +offset 1 rows fetch first 3 rows only; id first_name last_name score 7 Bob Trasc 9 @@ -405,7 +429,7 @@ id first_name last_name score 6 John Elton 8.1 select * from t1 order by first_name, last_name -offset 1 +offset 1 rows fetch first 3 rows with ties; id first_name last_name score 7 Bob Trasc 9 @@ -413,7 +437,7 @@ id first_name last_name score 6 John Elton 8.1 select * from t1 order by first_name, last_name -offset 2 +offset 2 rows fetch first 3 rows only; id first_name last_name score 2 John Doe 6 @@ -421,7 +445,7 @@ id first_name last_name score 5 John Smith 7 select * from t1 order by first_name, last_name -offset 2 +offset 2 rows fetch first 3 rows with ties; id first_name last_name score 2 John Doe 6 @@ -431,7 +455,7 @@ id first_name last_name score 5 John Smith 7 select * from t1 order by first_name, last_name -offset 3 +offset 3 rows fetch first 3 rows only; id first_name last_name score 6 John Elton 8.1 @@ -439,7 +463,7 @@ id first_name last_name score 4 John Smith 6 select * from t1 order by first_name, last_name -offset 3 +offset 3 rows fetch first 3 rows with ties; id first_name last_name score 6 John Elton 8.1 @@ -448,7 +472,7 @@ id first_name last_name score 5 John Smith 7 select * from t1 order by first_name, last_name -offset 4 +offset 4 rows fetch first 3 rows only; id first_name last_name score 3 John Smith 6 @@ -456,7 +480,7 @@ id first_name last_name score 5 John Smith 7 select * from t1 order by first_name, last_name -offset 4 +offset 4 rows fetch first 3 rows with ties; id first_name last_name score 3 John Smith 6 @@ -467,7 +491,7 @@ id first_name last_name score # select * from t1 order by first_name, last_name -offset 5 +offset 5 rows fetch first 3 rows with ties; id first_name last_name score 4 John Smith 6 @@ -475,7 +499,7 @@ id first_name last_name score 8 Silvia Ganush 10 select * from t1 order by first_name, last_name -offset 5 +offset 5 rows fetch first 3 rows only; id first_name last_name score 4 John Smith 6 @@ -539,14 +563,14 @@ id location fk 7 L4 7 select * from t2 order by t2.location desc -offset 1 +offset 1 rows fetch first 2 rows with ties; id location fk 6 L4 6 7 L4 7 select * from t2 order by t2.location desc -offset 2 +offset 2 rows fetch first 2 rows with ties; id location fk 3 L3 3 @@ -569,7 +593,7 @@ id first_name last_name score location select * from ( select * from t2 order by t2.location desc -offset 2 +offset 2 rows fetch first 2 rows with ties ) temp; id location fk @@ -578,7 +602,7 @@ id location fk 4 L3 4 select * from t2 order by t2.location desc -offset 0 +offset 0 rows fetch first 2 rows with ties; id location fk 5 L4 5 @@ -587,19 +611,19 @@ id location fk create view v1 as ( select * from t2 order by t2.location desc -offset 0 +offset 0 rows fetch first 2 rows with ties ); create view v2 as ( select * from t2 order by t2.location desc -offset 1 +offset 1 rows fetch first 2 rows with ties ); create view v3 as ( select * from t2 order by t2.location desc -offset 2 +offset 2 rows fetch first row with ties ); select * from v1; @@ -616,20 +640,20 @@ id location fk 7 L4 7 show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 0 fetch first 2 rows with ties) latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 0 rows fetch first 2 rows with ties) latin1 latin1_swedish_ci show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 1 fetch first 2 rows with ties) latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 1 rows fetch first 2 rows with ties) latin1 latin1_swedish_ci show create view v3; View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 2 fetch first 1 rows with ties) latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS (select `t2`.`id` AS `id`,`t2`.`location` AS `location`,`t2`.`fk` AS `fk` from `t2` order by `t2`.`location` desc offset 2 rows fetch first 1 rows with ties) latin1 latin1_swedish_ci # # Test joins with views and order by referenced from the view. # select * from t1 inner join v1 on t1.id = v1.fk order by v1.location desc, t1.first_name -offset 1 +offset 1 rows fetch first 1 rows with ties; id first_name last_name score id location fk 5 John Smith 7 5 L4 5 @@ -654,7 +678,7 @@ select first_name, last_name, sum(score) from t1 group by first_name, last_name order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort @@ -662,7 +686,7 @@ select first_name, last_name, sum(score) from t1 group by first_name, last_name order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name last_name sum(score) Bob Trasc 9 @@ -691,7 +715,7 @@ insert into t1 values explain select first_name, last_name from t1 order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL t1_name 206 NULL 3 Using index @@ -704,7 +728,7 @@ id select_type table type possible_keys key key_len ref rows Extra select first_name, last_name from t1 order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name last_name Bob Trasc @@ -727,7 +751,7 @@ John Doe select first_name, last_name from t1 order by first_name desc -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name last_name John Smith @@ -738,7 +762,7 @@ John Doe select first_name, last_name from t1 order by first_name desc -offset 4 +offset 4 rows fetch first 2 rows with ties; first_name last_name John Elton @@ -746,7 +770,7 @@ John Doe select first_name, last_name from t1 order by first_name desc -offset 4 +offset 4 rows fetch first 3 rows with ties; first_name last_name John Elton @@ -846,7 +870,7 @@ fetch first 3 rows with ties ) select * from temp_table order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name last_name Bob Trasc @@ -856,7 +880,7 @@ John Smith select first_name, row_number() over () rn from t1 order by rn -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name rn Bob 2 @@ -864,7 +888,7 @@ John 3 select first_name, row_number() over () rn from t1 order by rn desc -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name rn John 7 @@ -881,7 +905,7 @@ John 6 2 select first_name, score, rank() over (ORDER BY score) from t1 order by rank() over (ORDER BY score) -offset 1 +offset 1 rows fetch first 2 rows with ties; first_name score rank() over (ORDER BY score) John 6 2 @@ -963,7 +987,7 @@ id first_name last_name score id location time value fk select * from t1 left join t2 on t1.id = t2.fk order by t2.location -offset 2 +offset 2 rows fetch first 1 row with ties; id first_name last_name score id location time value fk 1 Alice Fowler 5 1 home 2020-01-01 10:00:00 10 1 @@ -1008,7 +1032,7 @@ id first_name last_name score except (select * from t1 order by first_name desc fetch first 3 rows with ties) order by first_name -offset 1 +offset 1 rows fetch first 1 row with ties; id first_name last_name score select sum(score) @@ -1083,7 +1107,7 @@ a b set @a=-1; execute s using @a; ERROR HY000: Incorrect arguments to EXECUTE -prepare s2 from "select a, b from t1 order by a offset ? fetch first ? rows with ties"; +prepare s2 from "select a, b from t1 order by a offset ? rows fetch first ? rows with ties"; set @offset=1; set @limit_count= 2; execute s2 using @offset, @limit_count; diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test index 34898ffe0e2..6995c1cb5b1 100644 --- a/mysql-test/main/fetch_first.test +++ b/mysql-test/main/fetch_first.test @@ -18,11 +18,12 @@ insert into t_keyword values (1), (1), (2), (3), (2); --echo # select * from t1 order by a -offset 2; +offset 2 rows; + --sorted_result select * from t1 -offset 2; +offset 2 rows; --echo # @@ -55,24 +56,48 @@ select * from t1 fetch next rows only; --echo # ---echo # Include offset before fetch clause. +--echo # Row / rows are mandatory after offset --echo # +--error 1064 select * from t1 order by a offset 2 fetch first row only; +--error 1064 +select * from t1 +order by a +offset 2; + select * from t1 order by a -offset 2 +offset 2 row +fetch first row only; + +select * from t1 +order by a +offset 2 rows +fetch first row only; + +--echo # +--echo # Include offset before fetch clause. +--echo # +select * from t1 +order by a +offset 2 rows +fetch first row only; + +select * from t1 +order by a +offset 2 rows fetch first rows only; select * from t1 -offset 2 +offset 2 rows fetch next row only; select * from t1 -offset 2 +offset 2 rows fetch next rows only; @@ -119,22 +144,22 @@ fetch next rows with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch first row with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch first rows with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch next row with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch next rows with ties; @@ -158,20 +183,20 @@ fetch next 3 rows only; --echo # select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 row only; select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 rows only; select * from t1 -offset 2 +offset 2 rows fetch next 3 row only; select * from t1 -offset 2 +offset 2 rows fetch next 3 rows only; @@ -218,27 +243,27 @@ fetch next 3 rows with ties; --error ER_WITH_TIES_NEEDS_ORDER select * from t1 -offset 2 +offset 2 rows fetch first row with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 row with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch first 3 rows with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch next 3 row with ties; select * from t1 order by a -offset 2 +offset 2 rows fetch next 3 rows with ties; @@ -309,47 +334,47 @@ fetch first 4 rows with ties; select * from t1 order by first_name, last_name -offset 1 +offset 1 rows fetch first 3 rows with ties; select * from t1 order by first_name, last_name -offset 1 +offset 1 rows fetch first 3 rows only; select * from t1 order by first_name, last_name -offset 1 +offset 1 rows fetch first 3 rows with ties; select * from t1 order by first_name, last_name -offset 2 +offset 2 rows fetch first 3 rows only; select * from t1 order by first_name, last_name -offset 2 +offset 2 rows fetch first 3 rows with ties; select * from t1 order by first_name, last_name -offset 3 +offset 3 rows fetch first 3 rows only; select * from t1 order by first_name, last_name -offset 3 +offset 3 rows fetch first 3 rows with ties; select * from t1 order by first_name, last_name -offset 4 +offset 4 rows fetch first 3 rows only; select * from t1 order by first_name, last_name -offset 4 +offset 4 rows fetch first 3 rows with ties; --echo # @@ -357,12 +382,12 @@ fetch first 3 rows with ties; --echo # select * from t1 order by first_name, last_name -offset 5 +offset 5 rows fetch first 3 rows with ties; select * from t1 order by first_name, last_name -offset 5 +offset 5 rows fetch first 3 rows only; --echo # @@ -407,13 +432,13 @@ fetch first 2 rows with ties; --sorted_result select * from t2 order by t2.location desc -offset 1 +offset 1 rows fetch first 2 rows with ties; --sorted_result select * from t2 order by t2.location desc -offset 2 +offset 2 rows fetch first 2 rows with ties; --echo # @@ -430,34 +455,34 @@ fetch first 3 rows with ties; select * from ( select * from t2 order by t2.location desc - offset 2 + offset 2 rows fetch first 2 rows with ties ) temp; select * from t2 order by t2.location desc -offset 0 +offset 0 rows fetch first 2 rows with ties; create view v1 as ( select * from t2 order by t2.location desc - offset 0 + offset 0 rows fetch first 2 rows with ties ); create view v2 as ( select * from t2 order by t2.location desc - offset 1 + offset 1 rows fetch first 2 rows with ties ); create view v3 as ( select * from t2 order by t2.location desc - offset 2 + offset 2 rows fetch first row with ties ); @@ -479,7 +504,7 @@ show create view v3; select * from t1 inner join v1 on t1.id = v1.fk order by v1.location desc, t1.first_name -offset 1 +offset 1 rows fetch first 1 rows with ties; select first_name, last_name, sum(score) @@ -497,7 +522,7 @@ select first_name, last_name, sum(score) from t1 group by first_name, last_name order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; --sorted_result @@ -505,7 +530,7 @@ select first_name, last_name, sum(score) from t1 group by first_name, last_name order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; @@ -536,7 +561,7 @@ insert into t1 values explain select first_name, last_name from t1 order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; explain select first_name, last_name @@ -548,7 +573,7 @@ fetch first 2 rows with ties; select first_name, last_name from t1 order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; select first_name, last_name @@ -560,19 +585,19 @@ fetch first 2 rows with ties; select first_name, last_name from t1 order by first_name desc -offset 1 +offset 1 rows fetch first 2 rows with ties; select first_name, last_name from t1 order by first_name desc -offset 4 +offset 4 rows fetch first 2 rows with ties; select first_name, last_name from t1 order by first_name desc -offset 4 +offset 4 rows fetch first 3 rows with ties; explain select first_name, last_name @@ -654,20 +679,20 @@ with temp_table as ( ) select * from temp_table order by first_name -offset 1 +offset 1 rows fetch first 2 rows with ties; select first_name, row_number() over () rn from t1 order by rn -offset 1 +offset 1 rows fetch first 2 rows with ties; select first_name, row_number() over () rn from t1 order by rn desc -offset 1 +offset 1 rows fetch first 2 rows with ties; select first_name, score, rank() over (ORDER BY score) @@ -678,7 +703,7 @@ fetch first 3 rows with ties; select first_name, score, rank() over (ORDER BY score) from t1 order by rank() over (ORDER BY score) -offset 1 +offset 1 rows fetch first 2 rows with ties; select first_name, score, rank() over (ORDER BY score) @@ -732,7 +757,7 @@ fetch first 3 row with ties; select * from t1 left join t2 on t1.id = t2.fk order by t2.location -offset 2 +offset 2 rows fetch first 1 row with ties; @@ -761,7 +786,7 @@ fetch first 1 row with ties; except (select * from t1 order by first_name desc fetch first 3 rows with ties) order by first_name -offset 1 +offset 1 rows fetch first 1 row with ties; --sorted_result @@ -827,7 +852,7 @@ set @a=-1; --error ER_WRONG_ARGUMENTS execute s using @a; -prepare s2 from "select a, b from t1 order by a offset ? fetch first ? rows with ties"; +prepare s2 from "select a, b from t1 order by a offset ? rows fetch first ? rows with ties"; set @offset=1; set @limit_count= 2; execute s2 using @offset, @limit_count; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 2d652a194d6..4b76d9684df 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3789,6 +3789,7 @@ void st_select_lex::print_limit(THD *thd, { str->append(STRING_WITH_LEN(" offset ")); limit_params.offset_limit->print(str, query_type); + str->append(STRING_WITH_LEN(" rows ")); } str->append(STRING_WITH_LEN(" fetch first ")); limit_params.select_limit->print(str, query_type); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e62a0484d6a..a5ab6848c92 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -12507,7 +12507,7 @@ fetch_first_clause: $$.explicit_limit= true; $$.with_ties= $4; } - | OFFSET_SYM limit_option + | OFFSET_SYM limit_option row_or_rows FETCH_SYM first_or_next row_or_rows only_or_with_ties { Item *one= new (thd->mem_root) Item_int(thd, (int32) 1); @@ -12516,7 +12516,7 @@ fetch_first_clause: $$.select_limit= one; $$.offset_limit= $2; $$.explicit_limit= true; - $$.with_ties= $6; + $$.with_ties= $7; } | FETCH_SYM first_or_next limit_option row_or_rows only_or_with_ties { @@ -12525,15 +12525,15 @@ fetch_first_clause: $$.explicit_limit= true; $$.with_ties= $5; } - | OFFSET_SYM limit_option + | OFFSET_SYM limit_option row_or_rows FETCH_SYM first_or_next limit_option row_or_rows only_or_with_ties { - $$.select_limit= $5; + $$.select_limit= $6; $$.offset_limit= $2; $$.explicit_limit= true; - $$.with_ties= $7; + $$.with_ties= $8; } - | OFFSET_SYM limit_option + | OFFSET_SYM limit_option row_or_rows { $$.select_limit= 0; $$.offset_limit= $2; @@ -12546,6 +12546,7 @@ first_or_next: FIRST_SYM | NEXT_SYM ; + row_or_rows: ROW_SYM | ROWS_SYM -- cgit v1.2.1