[tarantool-patches] Re: [PATCH 9/9] sql: make <search condition> accept only boolean

[Vladislav Shpilevoy <v.shpilevoy@tarantool.org>]

Thanks for the patch! See 4 comments below.

On 14/04/2019 18:04, Nikita Pettik wrote:
> <search condition> is a predicate used as a part of WHERE and
> JOIN clauses. ANSI SQL states that <search condition> must
> accept only boolean arguments. In our SQL it is implemented as
> bytecode instruction OP_If which in turn carries out logic of
> conditional jump. Since it can be involved in executing other routines
> different from <search condition>, 

1. Which other routines? What is a valid case of OP_If with non-boolean
value in check?

> we pass to it additional argument
> when generating bytecode for WHERE and JOIN clauses. When VDBE performs
> OP_If and detects such flag, it checks passed argument to be boolean.
> 
> Closes #3723

2. In addition, it fixes https://github.com/tarantool/tarantool/issues/3651,
doesn't it?

> diff --git a/test/sql-tap/e_delete.test.lua b/test/sql-tap/e_delete.test.lua
> index a58dc87c7..374a7d3e4 100755
> --- a/test/sql-tap/e_delete.test.lua
> +++ b/test/sql-tap/e_delete.test.lua
> @@ -89,15 +89,15 @@ test:do_delete_tests("e_delete-1.1", {
>  -- NULL are retained.
>  --
>  test:do_delete_tests("e_delete-1.2", {
> -    {1, "DELETE FROM t3 WHERE 1       ; SELECT x FROM t3", {}},
> -    {2, "DELETE FROM t4 WHERE 0  ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
> -    {3, "DELETE FROM t4 WHERE 0.0     ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
> +    {1, "DELETE FROM t3 WHERE true       ; SELECT x FROM t3", {}},
> +    {2, "DELETE FROM t4 WHERE false  ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
> +    {3, "DELETE FROM t4 WHERE false    ; SELECT x FROM t4", {1, 2, 3, 4, 5}},

3. The last two lines are exactly the same. Why not to drop one?

>      {4, "DELETE FROM t4 WHERE NULL    ; SELECT x FROM t4", {1, 2, 3, 4, 5}},
>      {5, "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4", {2}},
>      {6, "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4", {}},
>      {7, "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5", {1, 2, 3, 4}},
>      {8, "DELETE FROM t5 WHERE (SELECT max(x) FROM t4)  ;SELECT x FROM t5", {1, 2, 3, 4}},
> -    {9, "DELETE FROM t5 WHERE (SELECT max(x) FROM t6)  ;SELECT x FROM t5", {}},
> +    {9, "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) != 0  ;SELECT x FROM t5", {}},
>      {10, "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6", {"one", "four", "five"}},
>  })
>  
> diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
> index 970eeeed9..e47b0f43d 100755
> --- a/test/sql-tap/e_select1.test.lua
> +++ b/test/sql-tap/e_select1.test.lua
> @@ -448,15 +448,15 @@ test:do_select_tests(
>  -- true are included from the dataset.
>  --
>  local data ={
> -    {"1"," SELECT * FROM t1 JOIN_PATTERN t2 ON (1) ",t1_cross_t2},
> -    {"2"," SELECT * FROM t1 JOIN_PATTERN t2 ON (0) ",{}},
> +    {"1"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
> +    {"2"," SELECT * FROM t1 JOIN_PATTERN t2 ON (false) ",{}},
>      {"3"," SELECT * FROM t1 JOIN_PATTERN t2 ON (NULL) ",{}},
> -    {"6"," SELECT * FROM t1 JOIN_PATTERN t2 ON (0.9) ",t1_cross_t2},
> -    {"7"," SELECT * FROM t1 JOIN_PATTERN t2 ON ('0.9') ",t1_cross_t2},
> -    {"8"," SELECT * FROM t1 JOIN_PATTERN t2 ON (0.0) ",{}},
> +    {"6"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
> +    {"7"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},
> +    {"8"," SELECT * FROM t1 JOIN_PATTERN t2 ON (true) ",t1_cross_t2},

4. The same. 3 duplicates.