Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-15 Thread Jean-Baptiste Gardette

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 ?

2020-01-15 Thread Jean-Baptiste Gardette
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 ?

2020-01-14 Thread Jean-Baptiste Gardette

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 ?

2020-01-14 Thread Jean-Baptiste Gardette

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

2019-02-27 Thread Jean-Baptiste Gardette

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

2019-02-27 Thread Jean-Baptiste Gardette


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

2019-02-27 Thread Jean-Baptiste Gardette

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

2016-11-09 Thread Jean-Baptiste Gardette

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