[tarantool-patches] Re: [PATCH] sql: forbid different sorting orders in ORDER BY
i.koptelov
ivan.koptelov at tarantool.org
Fri Mar 15 19:52:09 MSK 2019
> On 15 Mar 2019, at 19:23, n.pettik <korablev at tarantool.org> wrote:
>
>>
>> + -- Tests are commented because of the reason described in
>> + -- NOTE at the beginning of the file.
>> + -- <begin>
>> + --local data = {
>> + -- {limit=0, offset=4, orderby="+b,+a"},
>> + -- {limit=0, offset=5, orderby="+b,+a"},
>> + -- {limit=0, offset=6, orderby="+b,+a"},
>> + -- {limit=0, offset=9, orderby="+b,+a"},
>> + -- {limit=0, offset=0, orderby="+b,+a"},
>> + -- {limit=0, offset=1, orderby="+b,+a"},
>> + -- {limit=7, offset=4, orderby="+b,+a"},
>> + -- {limit=7, offset=9, orderby="+b,+a”},
>
> These first tests come with the same sorting orders,
> can we avoid commenting them?
Yes.
diff --git a/test/sql-tap/orderby6.test.lua b/test/sql-tap/orderby6.test.lua
index 7fe0032dd..e51059ffc 100755
--- a/test/sql-tap/orderby6.test.lua
+++ b/test/sql-tap/orderby6.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(18)
+test:plan(34)
--!./tcltestrunner.lua
-- 2014-03-21
@@ -158,48 +158,49 @@ testprefix = "orderby6"
-- Many test cases where the LIMIT+OFFSET window is in various
-- alignments with block-sort boundaries.
--
- -- Tests are commented because of the reason described in
- -- NOTE at the beginning of the file.
- -- <begin>
- --local data = {
- -- {limit=0, offset=4, orderby="+b,+a"},
- -- {limit=0, offset=5, orderby="+b,+a"},
- -- {limit=0, offset=6, orderby="+b,+a"},
- -- {limit=0, offset=9, orderby="+b,+a"},
- -- {limit=0, offset=0, orderby="+b,+a"},
- -- {limit=0, offset=1, orderby="+b,+a"},
- -- {limit=7, offset=4, orderby="+b,+a"},
- -- {limit=7, offset=9, orderby="+b,+a"},
- -- {limit=0, offset=4, orderby="+b DESC,+a"},
- -- {limit=0, offset=5, orderby="+b DESC,+a"},
- -- {limit=0, offset=6, orderby="+b DESC,+a"},
- -- {limit=0, offset=9, orderby="+b DESC,+a"},
- -- {limit=0, offset=0, orderby="+b DESC,+a"},
- -- {limit=0, offset=1, orderby="+b DESC,+a"},
- -- {limit=7, offset=4, orderby="+b DESC,+a"},
- -- {limit=7, offset=9, orderby="+b DESC,+a"},
- -- {limit=0, offset=4, orderby="+b,+a DESC"},
- -- {limit=0, offset=5, orderby="+b,+a DESC"},
- -- {limit=0, offset=6, orderby="+b,+a DESC"},
- -- {limit=0, offset=9, orderby="+b,+a DESC"},
- -- {limit=0, offset=0, orderby="+b,+a DESC"},
- -- {limit=0, offset=1, orderby="+b,+a DESC"},
- -- {limit=7, offset=4, orderby="+b,+a DESC"},
- -- {limit=7, offset=9, orderby="+b,+a DESC"},
- -- {limit=0, offset=4, orderby="+b DESC,+a DESC"},
- -- {limit=0, offset=5, orderby="+b DESC,+a DESC"},
- -- {limit=0, offset=6, orderby="+b DESC,+a DESC"},
- -- {limit=0, offset=9, orderby="+b DESC,+a DESC"},
- -- {limit=0, offset=0, orderby="+b DESC,+a DESC"},
- -- {limit=0, offset=1, orderby="+b DESC,+a DESC"},
- -- {limit=7, offset=4, orderby="+b DESC,+a DESC"},
- -- {limit=7, offset=9, orderby="+b DESC,+a DESC"}}
- --for i, v in ipairs(data) do
- -- local sql1 = "SELECT a FROM t1 ORDER BY "..v.orderby.." LIMIT "..v.limit.." OFFSET "..v.offset..";"
- -- local sql2 = "SELECT a FROM t1 ORDER BY "..string.gsub(v.orderby, "+", "").." LIMIT "..v.limit.." OFFSET "..v.offset..";"
- -- test:do_execsql_test("1.21."..i, sql1, test:execsql(sql2))
- --end
- -- <end>
+ local data = {
+ {limit=0, offset=4, orderby="+b,+a"},
+ {limit=0, offset=5, orderby="+b,+a"},
+ {limit=0, offset=6, orderby="+b,+a"},
+ {limit=0, offset=9, orderby="+b,+a"},
+ {limit=0, offset=0, orderby="+b,+a"},
+ {limit=0, offset=1, orderby="+b,+a"},
+ {limit=7, offset=4, orderby="+b,+a"},
+ {limit=7, offset=9, orderby="+b,+a"},
+ -- Tests with different sorting orders are commented
+ -- because of the reason described in NOTE at the
+ -- beginning of the file.
+ -- <begin>
+ --{limit=0, offset=4, orderby="+b DESC,+a"},
+ --{limit=0, offset=5, orderby="+b DESC,+a"},
+ --{limit=0, offset=6, orderby="+b DESC,+a"},
+ --{limit=0, offset=9, orderby="+b DESC,+a"},
+ --{limit=0, offset=0, orderby="+b DESC,+a"},
+ --{limit=0, offset=1, orderby="+b DESC,+a"},
+ --{limit=7, offset=4, orderby="+b DESC,+a"},
+ --{limit=7, offset=9, orderby="+b DESC,+a"},
+ --{limit=0, offset=4, orderby="+b,+a DESC"},
+ --{limit=0, offset=5, orderby="+b,+a DESC"},
+ --{limit=0, offset=6, orderby="+b,+a DESC"},
+ --{limit=0, offset=9, orderby="+b,+a DESC"},
+ --{limit=0, offset=0, orderby="+b,+a DESC"},
+ --{limit=0, offset=1, orderby="+b,+a DESC"},
+ --{limit=7, offset=4, orderby="+b,+a DESC"},
+ --{limit=7, offset=9, orderby="+b,+a DESC”},
+ -- <end>
+ {limit=0, offset=4, orderby="+b DESC,+a DESC"},
+ {limit=0, offset=5, orderby="+b DESC,+a DESC"},
+ {limit=0, offset=6, orderby="+b DESC,+a DESC"},
+ {limit=0, offset=9, orderby="+b DESC,+a DESC"},
+ {limit=0, offset=0, orderby="+b DESC,+a DESC"},
+ {limit=0, offset=1, orderby="+b DESC,+a DESC"},
+ {limit=7, offset=4, orderby="+b DESC,+a DESC"},
+ {limit=7, offset=9, orderby="+b DESC,+a DESC"}}
+ for i, v in ipairs(data) do
+ local sql1 = "SELECT a FROM t1 ORDER BY "..v.orderby.." LIMIT "..v.limit.." OFFSET "..v.offset..";"
+ local sql2 = "SELECT a FROM t1 ORDER BY "..string.gsub(v.orderby, "+", "").." LIMIT "..v.limit.." OFFSET "..v.offset..";"
+ test:do_execsql_test("1.21."..i, sql1, test:execsql(sql2))
+ end
>
>> + -- {limit=0, offset=4, orderby="+b DESC,+a"},
>> + -- {limit=0, offset=5, orderby="+b DESC,+a"},
>> + -- {limit=0, offset=6, orderby="+b DESC,+a"},
>> + -- {limit=0, offset=9, orderby="+b DESC,+a"},
>> + -- {limit=0, offset=0, orderby="+b DESC,+a"},
>> + -- {limit=0, offset=1, orderby="+b DESC,+a"},
>> + -- {limit=7, offset=4, orderby="+b DESC,+a"},
>> + -- {limit=7, offset=9, orderby="+b DESC,+a"},
>>
>> +void
>> +sql_expr_check_sort_orders(struct Parse *parse,
>> + const struct ExprList *expr_list)
>> +{
>> + if(expr_list == NULL)
>> + return;
>> + enum sort_order reference_order = expr_list->a[0].sort_order;
>> + for (int i = 1; i < expr_list->nExpr; i++) {
>> + assert(expr_list->a[i].sort_order != SORT_ORDER_UNDEF);
>> + if (expr_list->a[i].sort_order != reference_order) {
>> + diag_set(ClientError, ER_UNSUPPORTED, "ORDER BY",
>> + "different sorting orders”);
>
> Different sorting orders and LIMIT clause at the same time OR
> “ORDER BY with LIMIT”, “different sorting orders”.
Thank you for noting. Fixed:
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index bd494ef2e..511e74c65 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -1819,7 +1819,8 @@ sql_expr_check_sort_orders(struct Parse *parse,
for (int i = 1; i < expr_list->nExpr; i++) {
assert(expr_list->a[i].sort_order != SORT_ORDER_UNDEF);
if (expr_list->a[i].sort_order != reference_order) {
- diag_set(ClientError, ER_UNSUPPORTED, "ORDER BY",
+ diag_set(ClientError, ER_UNSUPPORTED,
+ "ORDER BY with LIMIT",
"different sorting orders");
sql_parser_error(parse);
return;
diff --git a/test/sql-tap/orderby8.test.lua b/test/sql-tap/orderby8.test.lua
index 95acee1e1..52f125755 100755
--- a/test/sql-tap/orderby8.test.lua
+++ b/test/sql-tap/orderby8.test.lua
@@ -63,7 +63,7 @@ test:do_catchsql_test(
INSERT INTO t2 VALUES (1, 2, 1), (2, -3, 5), (3, 2, -3), (4, 2, 12);
SELECT * FROM t2 ORDER BY a ASC, b DESC LIMIT 5;
]],
- {1, "ORDER BY does not support different sorting orders"}
+ {1, "ORDER BY with LIMIT does not support different sorting orders"}
)
test:do_catchsql_test(
@@ -71,7 +71,7 @@ test:do_catchsql_test(
[[
SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
]],
- {1, "ORDER BY does not support different sorting orders"}
+ {1, "ORDER BY with LIMIT does not support different sorting orders"}
)
More information about the Tarantool-patches
mailing list