Win 7, SQLite 3.24.0, Tcl 8.6.9
We don't know if it is a problem about SQLite or Tcl, apologies if it
is the wrong list.
Our main concern is the type of a value returned by one UDF
dereferencing a tcl dictionnary : the type is changing depending from
the context.
We observed that a decimal number stored as a string with [format %.2f]
in a dictionnary is sometime understood as string at SQlite level, and
sometime undestood as a real.
Obviously, this lead to serious pb when comparison are done at SQL level.
Below is a script analysing all variations observed on this subject.
It points to different issues, but our main concern is at first about
the UDF.
package require sqlite3
sqlite3 db test
proc DerefDict {key} {
dict get $::tclDict $key
}
db function DerefDict_SQL -deterministic DerefDict
# (please note the -deterministic parameter of the UDF)
set i [format %.2f [expr {1 + 0}]]
dict set tclDict A $i
# Abreviations :
# OK = understood from reading the SQLite doc
# (!) , <- = not understood
# Sequence A
puts [dict get $tclDict A] ;# A1 returns 1.00 Reference
puts [db eval {SELECT $i, typeof($i)}] ;# A2 returns 1.00 text OK
puts [db eval "SELECT :i, typeof(:i)"] ;# A3 returns 1.00 text idem as
above
puts [db eval "SELECT DerefDict_SQL('A'),
typeof(DerefDict_SQL('A'))"] ;# A4 returns 1.00 text OK
puts [db eval "SELECT @i, typeof(@i)"] ;# A5 returns 1.00 blob OK
puts [db eval "SELECT DerefDict_SQL('A'),
typeof(DerefDict_SQL('A'))"] ;# A6 returns 1 real (!) <-
# Sequence B
if {$i == 0} {}
# Surprisingly, the above test on variable i modifies the type
# of returned values for : and @ operators in SQLite :
puts [dict get $tclDict A] ;# B1 returns 1.00 Reference
puts [db eval {SELECT $i, typeof($i)}] ;# B2 Changed with A : returns
1.0 real <-
puts [db eval "SELECT :i, typeof(:i)"] ;# B3 Changed with A : returns
1.0 real <-
puts [db eval "SELECT DerefDict_SQL('A'),
typeof(DerefDict_SQL('A'))"] ;# B4 Changed with A : returns 1.0
real <-
puts [db eval "SELECT @i, typeof(@i)"] ;# B5 Unchanged with A : returns
1.00 blob
puts [db eval "SELECT DerefDict_SQL('A'),
typeof(DerefDict_SQL('A'))"] ;# B6 Unchanged with A : returns 1
real (!) <-
# Sequence C
set i [format %.2f [expr {1 + 0}]]
if {$i eq "0"} {}
puts [dict get $tclDict A] ;# C1 returns 1.00 Reference
puts [db eval {SELECT $i, typeof($i)}] ;# C2 Changed with B : returns
1.00 text
puts [db eval "SELECT :i, typeof(:i)"] ;# C3 Changed with B :
returns 1.00 text <-
puts [db eval "SELECT DerefDict_SQL('A'),
typeof(DerefDict_SQL('A'))"] ;# C4 Changed with B : returns 1.0 real
puts [db eval "SELECT @i, typeof(@i)"] ;# C5 Unchanged with B : returns
1.00 blob
puts [db eval "SELECT DerefDict_SQL('A'),
typeof(DerefDict_SQL('A'))"] ;# C6 Changed with B : returns 1.0
real <-
Thank you in advance.
Jean-Baptiste
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel
antivirus Avast.
https://www.avast.com/antivirus
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
