To optimize the select-subquery tarantool uses
subquery flattening function which is only used
with respect to some restrictions. One of them
was implemented improperly.
Closes: #3353
---
Issue: https://github.com/tarantool/tarantool/issues/3353
Branch:
https://github.com/tarantool/tarantool/tree/N_Tatunov/gh-3353-assertion-on-nested-select
src/box/sql/select.c | 6 +++---
test/sql-tap/subquery.test.lua | 48 +++++++++++++++++++++++++++++++++++++++++-
2 files changed, 50 insertions(+), 4 deletions(-)
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 54f78a9..c035691 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -3893,14 +3893,14 @@ flattenSubquery(Parse * pParse, /* Parsing
context */
* queries.
*/
if (pSub->pPrior) {
- if (pSub->pOrderBy) {
- return 0; /* Restriction 20 */
- }
if (isAgg || (p->selFlags & SF_Distinct) != 0
|| pSrc->nSrc != 1) {
return 0;
}
for (pSub1 = pSub; pSub1; pSub1 = pSub1->pPrior) {
+ if (pSub1->pOrderBy) {
+ return 0; /* Restriction 20 */
+ }
testcase((pSub1->
selFlags & (SF_Distinct | SF_Aggregate)) ==
SF_Distinct);
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 06631c1..d1e17b3 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(69)
+test:plan(73)
--!./tcltestrunner.lua
-- 2005 January 19
@@ -926,6 +926,52 @@ test:do_execsql_test(
-- </subquery-8.1>
})
+test:do_catchsql_test(
+ "subquery-9.0",
+ [[
+ DROP TABLE IF EXISTS table1;
+ CREATE TABLE table1 (id VARCHAR(100) PRIMARY KEY);
+ INSERT INTO table1 VALUES ('abc'), ('abd');
+ ]], {
+ -- <subquery-9.0>
+ 0
+ -- <subquery-9.0>
+ })
+
+test:do_catchsql_test(
+ "subquery-9.1",
+ [[
+ SELECT * FROM (SELECT * FROM table1 UNION ALL
+ SELECT * FROM table1 ORDER BY 1 UNION ALL
+ SELECT * FROM table1);
+ ]], {
+ -- <subquery-9.1>
+ 1, 'ORDER BY clause should come after UNION ALL not before'
+ -- <subquery-9.1>
+ })
+
+test:do_execsql_test(
+ "subquery-9.2",
+ [[
+ SELECT 'abc' FROM (SELECT * FROM table1 UNION ALL
+ SELECT * FROM table1 UNION ALL
+ SELECT * FROM table1 ORDER BY 1);
+ ]], {
+ -- <subquery-9.2>
+ 'abc','abc','abc','abc','abc','abc'
+ -- <subquery-9.2>
+ })
+test:do_catchsql_test(
+ "subquery-9.3",
+ [[
+ SELECT * FROM (SELECT * FROM table1 ORDER BY 1 UNION All
+ SELECT * FROM table1 UNION All
+ SELECT * FROM table1);
+ ]], {
+ -- <subquery-9.3>
+ 1, 'ORDER BY clause should come after UNION ALL not before'
+ -- <subquery-9.3>
+ })
test:finish_test()
--
2.7.4
