Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?
Thank you Keith for the detail explanation. I misunderstood the 2 replies were opposite but this is not the case. Thank you again Jean-bapstiste ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?
Just to be sure, is it unsafe to write a non agregate SELECT with GROUP BY and HAVING clauses (without sub-SELECT) for the sole prupose explained before (even if the approache is discutable) ? I understand 2 different answers here : - "No, this kind of query can't be rewritten by the optimizer for the technical reasons (VDBE, index etc)" - "Yes it is unsafe, a future version of SQLite may optimize differently this kind of query" I can't see something related to my problem in the doc https://sqlite.org/optoverview.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?
Thank you Dominic and Keith for your replies The reason i asked this is that i have a query in wich one condition filtering the recordset involves an UDF and this UDF needs to be processed after all table filters have been applied Illustration : additionnal table : CREATE TABLE t2 ( a TEXT PRIMARY KEY, b INTEGER, c REAL); UDF : proc CallUDF {key} { if{[dict exists $myDict $key]} { dict get $myDict $key /* returns a value stored in myDict */ } else {error} } db function CallUDF -deterministic CallUDF If i do : SELECT * FROM t1 CROSS JOIN t2 WHERE t1.b > 10 AND t2.a = t1.a and t2.c < 0 AND CallUDF(t2.a) <> 0 Even though "CROSS JOIN" garanties that t2 is traversed after t1 has been filtered on "t1.b > 10", it seems there is no garanty when t2 is traversed, that the filter CallUDF(t2.a) <> 0 is evaluated *after* filters "t2.b = t1.b and t2.c < 0". In this case the sole solution we found is : SELECT * FROM t1, t2 WHERE t1.b > 10 AND t2.a = t1.a and t2.c < 0 GROUP BY t2.a HAVING CallUDF(t2.a) <> 0 Thank again Jean-Baptiste ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Planner GROUP BY and HAVING clauses optimization ?
Hi, Consider the following exemple : CREATE TABLE t1 ( a TEXT PRIMARY KEY, b INTEGER); SELECT * FROM t1 GROUP BY a HAVING b > 1; Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE clause by the optimizer ? Jean-Baptiste ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confused with type mismatch in Tcl interface
Sorry for my late reply. Yes, your proposal would definitively solve our problem. (our Tcl dictionnaries are allways filled with values sharing the same type) PS : on wich OS did you run the test script ? (the different outputs between the two computers gives unconfortable feeling) Thank you again 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
Re: [sqlite] Confused with type mismatch in Tcl interface
Le 27/02/2019 à 14:55, Richard Hipp a écrit : On 2/27/19, Richard Hipp wrote: The modified script can be downloaded from https://sqlite.orge/tmp/tcl-sqlite-testcase-20190227125331.txt Typo: "sqlite.org", not "sqlite.orge". https://sqlite.org/tmp/tcl-sqlite-testcase-20190227125331.txt Thank Mr Hipp, for your script Platform here : Win 7 + SQLite 3.24.0 + Tcl 8.6.9 Additional precisions : - Tcl provided by Magicsplat latest version (1.9.0) (https://www.magicsplat.com/tcl-installer/index.html) - SQLite package provided by AndroWish release "Asteroid Day" vanilla distribution (http://www.androwish.org/download/index.html) because we need JSON extension (absent from Magicsplat) I ran your script and I didn't get same results as yours. Here below are results from your script runed on my computer, unchanged from my first post : 8.6 8.6.9 {2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366d5221ac8fb199a87ca} # Sequence A 1.00 1.00 text 1.00 text 1.00 text 1.00 blob 1 real # Sequence B 1.00 1.0 real 1.0 real 1.0 real 1.00 blob 1 real # Sequence C 1.00 1.00 text 1.00 text 1.0 real 1.00 blob 1.0 real Compared to your output from your computer : 8.6 8.6.9 {2019-02-26 18:21:08 6d39d6a68bf09e0b4f6706218373e74fc03148fd8bdba5031c3de2f750d87cf2} # Sequence A 1.00 1.00 text 1.00 text 1.00 text 1.00 blob 1.00 text # Sequence B 1.00 1.0 real 1.0 real 1.0 real 1.00 blob 1.00 text # Sequence C 1.00 1.00 text 1.00 text 1.00 text 1.00 blob 1.00 text Apart the unclear reasons the outputs differ between the two computers, have you any explanation why the typof() output of "SELECT DerefDict_SQL('A'), typeof(DerefDict_SQL('A'))" (identical in both compturers) gives "text" in Sequence A and "real" in Sequence B ? Thank you 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
[sqlite] Confused with type mismatch in Tcl interface
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.00Reference 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 1real (!) <- # 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
[sqlite] Database is locked
On Window 7 with tcl/tk 8.6.6 and SQLite 3.13.0 The following tcl script craches : package require sqlite3 sqlite3 db1 dbfile1 db1 eval "SELECT A FROM TableA" { db1 eval "ATTACH DATABASE dbfile2 AS 'dbfile2'" db1 eval "SELECT B FROM dbfile2.TableB" db1 eval "DETACH DATABASE dbfile2" } Error message : database dbfile2 is locked while executing "db1 eval "DETACH DATABASE dbfile2"" Please, if anyone could explain to me what's wrong. Thanks Jean-Baptiste ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users