Wonky SQLite variable substitution in [db eval]

[Busirane]

Greetings from the frigid midwest USA!

The SQLite documentation at https://www.sqlite.org/tclsqlite.html says that [db eval] does variable substitution "in any position where it is legal to put a string or number literal". I'm learning that that means, for example, that column names aren't substituted. But I've got a case that clearly meets the "number literal" criterion, yet the substitution either doesn't occur, or occurs incorrectly! I'm using SQLite 3.13.0.

For this illustration I'm using a table called "plan" which has columns called "row" and "size"; they are explicit INTEGER columns, and "row" is not the PRIMARY KEY column. For what it's worth, I've tried this with and without an INDEX on "row", and also using another column "a".

A query with a hard-coded 5 gives the correct expected result:

() 4 % db eval {select row from plan where 5 between row+1 and row+size}
4 3 1

Replacing the 5 with a variable DOESN'T WORK!:

() 5 % set x 5
5
() 6 % db eval {select row from plan where :x between row+1 and row+size}

But it you do a no-op modification of the variable, it does work:

() 7 % db eval {select row from plan where :x+0 between row+1 and row+size}
4 3 1
() 8 % db eval {select row from plan where abs(:x) between row+1 and row+size}
4 3 1

I thought maybe it was a peculiarity of BETWEEN, so I expanded it:

() 47 % db eval {select row from plan where 5 >= row+1 and 5 <= row+size}
4 3 1

Replacing the first 5 with a variable gives the WRONG RESULT!:

() 48 % db eval {select row from plan where :x >= row+1 and 5 <= row+size}
5 6 7 8 9 10 11 12 13 4 14 3 15 1

But doing a no-op on it makes it work correctly:

() 49 % db eval {select row from plan where :x+0 >= row+1 and 5 <= row+size}
4 3 1

Replacing the second 5 doesn't work, but a no-op mod does:

() 54 % db eval {select row from plan where :x+0 >= row+1 and :x <= row+size}
() 55 % db eval {select row from plan where :x+0 >= row+1 and :x+0 <= row+size}
4 3 1

Understanding when variable substitution is or isn't done is one thing, but command 48 seems plain wrong. Is it a bug? Is there a more detailed discussion of the nuances of variable substitution somewhere? Google searches only turned up cases, like substituting table or column names, where you need to do the substitution in Tcl before passing it to [db eval]. Is this such a case?

[Peter Dean]

These issues are often solved with an explicit cast
https://www.sqlite.org/lang_expr.html#castexpr

[Busirane]

On Thursday, January 31, 2019 at 3:53:06 PM UTC-5, Peter Dean wrote:

> These issues are often solved with an explicit cast
> https://www.sqlite.org/lang_expr.html#castexpr

Indeed, CAST works as well as a no-op:

() 83 % db eval {select row from plan where cast(:x as integer) >= row+1 and 5 <= row+size}
4 3 1

I'm kind of curious why bare substitution doesn't work here, since it seems to work in other situations. But your solution is better, stylistically, since it doesn't leave readers wondering "why is he adding 0?". Thanks!

[heinrichmartin]

Disclamer: I don't know the internals, so this is gut feeling.

On Thursday, January 31, 2019 at 6:12:32 PM UTC+1, Busirane wrote:
> they are explicit INTEGER columns
> ...

> () 4 % db eval {select row from plan where 5 between row+1 and row+size}
> 4 3 1

You are not directly comparing to the column. Do we expect that the type of the right-hand expressions is derived?

> () 48 % db eval {select row from plan where :x >= row+1 and 5 <= row+size}
> 5 6 7 8 9 10 11 12 13 4 14 3 15 1

> ...

> Understanding when variable substitution is or isn't done is one thing, but command 48 seems plain wrong. Is it a bug?

Are you using one constant and one variable intentionally? Is the result correct, if >= compares texts?

> The SQLite documentation at https://www.sqlite.org/tclsqlite.html says

> ...

> Is there a more detailed discussion of the nuances of variable substitution somewhere?

Representation is relevant in some cases according to the doc (bytearray->blob, string->text). Have you tried [set x [expr 5]]?

[jm....@orens.fr]

Le jeudi 31 janvier 2019 18:12:32 UTC+1, Busirane a écrit :
> Replacing the 5 with a variable DOESN'T WORK!:
>
> () 5 % set x 5
> 5
> () 6 % db eval {select row from plan where :x between row+1 and row+size}
>

Hello Busirane,

SQLite variable substitution in the tcl shell with ":x" format works for me :

(jmc) 1 % package require sqlite3
3.24.0
(jmc) 2 % sqlite3 db1 :memory:
(jmc) 3 % set a 123
123
(jmc) 4 % tk_messageBox -message "[db1 eval {SELECT :a}]"

-> displays 123

FYI, column's type is not enforced by SQlite

-> see https://sqlite.org/datatype3.html#determination_of_column_affinity

Can you try

"SELECT typeof(row), typeof(size) FROM Plan" query,

to check that some values stored in the columns are not of type string instead of type integer as intended ?

Jean-Marie

[Florian Murr]

As has been mentioned before "set x [expr 5]" might be a solution.
It looks to me, as if we see the "Tcl/SQlite impedance problem" here. <https://wiki.tcl-lang.org/page/Datatype+impedance+mismatch+between+Tcl+and+SQLite>.
From a Tcl perspective there is no difference, but SQLite looks under the hood at the current variable representation in C. There "set x 5" and "set x [expr 5]" get represented differently: the first a string, the second as integer.
- Florian

[Peter Dean]

This has been solved. Use CAST so you know for sure.

[Busirane]

On Friday, February 1, 2019 at 3:55:30 AM UTC-5, heinrichmartin wrote:
> Representation is relevant in some cases according to the doc (bytearray->blob, string->text). Have you tried [set x [expr 5]]?

Indeed, [set x [expr 5]] gives SQLite the hint it needs to treat 5 as a number. But, it's still safer to CAST it explicitly, as needed, instead of assuming that everyplace that sets it sets it correctly.

Thanks for the reply.