Tarantool development patches archive
 help / color / mirror / Atom feed
* [tarantool-patches] [PATCH] sql: prohibit negative values under LIMIT clause
@ 2019-02-04  9:51 Stanislav Zudin
  2019-02-04 14:20 ` [tarantool-patches] " n.pettik
  2019-02-08 17:37 ` Konstantin Osipov
  0 siblings, 2 replies; 4+ messages in thread
From: Stanislav Zudin @ 2019-02-04  9:51 UTC (permalink / raw)
  To: tarantool-patches, korablev; +Cc: Stanislav Zudin

If LIMIT or OFFSET expressions can be casted to the
negative integer value VDBE returns an error

Closes #3467
---
Branch: https://github.com/tarantool/tarantool/tree/stanztt/gh-3467-prohibit-negative-limits
Issue: https://github.com/tarantool/tarantool/issues/3467

 src/box/sql/select.c                 |  40 +++++++
 test/sql-tap/e_select1.test.lua      |  19 ++-
 test/sql-tap/limit.test.lua          | 167 ++++++++++++++++++++-------
 test/sql-tap/orderby8.test.lua       |   2 +-
 test/sql-tap/select4.test.lua        |  40 +++++--
 test/sql-tap/select6.test.lua        |  10 +-
 test/sql-tap/select8.test.lua        |   2 +-
 test/sql-tap/subquery2.test.lua      |   2 +-
 test/sql-tap/with1.test.lua          |   6 +-
 test/sql/gh-3467-bind-limit.result   | 130 +++++++++++++++++++++
 test/sql/gh-3467-bind-limit.test.lua |  53 +++++++++
 11 files changed, 395 insertions(+), 76 deletions(-)
 create mode 100644 test/sql/gh-3467-bind-limit.result
 create mode 100644 test/sql/gh-3467-bind-limit.test.lua

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 02ee225f1..2aeae96b3 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -2075,6 +2075,9 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 	int iLimit = 0;
 	int iOffset;
 	int n;
+	const char *negativeLimitError =
+			"Only positive numbers allowed "
+			"in the LIMIT clause";
 	if (p->iLimit)
 		return;
 
@@ -2098,6 +2101,13 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 		v = sqlite3GetVdbe(pParse);
 		assert(v != 0);
 		if (sqlite3ExprIsInteger(p->pLimit, &n)) {
+		    if (n < 0) {
+                sqlite3VdbeAddOp4(v, OP_Halt,
+                                  SQLITE_MISMATCH,
+                                  0, 0, negativeLimitError,
+                                  P4_STATIC);
+            }
+
 			sqlite3VdbeAddOp2(v, OP_Integer, n, iLimit);
 			VdbeComment((v, "LIMIT counter"));
 			if (n == 0) {
@@ -2108,8 +2118,21 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 				p->selFlags |= SF_FixedLimit;
 			}
 		} else {
+			int lPosLimitValue = sqlite3VdbeMakeLabel(v);
 			sqlite3ExprCode(pParse, p->pLimit, iLimit);
 			sqlite3VdbeAddOp1(v, OP_MustBeInt, iLimit);
+			/* If LIMIT clause >= 0 continue execution */
+            int r1 = sqlite3GetTempReg(pParse);
+            sqlite3VdbeAddOp2(v, OP_Integer, 0, r1);
+            sqlite3VdbeAddOp3(v, OP_Ge, r1, lPosLimitValue, iLimit);
+			/* Otherwise return an error and stop */
+			sqlite3VdbeAddOp4(v, OP_Halt,
+							  SQLITE_MISMATCH,
+							  0, 0, negativeLimitError,
+							  P4_STATIC);
+
+			sqlite3VdbeResolveLabel(v, lPosLimitValue);
+            sqlite3ReleaseTempReg(pParse, r1);
 			VdbeCoverage(v);
 			VdbeComment((v, "LIMIT counter"));
 			sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, iBreak);
@@ -2149,10 +2172,27 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 			}
 		}
 		if (p->pOffset) {
+			int lPosOffsetValue = sqlite3VdbeMakeLabel(v);
+			const char *negativeOffsetError =
+					"Only positive numbers allowed "
+					"in the OFFSET clause";
 			p->iOffset = iOffset = ++pParse->nMem;
 			pParse->nMem++;	/* Allocate an extra register for limit+offset */
 			sqlite3ExprCode(pParse, p->pOffset, iOffset);
 			sqlite3VdbeAddOp1(v, OP_MustBeInt, iOffset);
+			/* If OFFSET clause >= 0 continue execution */
+            int r1 = sqlite3GetTempReg(pParse);
+            sqlite3VdbeAddOp2(v, OP_Integer, 0, r1);
+
+            sqlite3VdbeAddOp3(v, OP_Ge, r1, lPosOffsetValue, iOffset);
+			/* Otherwise return an error and stop */
+			sqlite3VdbeAddOp4(v, OP_Halt,
+							  SQLITE_MISMATCH,
+							  0, 0, negativeOffsetError,
+							  P4_STATIC);
+
+			sqlite3VdbeResolveLabel(v, lPosOffsetValue);
+            sqlite3ReleaseTempReg(pParse, r1);
 			VdbeCoverage(v);
 			VdbeComment((v, "OFFSET counter"));
 			sqlite3VdbeAddOp3(v, OP_OffsetLimit, iLimit,
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 464233e56..b419ca640 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(523)
+test:plan(520)
 
 --!./tcltestrunner.lua
 -- 2010 July 16
@@ -2186,9 +2186,9 @@ end
 test:do_select_tests(
     "e_select-9.4",
     {
-        {"1", "SELECT b FROM f1 ORDER BY a LIMIT -1 ", {"a",  "b",  "c",  "d",  "e",  "f",  "g",  "h",  "i",  "j",  "k",  "l",  "m",  "n",  "o",  "p",  "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}},
-        {"2", "SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 ", {"a",  "b",  "c",  "d",  "e",  "f",  "g",  "h",  "i",  "j",  "k",  "l",  "m",  "n",  "o",  "p",  "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}},
-        {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 ", {"a",  "b",  "c",  "d",  "e",  "f",  "g",  "h",  "i",  "j",  "k",  "l",  "m",  "n",  "o",  "p",  "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}},
+        {"1", "SELECT b FROM f1 ORDER BY a LIMIT 10000 ", {"a",  "b",  "c",  "d",  "e",  "f",  "g",  "h",  "i",  "j",  "k",  "l",  "m",  "n",  "o",  "p",  "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}},
+        {"2", "SELECT b FROM f1 ORDER BY a LIMIT 123+length('abc')-100 ", {"a",  "b",  "c",  "d",  "e",  "f",  "g",  "h",  "i",  "j",  "k",  "l",  "m",  "n",  "o",  "p",  "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}},
+        {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 10 ", {"a",  "b",  "c"}},
     })
 
 -- EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N
@@ -2270,9 +2270,9 @@ test:do_select_tests(
 test:do_select_tests(
     "e_select-9.10",
     {
-        {"1", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 ", {"a", "b", "c", "d", "e"}},
-        {"2", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 ", {"a", "b", "c", "d", "e"}},
-        {"3", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0  ", {"a", "b", "c", "d", "e"}},
+        {"1", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 ", {"a", "b", "c", "d", "e"}},
+--        {"2", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 ", {"a", "b", "c", "d", "e"}},
+--        {"3", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1  ", {"a", "b", "c", "d", "e"}},
     })
 
 -- EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the
@@ -2293,9 +2293,8 @@ test:do_select_tests(
         {"7", "SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 ", {"p", "q", "r"}},
         {"8", "SELECT b FROM f1 ORDER BY a LIMIT 20, 10 ", {"u", "v", "w", "x", "y", "z"}},
         {"9", "SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 ", {4, 3, 2, 1}},
-        {"10", "SELECT b FROM f1 ORDER BY a LIMIT -1, 5 ", {"a", "b", "c", "d", "e"}},
-        {"11", "SELECT b FROM f1 ORDER BY a LIMIT -500, 5 ", {"a", "b", "c", "d", "e"}},
-        {"12", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}},
+        {"10", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}},
+        {"11", "SELECT b FROM f1 ORDER BY a LIMIT 500-500, 5 ", {"a", "b", "c", "d", "e"}},
     })
 
 test:finish_test()
diff --git a/test/sql-tap/limit.test.lua b/test/sql-tap/limit.test.lua
index 062ba4e38..626695864 100755
--- a/test/sql-tap/limit.test.lua
+++ b/test/sql-tap/limit.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(103)
+test:plan(111)
 
 --!./tcltestrunner.lua
 -- 2001 November 6
@@ -78,56 +78,75 @@ test:do_execsql_test(
         -- </limit-1.2.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "limit-1.2.3",
     [[
         SELECT x FROM t1 ORDER BY x+1 LIMIT 5 OFFSET -2
     ]], {
-        -- <limit-1.2.3>
-        0, 1, 2, 3, 4
-        -- </limit-1.2.3>
+        -- <limit-1.2.13>
+        1 ,"Only positive numbers allowed in the OFFSET clause"
+        -- </limit-1.2.13>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "limit-1.2.4",
     [[
         SELECT x FROM t1 ORDER BY x+1 LIMIT 2, -5
     ]], {
         -- <limit-1.2.4>
-        2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
-        20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31
+        1, "Only positive numbers allowed in the LIMIT clause"
         -- </limit-1.2.4>
     })
 
 test:do_execsql_test(
     "limit-1.2.5",
     [[
-        SELECT x FROM t1 ORDER BY x+1 LIMIT -2, 5
+        SELECT x FROM t1 ORDER BY x+1 LIMIT 2, 1000
     ]], {
-        -- <limit-1.2.5>
-        0, 1, 2, 3, 4
-        -- </limit-1.2.5>
-    })
+    -- <limit-1.2.5>
+    2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
+    20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31
+    -- </limit-1.2.5>
+})
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "limit-1.2.6",
     [[
-        SELECT x FROM t1 ORDER BY x+1 LIMIT -2, -5
+        SELECT x FROM t1 ORDER BY x+1 LIMIT -2, 5
     ]], {
         -- <limit-1.2.6>
-        0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
-        20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31
+        1, "Only positive numbers allowed in the OFFSET clause"
         -- </limit-1.2.6>
     })
 
 test:do_execsql_test(
     "limit-1.2.7",
+    [[
+        SELECT x FROM t1 ORDER BY x+1 LIMIT 0, 5
+    ]], {
+    -- <limit-1.2.7>
+    0, 1, 2, 3, 4
+    -- </limit-1.2.7>
+})
+
+test:do_catchsql_test(
+    "limit-1.2.8",
+    [[
+        SELECT x FROM t1 ORDER BY x+1 LIMIT -2, -5
+    ]], {
+        -- <limit-1.2.8>
+        1, "Only positive numbers allowed in the LIMIT clause"
+        -- </limit-1.2.8>
+    })
+
+test:do_execsql_test(
+    "limit-1.2.9",
     [[
         SELECT x FROM t1 ORDER BY x LIMIT 2, 5
     ]], {
-        -- <limit-1.2.7>
+        -- <limit-1.2.9>
         2, 3, 4, 5, 6
-        -- </limit-1.2.7>
+        -- </limit-1.2.9>
     })
 
 test:do_execsql_test(
@@ -359,56 +378,96 @@ test:do_execsql_test(
         -- </limit-6.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "limit-6.2",
     [[
         SELECT * FROM t6 LIMIT -1 OFFSET -1;
     ]], {
         -- <limit-6.2>
-        1, 2, 3, 4
+        1, "Only positive numbers allowed in the LIMIT clause"
         -- </limit-6.2>
     })
 
-test:do_execsql_test(
-    "limit-6.3",
+test:do_catchsql_test(
+    "limit-6.3.1",
     [[
         SELECT * FROM t6 LIMIT 2 OFFSET -123;
     ]], {
         -- <limit-6.3>
-        1, 2
+        1, "Only positive numbers allowed in the OFFSET clause"
         -- </limit-6.3>
     })
 
 test:do_execsql_test(
-    "limit-6.4",
+    "limit-6.3.2",
+    [[
+        SELECT * FROM t6 LIMIT 2 OFFSET 0;
+    ]], {
+    -- <limit-6.3>
+    1, 2
+    -- </limit-6.3>
+})
+
+test:do_catchsql_test(
+    "limit-6.4.1",
     [[
         SELECT * FROM t6 LIMIT -432 OFFSET 2;
     ]], {
         -- <limit-6.4>
-        3, 4
+        1, "Only positive numbers allowed in the LIMIT clause"
         -- </limit-6.4>
     })
 
 test:do_execsql_test(
-    "limit-6.5",
+    "limit-6.4.2",
+    [[
+        SELECT * FROM t6 LIMIT 1000 OFFSET 2;
+    ]], {
+    -- <limit-6.4>
+    3, 4
+    -- </limit-6.4>
+})
+
+test:do_catchsql_test(
+    "limit-6.5.1",
     [[
         SELECT * FROM t6 LIMIT -1
     ]], {
         -- <limit-6.5>
-        1, 2, 3, 4
+        1, "Only positive numbers allowed in the LIMIT clause"
         -- </limit-6.5>
     })
 
 test:do_execsql_test(
-    "limit-6.6",
+    "limit-6.5.2",
+    [[
+        SELECT * FROM t6 LIMIT '12'
+    ]], {
+    -- <limit-6.5>
+    1, 2, 3, 4
+    -- </limit-6.5>
+})
+
+test:do_catchsql_test(
+    "limit-6.6.1",
     [[
         SELECT * FROM t6 LIMIT -1 OFFSET 1
     ]], {
         -- <limit-6.6>
-        2, 3, 4
+        1, "Only positive numbers allowed in the LIMIT clause"
         -- </limit-6.6>
     })
 
+test:do_execsql_test(
+    "limit-6.6.2",
+    [[
+        SELECT * FROM t6 LIMIT 111 OFFSET 1
+    ]], {
+    -- <limit-6.6>
+    2, 3, 4
+    -- </limit-6.6>
+})
+
 test:do_execsql_test(
     "limit-6.7",
     [[
@@ -660,13 +719,13 @@ test:do_test(
 test:do_test(
     "limit-10.3",
     function()
-        local limit = -1
+        local limit = 111
         return test:execsql("SELECT x FROM t1 WHERE x<10 LIMIT "..limit)
     end, {
-        -- <limit-10.3>
-        9, 8, 7, 6, 5, 4, 3, 2, 1, 0
-        -- </limit-10.3>
-    })
+    -- <limit-10.3.2>
+    9, 8, 7, 6, 5, 4, 3, 2, 1, 0
+    -- </limit-10.3.2>
+})
 
 test:do_test(
     "limit-10.4",
@@ -1259,24 +1318,44 @@ test:do_execsql_test(
         -- </limit-14.4>
     })
 
-test:do_execsql_test(
-    "limit-14.6",
+test:do_catchsql_test(
+    "limit-14.6.1",
     [[
         SELECT 123 LIMIT -1 OFFSET 0
     ]], {
-        -- <limit-14.6>
-        123
-        -- </limit-14.6>
+        -- <limit-14.6.1>
+        1, "Only positive numbers allowed in the LIMIT clause"
+        -- </limit-14.6.1>
     })
 
 test:do_execsql_test(
-    "limit-14.7",
+    "limit-14.6.2",
+    [[
+        SELECT 123 LIMIT 21 OFFSET 0
+    ]], {
+    -- <limit-14.6.2>
+    123
+    -- </limit-14.6.2>
+})
+
+test:do_catchsql_test(
+    "limit-14.7.1",
     [[
         SELECT 123 LIMIT -1 OFFSET 1
     ]], {
-        -- <limit-14.7>
-        
-        -- </limit-14.7>
+        -- <limit-14.7.1>
+        1, "Only positive numbers allowed in the LIMIT clause"
+        -- </limit-14.7.1>
     })
 
+test:do_execsql_test(
+    "limit-14.7.2",
+    [[
+        SELECT 123 LIMIT 111 OFFSET 1
+    ]], {
+    -- <limit-14.7.2>
+
+    -- </limit-14.7.2>
+})
+
 test:finish_test()
diff --git a/test/sql-tap/orderby8.test.lua b/test/sql-tap/orderby8.test.lua
index 63ec6da1c..55b944230 100755
--- a/test/sql-tap/orderby8.test.lua
+++ b/test/sql-tap/orderby8.test.lua
@@ -44,7 +44,7 @@ for i=1,200 do
     rs = rs..", x+"..i
     test:do_execsql_test(
         "1."..i,
-        "SELECT x FROM (SELECT "..rs.." FROM t1 ORDER BY x LIMIT -1)",
+        "SELECT x FROM (SELECT "..rs.." FROM t1 ORDER BY x LIMIT 100)",
         {
             1, 2, 3, 4, 5, 6, 7, 8, 9
     })
diff --git a/test/sql-tap/select4.test.lua b/test/sql-tap/select4.test.lua
index ebe8cd4ca..0a892488a 100755
--- a/test/sql-tap/select4.test.lua
+++ b/test/sql-tap/select4.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(103)
+test:plan(105)
 
 --!./tcltestrunner.lua
 -- 2001 September 15
@@ -984,25 +984,43 @@ test:do_execsql_test(
         -- </select4-10.3>
     })
 
-test:do_execsql_test(
-    "select4-10.4",
+test:do_catchsql_test(
+    "select4-10.4.1",
     [[
         SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
     ]], {
-        -- <select4-10.4>
-        0, 1, 2, 3, 4, 5
-        -- </select4-10.4>
+        -- <select4-10.4.1>
+    1,"Only positive numbers allowed in the LIMIT clause"
+        -- </select4-10.4.1>
     })
-
 test:do_execsql_test(
-    "select4-10.5",
+    "select4-10.4.2",
+    [[
+        SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 111
+    ]], {
+    -- <select4-10.4.2>
+    0, 1, 2, 3, 4, 5
+    -- </select4-10.4.2>
+})
+
+test:do_catchsql_test(
+    "select4-10.5.1",
     [[
         SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
     ]], {
-        -- <select4-10.5>
-        2, 3, 4, 5
-        -- </select4-10.5>
+        -- <select4-10.5.1>
+        1,"Only positive numbers allowed in the LIMIT clause"
+        -- </select4-10.5.1>
     })
+test:do_execsql_test(
+    "select4-10.5.2",
+    [[
+        SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 111 OFFSET 2
+    ]], {
+    -- <select4-10.5.2>
+    2, 3, 4, 5
+    -- </select4-10.5.2>
+})
 
 test:do_execsql_test(
     "select4-10.6",
diff --git a/test/sql-tap/select6.test.lua b/test/sql-tap/select6.test.lua
index 9a0fe6efb..7f174f04f 100755
--- a/test/sql-tap/select6.test.lua
+++ b/test/sql-tap/select6.test.lua
@@ -842,7 +842,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select6-9.7",
     [[
-        SELECT x FROM (SELECT x FROM t1 LIMIT -1) LIMIT 3;
+        SELECT x FROM (SELECT x FROM t1 LIMIT 101) LIMIT 3;
     ]], {
         -- <select6-9.7>
         1, 2, 3
@@ -852,7 +852,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select6-9.8",
     [[
-        SELECT x FROM (SELECT x FROM t1 LIMIT -1);
+        SELECT x FROM (SELECT x FROM t1 LIMIT 101);
     ]], {
         -- <select6-9.8>
         1, 2, 3, 4
@@ -862,7 +862,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select6-9.9",
     [[
-        SELECT x FROM (SELECT x FROM t1 LIMIT -1 OFFSET 1);
+        SELECT x FROM (SELECT x FROM t1 LIMIT 10-1 OFFSET 1);
     ]], {
         -- <select6-9.9>
         2, 3, 4
@@ -872,7 +872,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select6-9.10",
     [[
-        SELECT x, y FROM (SELECT x, (SELECT 10+x) y FROM t1 LIMIT -1 OFFSET 1);
+        SELECT x, y FROM (SELECT x, (SELECT 10+x) y FROM t1 LIMIT 9-1 OFFSET 1);
     ]], {
         -- <select6-9.10>
         2, 12, 3, 13, 4, 14
@@ -882,7 +882,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select6-9.11",
     [[
-        SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT -1 OFFSET 1);
+        SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT 7-1 OFFSET 1);
     ]], {
         -- <select6-9.11>
         2, 12, 3, 13, 4, 14
diff --git a/test/sql-tap/select8.test.lua b/test/sql-tap/select8.test.lua
index b67d0a194..7b2bec51d 100755
--- a/test/sql-tap/select8.test.lua
+++ b/test/sql-tap/select8.test.lua
@@ -69,7 +69,7 @@ test:do_execsql_test(
         SELECT DISTINCT artist,sum(timesplayed) AS total      
         FROM songs      
         GROUP BY LOWER(artist)      
-        LIMIT -1 OFFSET 2
+        LIMIT 1000 OFFSET 2
     ]], subrange(result, 5, #result))
 
 test:finish_test()
diff --git a/test/sql-tap/subquery2.test.lua b/test/sql-tap/subquery2.test.lua
index 240911f4d..58c5de2f6 100755
--- a/test/sql-tap/subquery2.test.lua
+++ b/test/sql-tap/subquery2.test.lua
@@ -132,7 +132,7 @@ test:do_execsql_test(
     2.2,
     [[
         SELECT * 
-        FROM (SELECT * FROM t4 ORDER BY a LIMIT -1 OFFSET 1) 
+        FROM (SELECT * FROM t4 ORDER BY a LIMIT 1000000 OFFSET 1)
         LIMIT (SELECT a FROM t5)
     ]], {
         -- <2.2>
diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 97585c13b..1556cce3c 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(67)
+test:plan(65)
 
 --!./tcltestrunner.lua
 -- 2014 January 11
@@ -663,8 +663,8 @@ limit_test(9.4, 20, -1)
 limit_test(9.5, 5, 5)
 limit_test(9.6, 0, -1)
 limit_test(9.7, 40, -1)
-limit_test(9.8, -1, -1)
-limit_test(9.9, -1, -1)
+-- limit_test(9.8, -1, -1)
+-- limit_test(9.9, -1, -1)
 -- #--------------------------------------------------------------------------
 -- # Test the ORDER BY clause on recursive tables.
 -- #
diff --git a/test/sql/gh-3467-bind-limit.result b/test/sql/gh-3467-bind-limit.result
new file mode 100644
index 000000000..4f7c72cdb
--- /dev/null
+++ b/test/sql/gh-3467-bind-limit.result
@@ -0,0 +1,130 @@
+--
+-- Created by IntelliJ IDEA.
+-- User: szudin
+-- Date: 04.02.19
+-- Time: 12:26
+-- To change this template use File | Settings | File Templates.
+--
+remote = require('net.box')
+---
+...
+test_run = require('test_run').new()
+---
+...
+engine = test_run:get_cfg('engine')
+---
+...
+box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+---
+...
+box.sql.execute('create table test (id int primary key, a float, b text)')
+---
+...
+--
+-- setup permissions
+--
+box.sql.execute('select * from test')
+---
+- []
+...
+box.schema.user.grant('guest','read,write,execute', 'universe')
+---
+...
+box.schema.user.grant('guest', 'create', 'space')
+---
+...
+--
+--
+cn = remote.connect(box.cfg.listen)
+---
+...
+cn:ping()
+---
+- true
+...
+-- Simple select.
+ret = cn:execute('select * from test')
+---
+...
+ret
+---
+- metadata:
+  - name: ID
+    type: INTEGER
+  - name: A
+    type: NUMERIC
+  - name: B
+    type: TEXT
+  rows: []
+...
+cn:execute('insert into test values (1, 1.1, \'preved\')')
+---
+- rowcount: 1
+...
+cn:execute('insert into test values (2, 2.2, \'medved\')')
+---
+- rowcount: 1
+...
+cn:execute('insert into test values (3, 3.3, \'bread\')')
+---
+- rowcount: 1
+...
+cn:execute('insert into test values (4, 4.4, \'honey\')')
+---
+- rowcount: 1
+...
+cn:execute('insert into test values (5, 5.5, \'beer\')')
+---
+- rowcount: 1
+...
+--
+-- Parmaeters bindig.
+--
+cn:execute('select * from test where id = ?', {1})
+---
+- metadata:
+  - name: ID
+    type: INTEGER
+  - name: A
+    type: NUMERIC
+  - name: B
+    type: TEXT
+  rows:
+  - [1, 1.1, 'preved']
+...
+cn:execute('select ?, ?, ?', {1, 2, 3})
+---
+- metadata:
+  - name: '?'
+    type: INTEGER
+  - name: '?'
+    type: INTEGER
+  - name: '?'
+    type: INTEGER
+  rows:
+  - [1, 2, 3]
+...
+cn:execute('select * from test limit ?', {2})
+---
+- metadata:
+  - name: ID
+    type: INTEGER
+  - name: A
+    type: NUMERIC
+  - name: B
+    type: TEXT
+  rows:
+  - [1, 1.1, 'preved']
+  - [2, 2.2, 'medved']
+...
+--
+-- cleanup
+--
+cn:close()
+---
+...
+-- Cleanup xlog
+box.snapshot()
+---
+- ok
+...
diff --git a/test/sql/gh-3467-bind-limit.test.lua b/test/sql/gh-3467-bind-limit.test.lua
new file mode 100644
index 000000000..6c4704970
--- /dev/null
+++ b/test/sql/gh-3467-bind-limit.test.lua
@@ -0,0 +1,53 @@
+--
+-- Created by IntelliJ IDEA.
+-- User: szudin
+-- Date: 04.02.19
+-- Time: 12:26
+-- To change this template use File | Settings | File Templates.
+--
+remote = require('net.box')
+test_run = require('test_run').new()
+engine = test_run:get_cfg('engine')
+box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+
+box.sql.execute('create table test (id int primary key, a float, b text)')
+
+--
+-- setup permissions
+--
+box.sql.execute('select * from test')
+box.schema.user.grant('guest','read,write,execute', 'universe')
+box.schema.user.grant('guest', 'create', 'space')
+
+--
+--
+cn = remote.connect(box.cfg.listen)
+cn:ping()
+
+
+-- Simple select.
+ret = cn:execute('select * from test')
+ret
+
+cn:execute('insert into test values (1, 1.1, \'preved\')')
+cn:execute('insert into test values (2, 2.2, \'medved\')')
+cn:execute('insert into test values (3, 3.3, \'bread\')')
+cn:execute('insert into test values (4, 4.4, \'honey\')')
+cn:execute('insert into test values (5, 5.5, \'beer\')')
+
+--
+-- Parmaeters bindig.
+--
+cn:execute('select * from test where id = ?', {1})
+cn:execute('select ?, ?, ?', {1, 2, 3})
+
+cn:execute('select * from test limit ?', {2})
+
+
+--
+-- cleanup
+--
+cn:close()
+
+-- Cleanup xlog
+box.snapshot()
-- 
2.17.1

^ permalink raw reply	[flat|nested] 4+ messages in thread

end of thread, other threads:[~2019-02-11 13:39 UTC | newest]

Thread overview: 4+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2019-02-04  9:51 [tarantool-patches] [PATCH] sql: prohibit negative values under LIMIT clause Stanislav Zudin
2019-02-04 14:20 ` [tarantool-patches] " n.pettik
2019-02-08 17:37 ` Konstantin Osipov
2019-02-11 13:39   ` Stanislav Zudin

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox