<HTML><BODY>From 102a9130f6c884411b89a33c668928e5f85422c1 Mon Sep 17 00:00:00 2001<br>From: Mergen Imeev <imeevma@tarantool.org><br>Date: Tue, 15 May 2018 21:25:28 +0300<br>Subject: [PATCH] sql: COLLATE after LIMIT throws an error<br><br>Originally, SQLite3 execute queries with COLLATE after LIMIT like<br>"SELECT * FROM test LIMIT N COLLATE not_exist"<br>and queries without COLLATE like<br>"SELECT * FROM test LIMIT N"<br>the same way.<br><br>From this patch on queries with COLLATE after LIMIT<br>throws a syntax error.<br><br>Closes: #3010<br>---<br> src/box/sql/parse.y | 20 +++++++++----<br> test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua | 29 ++++++++++++++++++-<br> test/sql/errinj.result | 35 +++++++++++++++++++++++<br> test/sql/errinj.test.lua | 17 +++++++++++<br> 4 files changed, 95 insertions(+), 6 deletions(-)<br><br>diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y<br>index 872647d..030d58a 100644<br>--- a/src/box/sql/parse.y<br>+++ b/src/box/sql/parse.y<br>@@ -711,11 +711,21 @@ having_opt(A) ::= HAVING expr(X). {A = X.pExpr;}<br> // sqlite3ExprDelete(pParse->db, $$.pOffset);<br> //}<br> limit_opt(A) ::= . {A.pLimit = 0; A.pOffset = 0;}<br>-limit_opt(A) ::= LIMIT expr(X). {A.pLimit = X.pExpr; A.pOffset = 0;}<br>-limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). <br>- {A.pLimit = X.pExpr; A.pOffset = Y.pExpr;}<br>-limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). <br>- {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;}<br>+limit_opt(A) ::= LIMIT expr(X). {<br>+ if(X.pExpr->flags & EP_Collate)<br>+ sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");<br>+ A.pLimit = X.pExpr; A.pOffset = 0;<br>+}<br>+limit_opt(A) ::= LIMIT expr(X) OFFSET expr(Y). {<br>+ if((X.pExpr->flags & EP_Collate) || (Y.pExpr->flags & EP_Collate))<br>+ sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");<br>+ A.pLimit = X.pExpr; A.pOffset = Y.pExpr;<br>+}<br>+limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). {<br>+ if((X.pExpr->flags & EP_Collate) || (Y.pExpr->flags & EP_Collate))<br>+ sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");<br>+ A.pOffset = X.pExpr; A.pLimit = Y.pExpr;<br>+}<br> <br> /////////////////////////// The DELETE statement /////////////////////////////<br> //<br>diff --git a/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua b/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua<br>index 74d69aa..6cdc2ab 100755<br>--- a/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua<br>+++ b/test/sql-tap/gh-2884-forbid-rowid-syntax.test.lua<br>@@ -1,6 +1,6 @@<br> #!/usr/bin/env tarantool<br> test = require("sqltester")<br>-test:plan(1)<br>+test:plan(4)<br> <br> local ok = pcall(test.execsql, test, [[<br> DROP TABLE IF EXISTS t1;<br>@@ -9,4 +9,31 @@ local ok = pcall(test.execsql, test, [[<br> <br> test:ok(not ok, 'rowid syntax must be forbidden')<br> <br>+-- gh-3010: COLLATE after LIMIT should throw an error (First part)<br>+<br>+test:do_catchsql_test(<br>+ "collate-after-limit-1.0",<br>+ "SELECT 1 LIMIT 1 COLLATE BINARY;", {<br>+ -- <collate-after-limit-1.0><br>+ 1, "near \"COLLATE\": syntax error"<br>+ -- <collate-after-limit-1.0><br>+});<br>+<br>+test:do_catchsql_test(<br>+ "collate-after-limit-1.1",<br>+ "SELECT 1 LIMIT 1 OFFSET 2 COLLATE BINARY;", {<br>+ -- <collate-after-limit-1.1><br>+ 1, "near \"COLLATE\": syntax error"<br>+ -- <collate-after-limit-1.1><br>+});<br>+<br>+test:do_catchsql_test(<br>+ "collate-after-limit-1.2",<br>+ "SELECT 1 LIMIT 1 COLLATE BINARY, 2;", {<br>+ -- <collate-after-limit-1.2><br>+ 1, "near \"COLLATE\": syntax error"<br>+ -- <collate-after-limit-1.2><br>+});<br>+<br>+<br> test:finish_test()<br>diff --git a/test/sql/errinj.result b/test/sql/errinj.result<br>index 00a0d6b..e667091 100644<br>--- a/test/sql/errinj.result<br>+++ b/test/sql/errinj.result<br>@@ -115,3 +115,38 @@ box.sql.execute('DROP TABLE test')<br> box.schema.user.revoke('guest', 'read,write,execute', 'universe')<br> ---<br> ...<br>+cn:close()<br>+---<br>+...<br>+-- gh-3010: COLLATE after LIMIT should throw an error (Second part)<br>+box.sql.execute('CREATE TABLE test (id integer primary key)')<br>+---<br>+...<br>+box.schema.user.grant('guest','read,write,execute', 'universe')<br>+---<br>+...<br>+cn = remote.connect(box.cfg.listen)<br>+---<br>+...<br>+cn:ping()<br>+---<br>+- true<br>+...<br>+cn:execute('insert into test values (1)')<br>+---<br>+- rowcount: 1<br>+...<br>+-- SQL error: syntax error<br>+cn:execute('select * from test limit ? collate not_exist', {1})<br>+---<br>+- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'<br>+...<br>+cn:close()<br>+---<br>+...<br>+box.sql.execute('DROP TABLE test')<br>+---<br>+...<br>+box.schema.user.revoke('guest', 'read,write,execute', 'universe')<br>+---<br>+...<br>diff --git a/test/sql/errinj.test.lua b/test/sql/errinj.test.lua<br>index 63d3063..1268436 100644<br>--- a/test/sql/errinj.test.lua<br>+++ b/test/sql/errinj.test.lua<br>@@ -44,3 +44,20 @@ errinj.set("ERRINJ_IPROTO_TX_DELAY", false)<br> <br> box.sql.execute('DROP TABLE test')<br> box.schema.user.revoke('guest', 'read,write,execute', 'universe')<br>+<br>+<br>+cn:close()<br>+<br>+-- gh-3010: COLLATE after LIMIT should throw an error (Second part)<br>+box.sql.execute('CREATE TABLE test (id integer primary key)')<br>+box.schema.user.grant('guest','read,write,execute', 'universe')<br>+cn = remote.connect(box.cfg.listen)<br>+cn:ping()<br>+<br>+cn:execute('insert into test values (1)')<br>+-- SQL error: syntax error<br>+cn:execute('select * from test limit ? collate not_exist', {1})<br>+<br>+cn:close()<br>+box.sql.execute('DROP TABLE test')<br>+box.schema.user.revoke('guest', 'read,write,execute', 'universe')<br>-- <br>2.7.4<br><br><br></BODY></HTML>