[tarantool-patches] [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[tarantool-patches] [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[ImeevMA]

Originally, SQLite3 execute queries with COLLATE after LIMIT like
"SELECT * FROM test LIMIT N COLLATE not_exist"
and queries without COLLATE like
"SELECT * FROM test LIMIT N"
the same way.

From this patch on queries with COLLATE after LIMIT
or OFFSET throws a syntax error.

Closes #3010
---
 src/box/sql/select.c        |  5 +++++
 test/sql/collation.result   | 41 +++++++++++++++++++++++++++++++++++++++++
 test/sql/collation.test.lua | 19 +++++++++++++++++++
 3 files changed, 65 insertions(+)
 create mode 100644 test/sql/collation.result
 create mode 100644 test/sql/collation.test.lua

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 29075d5..b11e688 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1993,6 +1993,11 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 	sqlite3ExprCacheClear(pParse);
 	assert(p->pOffset == 0 || p->pLimit != 0);
 	if (p->pLimit) {
+		if((p->pLimit->flags & EP_Collate) != 0 ||
+					(p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
+			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+			return;
+		}
 		p->iLimit = iLimit = ++pParse->nMem;
 		v = sqlite3GetVdbe(pParse);
 		assert(v != 0);
diff --git a/test/sql/collation.result b/test/sql/collation.result
new file mode 100644
index 0000000..3a4f81f
--- /dev/null
+++ b/test/sql/collation.result
@@ -0,0 +1,41 @@
+remote = require('net.box')
+---
+...
+-- gh-3010: COLLATE after LIMIT should throw an error
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.schema.user.grant('guest','read,write,execute', 'universe')
+---
+...
+cn = remote.connect(box.cfg.listen)
+---
+...
+cn:execute('select 1 limit ? collate not_exist', {1})
+---
+- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'
+...
+cn:close()
+---
+...
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
+---
+...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
new file mode 100644
index 0000000..fe8c1ba
--- /dev/null
+++ b/test/sql/collation.test.lua
@@ -0,0 +1,19 @@
+remote = require('net.box')
+
+-- gh-3010: COLLATE after LIMIT should throw an error
+
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+
+
+box.schema.user.grant('guest','read,write,execute', 'universe')
+cn = remote.connect(box.cfg.listen)
+
+cn:execute('select 1 limit ? collate not_exist', {1})
+
+cn:close()
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
-- 
2.7.4

[tarantool-patches] Re: [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[Vladislav Shpilevoy]

Thanks for the patch! Looks like you did fix not all my comments from the
previous review.

On 21/05/2018 17:36, ImeevMA wrote:
> Originally, SQLite3 execute queries with COLLATE after LIMIT like
> "SELECT * FROM test LIMIT N COLLATE not_exist"
> and queries without COLLATE like
> "SELECT * FROM test LIMIT N"
> the same way.
> 
>  From this patch on queries with COLLATE after LIMIT
> or OFFSET throws a syntax error.
> 
> Closes #3010
> ---
>   src/box/sql/select.c        |  5 +++++
>   test/sql/collation.result   | 41 +++++++++++++++++++++++++++++++++++++++++
>   test/sql/collation.test.lua | 19 +++++++++++++++++++
>   3 files changed, 65 insertions(+)
>   create mode 100644 test/sql/collation.result
>   create mode 100644 test/sql/collation.test.lua
> 
> diff --git a/src/box/sql/select.c b/src/box/sql/select.c
> index 29075d5..b11e688 100644
> --- a/src/box/sql/select.c
> +++ b/src/box/sql/select.c
> @@ -1993,6 +1993,11 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
>   	sqlite3ExprCacheClear(pParse);
>   	assert(p->pOffset == 0 || p->pLimit != 0);
>   	if (p->pLimit) {
> +		if((p->pLimit->flags & EP_Collate) != 0 ||
> +					(p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
> +			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
> +			return;
> +		}

1. Still bad indentation.
> diff --git a/test/sql/collation.result b/test/sql/collation.result
> new file mode 100644
> index 0000000..3a4f81f
> --- /dev/null
> +++ b/test/sql/collation.result

2. Still no test on OFFSET ... COLLATE.

[tarantool-patches] Re: [tarantool-patches] Re: [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[Мерген Имеев]

[-- Attachment #1: Type: text/plain, Size: 5160 bytes --]




>Понедельник, 21 мая 2018, 17:45 +03:00 от Vladislav Shpilevoy <v.shpilevoy@tarantool.org>:
>
>Thanks for the patch! Looks like you did fix not all my comments from the
>previous review.
>
>On 21/05/2018 17:36, ImeevMA wrote:
>> Originally, SQLite3 execute queries with COLLATE after LIMIT like
>> "SELECT * FROM test LIMIT N COLLATE not_exist"
>> and queries without COLLATE like
>> "SELECT * FROM test LIMIT N"
>> the same way.
>> 
>>  From this patch on queries with COLLATE after LIMIT
>> or OFFSET throws a syntax error.
>> 
>> Closes #3010
>> ---
>>   src/box/sql/select.c        |  5 +++++
>>   test/sql/collation.result   | 41 +++++++++++++++++++++++++++++++++++++++++
>>   test/sql/collation.test.lua | 19 +++++++++++++++++++
>>   3 files changed, 65 insertions(+)
>>   create mode 100644 test/sql/collation.result
>>   create mode 100644 test/sql/collation.test.lua
>> 
>> diff --git a/src/box/sql/select.c b/src/box/sql/select.c
>> index 29075d5..b11e688 100644
>> --- a/src/box/sql/select.c
>> +++ b/src/box/sql/select.c
>> @@ -1993,6 +1993,11 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
>>   	sqlite3ExprCacheClear(pParse);
>>   	assert(p->pOffset == 0 || p->pLimit != 0);
>>   	if (p->pLimit) {
>> +		if((p->pLimit->flags & EP_Collate) != 0 ||
>> +					(p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
>> +			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
>> +			return;
>> +		}
>
>1. Still bad indentation.
>> diff --git a/test/sql/collation.result b/test/sql/collation.result
>> new file mode 100644
>> index 0000000..3a4f81f
>> --- /dev/null
>> +++ b/test/sql/collation.result 
Done.
>
>
>2. Still no test on OFFSET ... COLLATE.
>
In file test/sql/collation.test.lua lines 7-10.

commit 1ef0c67c409bf45d10adabbe8c9365c1437c626c
Author: ImeevMA <imeevma@tarantool.org>
Date:   Mon May 21 16:21:57 2018 +0300

    sql: COLLATE after LIMIT throws an error
    
    Originally, SQLite3 execute queries with COLLATE after LIMIT like
    "SELECT * FROM test LIMIT N COLLATE not_exist"
    and queries without COLLATE like
    "SELECT * FROM test LIMIT N"
    the same way.
    
    From this patch on queries with COLLATE after LIMIT
    or OFFSET throws a syntax error.
    
    Closes #3010

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 29075d5..7a77289 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1993,6 +1993,11 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
     sqlite3ExprCacheClear(pParse);
     assert(p->pOffset == 0 || p->pLimit != 0);
     if (p->pLimit) {
+        if((p->pLimit->flags & EP_Collate) != 0 ||
+           (p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
+            sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+            return;
+        }
         p->iLimit = iLimit = ++pParse->nMem;
         v = sqlite3GetVdbe(pParse);
         assert(v != 0);
diff --git a/test/sql/collation.result b/test/sql/collation.result
new file mode 100644
index 0000000..3a4f81f
--- /dev/null
+++ b/test/sql/collation.result
@@ -0,0 +1,41 @@
+remote = require('net.box')
+---
+...
+-- gh-3010: COLLATE after LIMIT should throw an error
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.schema.user.grant('guest','read,write,execute', 'universe')
+---
+...
+cn = remote.connect(box.cfg.listen)
+---
+...
+cn:execute('select 1 limit ? collate not_exist', {1})
+---
+- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'
+...
+cn:close()
+---
+...
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
+---
+...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
new file mode 100644
index 0000000..fe8c1ba
--- /dev/null
+++ b/test/sql/collation.test.lua
@@ -0,0 +1,19 @@
+remote = require('net.box')
+
+-- gh-3010: COLLATE after LIMIT should throw an error
+
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+
+
+box.schema.user.grant('guest','read,write,execute', 'universe')
+cn = remote.connect(box.cfg.listen)
+
+cn:execute('select 1 limit ? collate not_exist', {1})
+
+cn:close()
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')



[-- Attachment #2: Type: text/html, Size: 6736 bytes --]

[tarantool-patches] Re: [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[Vladislav Shpilevoy]

Thanks a lot!

But please, apply this diff:

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 7a7728992..0b610dca7 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1994,8 +1994,10 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
  	assert(p->pOffset == 0 || p->pLimit != 0);
  	if (p->pLimit) {
  		if((p->pLimit->flags & EP_Collate) != 0 ||
-		   (p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
-			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+		   (p->pOffset != NULL &&
+		    (p->pOffset->flags & EP_Collate) != 0)) {
+			sqlite3ErrorMsg(pParse, "near \"COLLATE\": "\
+					"syntax error");
  			return;
  		}
  		p->iLimit = iLimit = ++pParse->nMem;

In your patch sqlite3ErrorMsg was out of 80 symbols, and pointer p->pOffset must be checked
on != NULL explicitly. (I see, that other SQLite3 code does not this, but we will fix
this gradually).

After this the patch LGTM.

[tarantool-patches] Re: [tarantool-patches] Re: [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[Мерген Имеев]

[-- Attachment #1: Type: text/plain, Size: 4591 bytes --]




>Понедельник, 21 мая 2018, 18:27 +03:00 от Vladislav Shpilevoy <v.shpilevoy@tarantool.org>:
>
>Thanks a lot!
>
>But please, apply this diff:
>
>diff --git a/src/box/sql/select.c b/src/box/sql/select.c
>index 7a7728992..0b610dca7 100644
>--- a/src/box/sql/select.c
>+++ b/src/box/sql/select.c
>@@ -1994,8 +1994,10 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
>  	assert(p->pOffset == 0 || p->pLimit != 0);
>  	if (p->pLimit) {
>  		if((p->pLimit->flags & EP_Collate) != 0 ||
>-		   (p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
>-			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
>+		   (p->pOffset != NULL &&
>+		    (p->pOffset->flags & EP_Collate) != 0)) {
>+			sqlite3ErrorMsg(pParse, "near \"COLLATE\": "\
>+					"syntax error");
>  			return;
>  		}
>  		p->iLimit = iLimit = ++pParse->nMem;
>
>In your patch sqlite3ErrorMsg was out of 80 symbols, and pointer p->pOffset must be checked
>on != NULL explicitly. (I see, that other SQLite3 code does not this, but we will fix
>this gradually). 
Done.
>
>
>After this the patch LGTM.
>

commit 8ae83069671e7384c70523f4eda201d4815dba26
Author: ImeevMA <imeevma@tarantool.org>
Date:   Mon May 21 16:21:57 2018 +0300

    sql: COLLATE after LIMIT throws an error
    
    Originally, SQLite3 execute queries with COLLATE after LIMIT like
    "SELECT * FROM test LIMIT N COLLATE not_exist"
    and queries without COLLATE like
    "SELECT * FROM test LIMIT N"
    the same way.
    
    From this patch on queries with COLLATE after LIMIT
    or OFFSET throws a syntax error.
    
    Closes #3010

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 29075d5..fc1da27 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1993,6 +1993,13 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
     sqlite3ExprCacheClear(pParse);
     assert(p->pOffset == 0 || p->pLimit != 0);
     if (p->pLimit) {
+        if((p->pLimit->flags & EP_Collate) != 0 ||
+           (p->pOffset != NULL &&
+           (p->pOffset->flags & EP_Collate) != 0)) {
+            sqlite3ErrorMsg(pParse, "near \"COLLATE\": "\
+                        "syntax error");
+            return;
+        }
         p->iLimit = iLimit = ++pParse->nMem;
         v = sqlite3GetVdbe(pParse);
         assert(v != 0);
diff --git a/test/sql/collation.result b/test/sql/collation.result
new file mode 100644
index 0000000..3a4f81f
--- /dev/null
+++ b/test/sql/collation.result
@@ -0,0 +1,41 @@
+remote = require('net.box')
+---
+...
+-- gh-3010: COLLATE after LIMIT should throw an error
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.schema.user.grant('guest','read,write,execute', 'universe')
+---
+...
+cn = remote.connect(box.cfg.listen)
+---
+...
+cn:execute('select 1 limit ? collate not_exist', {1})
+---
+- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'
+...
+cn:close()
+---
+...
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
+---
+...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
new file mode 100644
index 0000000..fe8c1ba
--- /dev/null
+++ b/test/sql/collation.test.lua
@@ -0,0 +1,19 @@
+remote = require('net.box')
+
+-- gh-3010: COLLATE after LIMIT should throw an error
+
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+
+
+box.schema.user.grant('guest','read,write,execute', 'universe')
+cn = remote.connect(box.cfg.listen)
+
+cn:execute('select 1 limit ? collate not_exist', {1})
+
+cn:close()
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')



[-- Attachment #2: Type: text/html, Size: 6194 bytes --]

[tarantool-patches] Re: [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[Vladislav Shpilevoy]

LGTM. Kirill, can you please take a look?

On 21/05/2018 19:50, Мерген Имеев wrote:
> 
> 
> 
>     Понедельник, 21 мая 2018, 18:27 +03:00 от Vladislav Shpilevoy <v.shpilevoy@tarantool.org>:
> 
>     Thanks a lot!
> 
>     But please, apply this diff:
> 
>     diff --git a/src/box/sql/select.c b/src/box/sql/select.c
>     index 7a7728992..0b610dca7 100644
>     --- a/src/box/sql/select.c
>     +++ b/src/box/sql/select.c
>     @@ -1994,8 +1994,10 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
>         assert(p->pOffset == 0 || p->pLimit != 0);
>         if (p->pLimit) {
>         if((p->pLimit->flags & EP_Collate) != 0 ||
>     - (p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
>     - sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
>     + (p->pOffset != NULL &&
>     + (p->pOffset->flags & EP_Collate) != 0)) {
>     + sqlite3ErrorMsg(pParse, "near \"COLLATE\": "\
>     + "syntax error");
>         return;
>         }
>         p->iLimit = iLimit = ++pParse->nMem;
> 
>     In your patch sqlite3ErrorMsg was out of 80 symbols, and pointer p->pOffset must be checked
>     on != NULL explicitly. (I see, that other SQLite3 code does not this, but we will fix
>     this gradually).
> 
> Done.
> 
> 
> 
>     After this the patch LGTM.
> 
> 
> commit 8ae83069671e7384c70523f4eda201d4815dba26
> Author: ImeevMA <imeevma@tarantool.org>
> Date:   Mon May 21 16:21:57 2018 +0300
> 
>      sql: COLLATE after LIMIT throws an error
> 
>      Originally, SQLite3 execute queries with COLLATE after LIMIT like
>      "SELECT * FROM test LIMIT N COLLATE not_exist"
>      and queries without COLLATE like
>      "SELECT * FROM test LIMIT N"
>      the same way.
> 
>      From this patch on queries with COLLATE after LIMIT
>      or OFFSET throws a syntax error.
> 
>      Closes #3010
> 
> diff --git a/src/box/sql/select.c b/src/box/sql/select.c
> index 29075d5..fc1da27 100644
> --- a/src/box/sql/select.c
> +++ b/src/box/sql/select.c
> @@ -1993,6 +1993,13 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
>       sqlite3ExprCacheClear(pParse);
>       assert(p->pOffset == 0 || p->pLimit != 0);
>       if (p->pLimit) {
> +        if((p->pLimit->flags & EP_Collate) != 0 ||
> +           (p->pOffset != NULL &&
> +           (p->pOffset->flags & EP_Collate) != 0)) {
> +            sqlite3ErrorMsg(pParse, "near \"COLLATE\": "\
> +                        "syntax error");
> +            return;
> +        }
>           p->iLimit = iLimit = ++pParse->nMem;
>           v = sqlite3GetVdbe(pParse);
>           assert(v != 0);
> diff --git a/test/sql/collation.result b/test/sql/collation.result
> new file mode 100644
> index 0000000..3a4f81f
> --- /dev/null
> +++ b/test/sql/collation.result
> @@ -0,0 +1,41 @@
> +remote = require('net.box')
> +---
> +...
> +-- gh-3010: COLLATE after LIMIT should throw an error
> +-- All of these tests should throw error "near "COLLATE": syntax error"
> +box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
> +---
> +- error: 'near "COLLATE": syntax error'
> +...
> +box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
> +---
> +- error: 'near "COLLATE": syntax error'
> +...
> +box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
> +---
> +- error: 'near "COLLATE": syntax error'
> +...
> +box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
> +---
> +- error: 'near "COLLATE": syntax error'
> +...
> +box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
> +---
> +- error: 'near "COLLATE": syntax error'
> +...
> +box.schema.user.grant('guest','read,write,execute', 'universe')
> +---
> +...
> +cn = remote.connect(box.cfg.listen)
> +---
> +...
> +cn:execute('select 1 limit ? collate not_exist', {1})
> +---
> +- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'
> +...
> +cn:close()
> +---
> +...
> +box.schema.user.revoke('guest', 'read,write,execute', 'universe')
> +---
> +...
> diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
> new file mode 100644
> index 0000000..fe8c1ba
> --- /dev/null
> +++ b/test/sql/collation.test.lua
> @@ -0,0 +1,19 @@
> +remote = require('net.box')
> +
> +-- gh-3010: COLLATE after LIMIT should throw an error
> +
> +-- All of these tests should throw error "near "COLLATE": syntax error"
> +box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
> +box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
> +box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
> +box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
> +box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
> +
> +
> +box.schema.user.grant('guest','read,write,execute', 'universe')
> +cn = remote.connect(box.cfg.listen)
> +
> +cn:execute('select 1 limit ? collate not_exist', {1})
> +
> +cn:close()
> +box.schema.user.revoke('guest', 'read,write,execute', 'universe')
> 
> 

[tarantool-patches] Re: [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[Kirill Yukhin]

Hello Mergen, Vlad,
On 21 мая 21:26, Vladislav Shpilevoy wrote:
> LGTM. Kirill, can you please take a look?

I've checked your patch into 2.0 branch.
In future, could you please follow SOP policies?
For details, please take a look ar chaptert `Submitting a patch`.

--
Regards, Kirill Yukhin

[tarantool-patches] [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[ImeevMA]

Originally, SQLite3 execute queries with COLLATE after LIMIT like
"SELECT * FROM test LIMIT N COLLATE not_exist"
and queries without COLLATE like
"SELECT * FROM test LIMIT N"
the same way.

Closes #3010
---
Branch: https://github.com/tarantool/tarantool/tree/gh-3010-COLLATE-after-LIMIT-throws-an-error
Issue: https://github.com/tarantool/tarantool/issues/3010

 src/box/sql/select.c        |  5 +++++
 test/sql/collation.result   | 41 +++++++++++++++++++++++++++++++++++++++++
 test/sql/collation.test.lua | 19 +++++++++++++++++++
 3 files changed, 65 insertions(+)
 create mode 100644 test/sql/collation.result
 create mode 100644 test/sql/collation.test.lua

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 29075d5..b11e688 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1993,6 +1993,11 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 	sqlite3ExprCacheClear(pParse);
 	assert(p->pOffset == 0 || p->pLimit != 0);
 	if (p->pLimit) {
+		if((p->pLimit->flags & EP_Collate) != 0 ||
+					(p->pOffset && (p->pOffset->flags & EP_Collate) != 0)) {
+			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+			return;
+		}
 		p->iLimit = iLimit = ++pParse->nMem;
 		v = sqlite3GetVdbe(pParse);
 		assert(v != 0);
diff --git a/test/sql/collation.result b/test/sql/collation.result
new file mode 100644
index 0000000..3a4f81f
--- /dev/null
+++ b/test/sql/collation.result
@@ -0,0 +1,41 @@
+remote = require('net.box')
+---
+...
+-- gh-3010: COLLATE after LIMIT should throw an error
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+---
+- error: 'near "COLLATE": syntax error'
+...
+box.schema.user.grant('guest','read,write,execute', 'universe')
+---
+...
+cn = remote.connect(box.cfg.listen)
+---
+...
+cn:execute('select 1 limit ? collate not_exist', {1})
+---
+- error: 'Failed to execute SQL statement: near "COLLATE": syntax error'
+...
+cn:close()
+---
+...
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
+---
+...
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
new file mode 100644
index 0000000..fe8c1ba
--- /dev/null
+++ b/test/sql/collation.test.lua
@@ -0,0 +1,19 @@
+remote = require('net.box')
+
+-- gh-3010: COLLATE after LIMIT should throw an error
+
+-- All of these tests should throw error "near "COLLATE": syntax error"
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY OFFSET 1;")
+box.sql.execute("SELECT 1 LIMIT 1 OFFSET 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1, 1 COLLATE BINARY;")
+box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY, 1;")
+
+
+box.schema.user.grant('guest','read,write,execute', 'universe')
+cn = remote.connect(box.cfg.listen)
+
+cn:execute('select 1 limit ? collate not_exist', {1})
+
+cn:close()
+box.schema.user.revoke('guest', 'read,write,execute', 'universe')
-- 
2.7.4

[tarantool-patches] [PATCH v2 1/1] sql: COLLATE after LIMIT throws an error

[ImeevMA]

Originally, SQLite3 execute queries with COLLATE after LIMIT like
"SELECT * FROM test LIMIT N COLLATE not_exist"
and queries without COLLATE like
"SELECT * FROM test LIMIT N"
the same way.

Closes #3010
---
Branch: https://github.com/tarantool/tarantool/tree/gh-3010-COLLATE-after-LIMIT-throws-an-error
Issue: https://github.com/tarantool/tarantool/issues/3010

 src/box/sql/select.c            |  5 +++++
 test/sql-tap/collation.test.lua | 44 ++++++++++++++++++++++++++++++++++++++++-
 2 files changed, 48 insertions(+), 1 deletion(-)

diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 29075d5..5e33256 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1993,6 +1993,11 @@ computeLimitRegisters(Parse * pParse, Select * p, int iBreak)
 	sqlite3ExprCacheClear(pParse);
 	assert(p->pOffset == 0 || p->pLimit != 0);
 	if (p->pLimit) {
+		if((p->pLimit->flags & EP_Collate) ||
+					(p->pOffset && p->pOffset->flags & EP_Collate)) {
+			sqlite3ErrorMsg(pParse, "near \"COLLATE\": syntax error");
+			return;
+		}
 		p->iLimit = iLimit = ++pParse->nMem;
 		v = sqlite3GetVdbe(pParse);
 		assert(v != 0);
diff --git a/test/sql-tap/collation.test.lua b/test/sql-tap/collation.test.lua
index 8a98de9..5c7255a 100755
--- a/test/sql-tap/collation.test.lua
+++ b/test/sql-tap/collation.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(173)
+test:plan(177)
 
 local prefix = "collation-"
 
@@ -249,4 +249,46 @@ local like_testcases =
 
 test:do_catchsql_set_test(like_testcases, prefix)
 
+-- gh-3010: COLLATE after LIMIT should throw an error
+
+test:do_catchsql_test(
+    "collate-after-limit-1.0",
+    "SELECT 1 LIMIT 1 COLLATE BINARY;", {
+        -- <collate-after-limit-1.0>
+    1, "near \"COLLATE\": syntax error"
+        -- <collate-after-limit-1.0>
+});
+
+test:do_catchsql_test(
+    "collate-after-limit-1.1",
+    "SELECT 1 LIMIT 1 OFFSET 2 COLLATE BINARY;", {
+        -- <collate-after-limit-1.1>
+    1, "near \"COLLATE\": syntax error"
+        -- <collate-after-limit-1.1>
+});
+
+test:do_catchsql_test(
+    "collate-after-limit-1.2",
+    "SELECT 1 LIMIT 1 COLLATE BINARY, 2;", {
+        -- <collate-after-limit-1.2>
+    1, "near \"COLLATE\": syntax error"
+        -- <collate-after-limit-1.2>
+});
+
+
+local net_box = require('net.box')
+local test_run = require('test_run')
+local inspector = test_run.new()
+
+inspector:cmd("create server second with script='box/box.lua'\n")
+inspector:cmd('start server second')
+local uri = inspector:eval('second', 'box.cfg.listen')[1]
+local conn = net_box.connect(uri)
+
+test:ok(not pcall(function() conn:execute("select 1 limit 1 collate not_exist") end),
+        'attempt to use collate after limit')
+
+conn:close()
+inspector:cmd('stop server second with cleanup=1')
+
 test:finish_test()
-- 
2.7.4