Re: [sqlite] New word to replace "serverless"

[E.Pasma]

I thougth about self-service, self-serve or self-served. Thanks, E. Pasma

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing a statement cache

[E.Pasma]

> Op 16 dec. 2019, om 22:38 heeft carsten.muencheberg 
>  het volgende geschreven:
> 
> Hi,
> I am working on a generic cache for prepared statements and would like to 
> make sure that I am not overlooking anything important.
> 
> The cache is a simple map from an SQL string to a statement pointer.
> 
> 1. When to call sqlite3_reset()? It looks like the safest and easiest 
> approach is to call sqlite3_reset() immediately after retrieving a statement 
> from the cache. Is there any disadvantage in regards to concurrency or 
> performance in keeping dozens or hundreds of statements alive in a non reset 
> state e.g. SELECT statements which have not stepped over all rows?
> 
> 2. When to call sqlite3_clear_bindings()? If I understand correctly new 
> values can be bound without clearing old ones first, but calling 
> sqlite3_clear_bindings() can be a safeguard against accidentally executing a 
> statement with old values?
> 
> 3. When to clear the cache? I read that in some cases statements are 
> automatically recompiled when a different value is bound to a parameter 
> inside the WHERE clause. What about SQLITE_ENABLE_STAT4 and ANALYZE, would it 
> make sense to clear the cache afterwards or can we trust SQLite to maintain 
> existing statements under all circumstances?
> 
> 4. Other ideas, comments?
> 
> Thanks in advance.
> Carsten
Hi, as nobody answers yet to the real questions, I just mention that a 
statement cache is in tclsqlite3.c. That may be a useful example. It is also in 
the apsw python interface. Regards, E. Pasma


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation issue: carray

[E.Pasma]

Hello, 
on the page https://www.sqlite.org/carray.html 


This query gives the same result:
-->
This query gives the same results, uniquely ordered.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

[E.Pasma]

Keith,

The final script produces corresponding results here, only a constant factor 
slower (minimal CPU). The rows per second is useful to summarize the tests for 
various keyset sizes. Below is the average per method with input parameter 5.

meth|rps|note
1|149431|Individual Row 
2|195447|Individual Row (Sorted)
3|167740|Rows ExecuteMany   
3|167740|Rows ExecuteMany Sorted 
4|146503|Using IN temp  
5|149261|Using IN temp (sorted) 
6|137831|Using IN keyset
7|136984|Using IN keyset sorted 
8|170922|Using IN (dynamic)
9|188759|Using IN (sorted)  
A|242761|Using IN CArray
B|274883|Using IN CArray sorted 
C|308547|Using Array JOIN sorted

Hope this is useful to the original poster.

To me SQLite-Python is almost addicting. I learned to use carray now. It 
appears to interface brillantly with Python's array module. Only I have a 
custom carray instead of a custom execute method as you APSW (replaced 
sqlite3_bind_pointer by sqlite3_value_int64, for home use only).

Method C is a JOIN to carray, where the keys are sorted. Order by is not needed 
then.
   select x.* from carray(?,?,'int64') cross join x on id=value
 

Thanks, E. Pasma 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

[E.Pasma]

Stop stop stop

> create table x
> (
>id  integer primay key,
>datablob
> );

I did not see this until searching for the word PRIMARY and not finding it. 
Thus id is not a primary key at all. Probably it is a good habit to always add 
WITHOUT ROWID when there is an explicit primary key. The SQL parser would then 
have reported an error.   

The tests with individual rows must definitely be repeated.
 
In my tests the results are closer together:

Method 1: Retrieve Individual Row 00:00:00.081151 1
Method 3: using dynamic in00:00:00.060368 9995
Method 5: using in carray 00:00:00.050884 9995
Method 5: using carray join   00:00:00.043127 1
Method 6: Using temp table00:00:00.060808 9995

(for oarameter = 1)

I tuned the Python script, using fetchone() in the individual row test. 
And I added a temp table test. In Python this just uses executemany() to insert 
the rowset.

Thanks, E. Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Differentiate between an empty result set and an error using Python3

[E.Pasma]

> Op 5 sep. 2019, om 00:10 heeft Keith Medcalf  het 
> volgende geschreven:
> 
> 
> On Wednesday, 4 September, 2019 12:18, Rob Sciuk  wrote:
> 
>> Forgive me if this is an FAQ, but in looking over the python3 interface to
>> SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
>> execute() command.
> 
>> My use case is to differentiate between an empty row set (OK) vs an error
>> of some kind in the query.
> 
>> Anyone figured this out?
> 
> If there is an error then an exception will be thrown.  No exception means no 
> error.  Otherwise, iterating over the cursor will return the rows.  If there 
> are no rows then it is like iterating over an empty list -- there is nothing 
> to return (the cursor object is a generator that yields row tuples and it 
> will internally raise StopIteration when it is out of data to return, just 
> like any other generator).
> 
> -- 
Possibly the Python documentation is overwhelming here as data can also be 
retrieved with explicit fetch steps. The example where the cursor is treated as 
a generator shows how simple it is:
>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

(from https://docs.python.org/3/library/sqlite3.html 
)

Personal note: even more simple is to use the execute method directly from the 
connection instance.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding the WITH clause

[E.Pasma]

> Op 15 jun. 2019, om 19:20 heeft Sam Carleton  het 
> volgende geschreven:
> 
> I have kept reading and the next section of the book does away with the
> update and simply creates a recursive function.  The goal of the function
> is to determine the 'weight' of a part by adding up all the subassemblies *
> qty.  At first I thought this might be easier to convert into SQLite, but
> upon deeper reading of the SQLite doc's, I am getting the impression this
> is NOT the type of thing the WITH statement can do.  Below is the stored
> proc from the book, followed by the SQL to create the table and populate
> it.
> 
> Can this be converted, if so, how?
> 
> CREATE FUNCTION WgtCalc(IN MY_PART CHAR(2))
> RETURNS INTEGER
> LANGUAGE SQL
> DETERMINISTIC
> -- RECURSIVE FUNCTION
> RETURN
> (select coalesce(sum(Subassemblies.qty *
>case when Subassemblies.lft + 1 = Subassemblies.rgt
> then subassemblies.wgt
> else WgtCalc(Subassemblies.part)
> end), max(Assemblies.wgt))
> from Frammis as Assemblies
>left join Frammis as Subassemblies on
>Assemblies.lft < Subassemblies.lft and
>Assemblies.rgt > Subassemblies.rgt and
>not exists (
>select *
>from frammis
>where lft < Subassemblies.lft and
>  lft > Assemblies.lft and
>  rgt > Subassemblies.rgt and
>  rgt < Assemblies.rgt)
> where Assemblies.part = MY_PART);
> 
> -- --
> 
> create table frammis
> (
>part char,
>qty  int,
>wgt  int,
>lft  int,
>rgt  int
> );
> 
> create unique index frammis_part_uindex
>on frammis (part);
> 
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('a', 1,  0,  1, 28);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('b', 1,  0,  2,  5);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('c', 2,  0,  6, 19);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('d', 2,  0, 20, 27);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('e', 2, 12,  3,  4);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('f', 5,  0,  7, 16);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('g', 2,  6, 17, 18);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('h', 3,  0, 21, 26);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('I', 4,  8,  8,  9);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('j', 1,  0, 10, 15);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('k', 5,  3, 22, 23);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('l', 1,  4, 24, 25);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('m', 2,  7, 11, 12);
> INSERT INTO frammis (part, qty, wgt, lft, rgt) VALUES ('n', 3,  2, 13, 14);
> 
> Pax vobiscum,
> Sam Carleton
Hello,
I think it is just a regular tree walk. Below is a recursive CTE for that, 
keeping track of the quantity and counting the weight of the leaves.
Message to Luuk: thanks for explaining Pax vobiscum.
E. Pasma


select  part, 
(-- correlated subquery to calculate the composed weight
with r as (
select a.part, 1 as qty, a.wgt, a.lft, a.rgt
union all
select b.part, r.qty*b.qty, b.wgt, b.lft, b.rgt
from r
join Frammis as b
on  b.lft > r.lft and
b.rgt < r.rgt and
not exists (-- condition to descend just one level at a time
select *
from frammis as c
where
c.lft < b.lft and
c.lft > r.lft and
c.rgt > b.rgt and
c.rgt < r.rgt)
)
select  sum (r.qty*r.wgt)
from r 
where   not exists (-- condition to count only elementary parts 
select *
from frammis as d
where
d.lft > r.lft and
d.rgt < r.rgt)
) as sumwgt
from Frammis as a   
;

a|682
b|24
c|272
d|57
e|12
f|52
g|6
h|19
I|8
j|20
k|3
l|4
m|7
n|2
 
















___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimising multiple group by clauses

[E.Pasma]

Hello,


> explain query plan select
>  prod,
>  per,
>  min(val)
> from
>  (select
>prod,
>per,
>mar,
>sum(val) as val
>  from
>data
>  group by
>prod,
>per,
>mar)
> group by
>  prod,
>  per
> ;
> QUERY PLAN
> |--CO-ROUTINE 1
> |  `--SCAN TABLE data USING INDEX sqlite_autoindex_data_1
> |--SCAN SUBQUERY 1
> `--USE TEMP B-TREE FOR GROUP BY


I constructed an equivalent query that does not involve a temporary B-tree. I 
don't expect this to be useful for the real case. But it proves that speed can 
be improved. With 1000*100*10*1 rows in the data table, my timing was reduced 
from 0.859 to 0.635.

E. Pasma 

explain query plan select
  prod,
  per,
  (select min(val)
   from 
(select
  sum(val) as val
from
  data
where 
  (prod,per)=(v1.prod,v1.per)
group by
  prod,
  per,
  mar))
from 
 (select 
   prod, 
   per 
 from 
   data 
 group by
   prod, 
   per) v1
;
QUERY PLAN
|--CO-ROUTINE 3
|  `--SCAN TABLE data USING COVERING INDEX sqlite_autoindex_data_1
|--SCAN SUBQUERY 3 AS v1
`--CORRELATED SCALAR SUBQUERY 2
   |--CO-ROUTINE 1
   |  `--SEARCH TABLE data USING INDEX sqlite_autoindex_data_1 (prod=? AND 
per=?)
   `--SEARCH SUBQUERY 1


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row is not fetched with PRAGMA reverse_unordered_selects=true

[E.Pasma]

> Op 9 mei 2019, om 00:07 heeft Manuel Rigger  het 
> volgende geschreven:
> Hi,
> 
> I discovered another bug that is triggered when "PRAGMA
> reverse_unordered_selects=true" is used. It's similar to a previous bug
> that I reported [1], but the statement triggering the bug has a compound
> expression that should always be true (for values that are not NULL) in the
> WHERE clause:
> 
> CREATE TABLE t0(c0 INTEGER PRIMARY KEY);
> INSERT INTO t0(c0) VALUES (1);
> PRAGMA reverse_unordered_selects=true;
> SELECT * FROM t0 WHERE ((t0.c0 > 'a') OR (t0.c0 <= 'a')); -- fetches no row
> SELECT ((t0.c0 > 'a') OR (t0.c0 <= 'a')) FROM t0; -- returns 1
> 
> Best,
> Manuel
> 
> 
> [1] https://www.sqlite.org/src/tktview?name=9cf6c9bb51

Hi Mauel,

This appears to have been fixed with the previous bug that you referred to. The 
example in the referred ticket is even more simple:
CREATE TABLE t14(x INTEGER PRIMARY KEY);
  INSERT INTO t14(x) VALUES (100);
  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
Hope I'm not mistaken,

E. Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"

[E.Pasma]

> Op 11 mrt. 2019, om 13:20 heeft Graham Holden  het 
> volgende geschreven:
> 
> I'm using SQLite through Tcl, and am having a problem with the
> sqlite3/Tcl "copy" command (similar to the shell's ".import" command).
> 
> Given "test.csv"
> 1,"aaa","bbb ccc"
> 
> Using the shell, I get the following -- the double-quotes from the CSV
> are not stored in the database:
> 
> create table test ( id integer primary key, str1 text, str2 text ) ;
> .mode csv
> .import test.csv test
> .mode column
> select * from test ;
> 1   aaa bbb ccc
> 
> but using the following Tcl script:
> 
> package require sqlite3
> sqlite3 db :memory:
> db eval "create table test ( id integer primary key, str1 text, str2 text )"
> db copy ignore test test.csv ","
> db eval "select * from test" { puts "|$id|$str1|$str2:" }
> db eval "update test set str1='aaa', str2='bbb ccc'"
> db eval "select * from test" { puts "|$id|$str1|$str2:" }
> 
> produces:
> |1|"aaa"|"bbb ccc":
> |1|aaa|bbb ccc:
> 
> showing the double-quotes are stored in the database.
> 
> Is there a way to not get the double-quotes stored? NOTE: It may be a
> "version thing"... the version of SQLite bundled-in with the copy of
> Tcl I'm using is a little old (3.8.7.1) whereas the shell is 3.27.2.
> Unfortunately, I can't easily switch the Tcl version at the moment
> (but I might have to if it is something that's been fixed).
> 
> On a related note, under Windows, with CR-LF terminated lines in the
> CSV file, the CR also gets stored in the string (that's the reason for
> the ":" in the above test script: the value for 'str2' becomes
> 
>  "bbb ccc"\r
> 
> (with an embedded carriage-return), but I can work around this by
> switching to LF-terminated lines.
> 
> TIA for any help,
> Graham

Hello,
I can confirm that this has nothing to do with the sqlite version, as it is so 
in the tcl binding from the current release (3.28.0.). There is no "mode" 
method or paramater like in the shell.
I'd probably work around this by an update in SQL, after the import. Or import 
into a view, with an "instead of insert" trigger to insert cleaned data into 
the table.
Sorry, E. Pasma.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using sqlite3_interrupt with a timeout

[E.Pasma]

A question was if a sort is also programmatically interruptable. With the 
knowledge that the command tool calls  sqlite3_interrupt upon the first 
CONTROL-C, this is easily tested. From the timings below it appears to be so.

$ sqlite3
SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 
create table t (a);
insert into t
with r as (select 1 as i union all select i+1 from r where i<1000)
select 'row ' || i from r;

.timer on
select * from t order by a limit 1 offset 100;
row 185
Run Time: real 0.949 user 0.944579 sys 0.001983

select * from t order by a limit 1 offset 100;
^C
Run Time: real 0.160 user 0.159095 sys 0.000481
Error: near line 2: interrupted
sqlite> 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suitability for Macintosh OS 9.2?

[E.Pasma]

Hello,

I use SQLite on a Macintosh (snow white) with OS 9.2. However SQLite actually 
runs on a new mac mini and is accessed via virtual network computing, using 
VNCthing 2.2. On the MacOS side, only desktop sharing must be switched on.   

I thought I should mention it here as it is exclusively to run SQLite, in a 
terminal window. 

I had to overcome two issues: vi does not behave properly. When typing a '+' it 
opens a new line. This is overcome by using MacVIM. 

Since upgrading the Mac mini to OS X Mojave (10.14.2), VNC hangs when logging 
in for the second time, after disconnecting. This is overcome by logging out 
before disconnecting. 

Thanks, E. Pasma


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[E.Pasma]

> 2 dec. 2018, Keith Medcalf:
> 
> 
> Well if it is unique and not null, then why not just make it the rowid?  In 
> either case, you would still have to permute the storage tree at insert time 
> if the inserts were not in-order.  So let us compare them shall we:
> 
> sqlite> create table x(value INTEGER PRIMARY KEY);
> sqlite> insert into x select random() from generate_series where start=1 and 
> stop=1;
> Run Time: real 185.795 user 184.265625 sys 0.343750
> 
> sqlite> create table x(value INTEGER PRIMARY KEY) WITHOUT ROWID;
> sqlite> insert into x select random() from generate_series where start=1 and 
> stop=1;
> Run Time: real 174.661 user 173.890625 sys 0.00
> 
> sqlite> create table x(value INTEGER NOT NULL);
> sqlite> insert into x select random() from generate_series where start=1 and 
> stop=1;
> Run Time: real 20.287 user 20.265625 sys 0.00
> sqlite> create unique index ix on x(value);
> Run Time: real 211.556 user 207.562500 sys 2.562500
> 
> sqlite> create table x(value INTEGER NOT NULL);
> sqlite> create unique index ix on x(value);
> sqlite> insert into x select random() from generate_series where start=1 and 
> stop=1;
> Run Time: real 196.719 user 195.437500 sys 0.00
> 
> So, the winner is (in this case, with no other payload) that the INTEGER 
> PRIMARY KEY on a WITHOUT ROWID table is the most time efficient, followed by 
> being the INTEGER PRIMARY KEY of a ROWID table.  Those two also happen to be 
> the most space-efficient as well.  Interestingly it was overall faster to 
> build and maintain the separate index at insert time in this example than to 
> build the index seperately, but not by much.  Note this is for 100,000,000 
> records processed entitely in RAM in a single transaction ... 

Thanks because this speed is what I had expected. Except that creating the 
index beforehand can be overall faster than adding it after the data is 
inserted.
Unfortunately, but almost funny, my measurements are almost the reverse of what 
you see. See below. 
Possible clues:
- I tested with a file database, not memory.
- I reduced the number of rows to 10.000.000
- I have only a basic system with 4Gb RAM and a single processor

.timer on
.load series
drop table if exists x;
create table x(value INTEGER PRIMARY KEY);

insert into x select random() from generate_series where start=1 and 
stop=1000;
Run Time: real 87.673 user 37.691114 sys 43.527249

drop table x;
create table x(value INTEGER PRIMARY KEY) WITHOUT ROWID;

insert into x select random() from generate_series where start=1 and 
stop=1000;
Run Time: real 88.759 user 36.276227 sys 44.190566

drop table x;
create table x(value INTEGER NOT NULL);

insert into x select random() from generate_series where start=1 and 
stop=1000;
Run Time: real 3.980 user 2.266869 sys 0.124012

create unique index ix on x(value);
Run Time: real 10.131 user 7.623369 sys 0.797015

drop table x;
create table x(value INTEGER NOT NULL);
create unique index ix on x(value);

insert into x select random() from generate_series where start=1 and 
stop=1000;
Run Time: real 107.647 user 41.249081 sys 50.869456


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[E.Pasma]

> 2 dec. 2018, E.Pasma:
> 
>> 30 nov. 2018, AJ Miles:
>> 
>> Ah, this tool seems very handy. For those curious, I'll paste the results
>> below. The index approximately doubles the storage size, but I am
>> intentionally making that tradeoff to avoid the slow down when enforcing a
>> unique/primary key on the Reference table while inserting.
>> 
>> -AJ
> 
> Hello, 
> 
> is the "integer" column unique and a potential primary key? Then it would be 
> surprising that defining it as such causes a slowdown. Assuming the table is 
> then defined WITHOUT ROWID. And that the multithreading pragma is set. 
> 
> About sqlite_analyzer: on macOS 10.13.6 I got
> ..Tcl.framework/Versions/8.4.. image not found
> After editing the binary file as below, it is alright.
> vi sqlite3_analyzer
> :%s/8\.4/8.5/
> :x
> 
> Thanks for the intriguing subject. E. Pasma.
Sorry,

In a small test, I measured inserting in a table with an INTEGER PRIMARY KEY 
and without rowid.
Unfortunately, it causes a slowdown of a factor 5, compared to your schema.

In my test, the rows are inserted in random order. If inserted in the order of 
the key value, the result is different altogether. But it looks hard to achieve 
that with billions of rows.

Thanks, E. Pasma




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[E.Pasma]

>  30 nov. 2018, AJ Miles:
> 
> Ah, this tool seems very handy. For those curious, I'll paste the results
> below. The index approximately doubles the storage size, but I am
> intentionally making that tradeoff to avoid the slow down when enforcing a
> unique/primary key on the Reference table while inserting.
> 
> -AJ

Hello, 

is the "integer" column unique and a potential primary key? Then it would be 
surprising that defining it as such causes a slowdown. Assuming the table is 
then defined WITHOUT ROWID. And that the multithreading pragma is set. 

About sqlite_analyzer: on macOS 10.13.6 I got
..Tcl.framework/Versions/8.4.. image not found
After editing the binary file as below, it is alright.
vi sqlite3_analyzer
:%s/8\.4/8.5/
:x

Thanks for the intriguing subject. E. Pasma.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Bug? Confused data entry with column name

[E.Pasma]

and the confusing behaviour is admitted to be a "quirk" in SQLite:
https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

[E.Pasma]

> 25 nov. 2018, 20:14 Digital Dog wrote:
> 
> I may have phrased the subject in a wrong way which misguided you.


Sorry, I was misguided by my limited experience with PRIMARY KEY's. I use to 
combine these with WITHOUT ROWID. As you referred to and quoted from the 
documentation, it is only in that case that primary key columns become not 
null. I see the reason for a separate setting now. Thanks, E. Pasma

btw I like the first suggestion (DEFAULT_WITHOUT_ROWID) 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

[E.Pasma]

Digital Dog wrote:
> 
> PRAGMA default_without_rowid = on
> To make all tables created while the directive is in use the WITHOUT ROWID
> tables.
..

> 
> PRAGMA enforce_not_null_on_primary_key = on
> For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would
> restore correct behavior.


Hello, just a remark about this second pragma.
Enforce_not_null_on+primary_key seems obsolete to me, also for "rowid" tables, 
that do not have a primary key defined.
Because once the first suggested pragma (default_without_rowid) is set, this 
rowid will be treated as a true integer primary key, including the NOT NULL 
property. And this also applies if a table is defined with INTEGER PRIMARY KEY 
as an alias for roiwid. 
Does that leave only the first suggestion? 

Thanks, E. Pasma 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying row count

[E.Pasma]

> R Smith:
> 
> Simply add a column to any select like this: et voila...


I tried without  "partition by 1" and that works as well: "row_number() OVER ()"


 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optmize queries on ranges

[E.Pasma]

> Keith Medcalf wrote:
>  .. Am I doing something wrong here ..

No! The query with order by + limit 1 is superior, also in my test. 

Still I am surprised that the rtree extension is available by default
(at least in the sqlite version 3.25 command line)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optmize queries on ranges

[E.Pasma]

About the rtree extension, which was the first idea.

The extension appears available without any special installation option. This 
is easier than what is mentioned in https://sqlite.org/rtree.html 
 chapter 2: "Compiling The R*Tree Module". 
This chapter may as well be left out?

With test data where the ranges are mostly non-overlapping, the query now runs 
faster than without rtree. Even though both run within a millisecond rtree is 
ten times faster.
With order by and limit the timing remains superior. But this relies on 
strictly non-overlapping ranges.
Below my test script


/* query 1: using rtree built-in extension */
;
create virtual table ranges using rtree(id, minX, maxX, +value);
with r as (select 0 as r union all select r+1 from r where r<100)
insert into ranges (minX, maxX, value) 
select r*10+1,r*10+10,r*10+5 from r
;
select value from ranges where 123456 between minx and maxx
;
123455
Run Time: real 0.000 user 0.000135 sys 0.18

/* query 2: using index on minx+maxx */
drop table ranges
;
create table ranges (minx int, maxx int, value int)
;
with r as (select 0 as r union all select r+1 from r where r<100)
insert into ranges (minX, maxX, value) 
select r*10+1,r*10+10,r*10+5 from r
;
create unique index ranges_minx_maxx on ranges(minx,maxx)
;
select value from ranges where 123456 between minx and maxx
;
123455
Run Time: real 0.002 user 0.001415 sys 0.16

/* query 3: same, assuming non-overlapping ranges */
select value from ranges where 123456 between minx and maxx
order by minx desc limit 1
;
123455
Run Time: real 0.000 user 0.57 sys 0.00

 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[E.Pasma]

Clemens Ladisch wrote:
> 
> John Found wrote:
>> Also, it seems max(b = ?1) will do the trick as well as count(b = ?1)
>> 
>> And here another question appears. What is more efficient?
> 
> In SQLite, both are equally efficient.
> 
> Use whatever makes the query easier to understand.
Clemens, I just learned from you that count(b=?1) is not an option as it also 
counts zero or false. Thanks, E. Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[E.Pasma]

> Op 12 okt. 2018, om 11:23 heeft Clemens Ladisch  het 
> volgende geschreven:
> 
> E.Pasma wrote:
>> select group_concat(b) as list
>> from t
>> group by a
>> having count(b=?1)
>> ;
> 
> In SQLite, a boolean expression returns 0 when false, and count(0) is 1.
> You have to generate a NULL for failed matches, or use another function
> like sum() or max() that can filter out zeros.
> 
Ok, I withdraw my suggestion.
Yours:

 select
   group_concat(b) as list
 from t
 where a in (select a
 from t
 where b = ?1)
 group by a;

looks more meanibgful then.
Also, with large amounts of data, and if column b is indexed, it likely becomes 
more efficient. 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[E.Pasma]

> John Found :
> 
> The following code does not work, but gives an idea what I want to do:
> 
>create table t (a, b);
> 
>select 
>  group_concat(b) as list 
>from t 
>group by a
>having ?1 in (list);


> 
> Clemens Ladisch  :
> 
>  select
>group_concat(b) as list
>  from t
>  where a in (select a
>  from t
>  where b = ?1)
>  group by a;


I think the original query is the best, here the having clause is be written as 
valid sql.

select group_concat(b) as list
from t
group by a
having count(b=?1)
;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] shell csv import

[E.Pasma]

> Rowan Worth wrote:
> 
> You can also filter out specific messages at the shell level:
> 
> sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the
> rest with NULL' >&2)
> 
> But note that the >() syntax is not a POSIX sh feature, and will not work
> in a script using a shebang of #!/bin/sh. You need to change it to
> #!/bin/bash or whatever shell you have on hand. For more info see the
> "Process Substition" section of the bash man page.
> 
> If you have the ability to modify the generated SQL, presumably you could
> avoid the error by generating a NULL yourself for the missing column?
> -Rowan
I replied just before reading this. This solution may be preferred. 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] shell csv import

[E.Pasma]

> I have a script that loads csv into an existing table.
> 
> I get this message on stderr for each row imported:
> 
> "... expected 7 columns but found 6 - filling the rest with NULL"
> 
> 
> We have the means to send stdout to /dev/null using the .once or .output
> 
> Is there a way to send suppress stderr messages for a dot command?

The example below works without warnings (though this diverts from exact 
question).

create table t (a,b,c,d,e,f)
;
/* Procedure to insert a line of input */
create view procinput(a,b,c) as values(null,null,null)
;
create trigger ins_procinput instead of insert on procinput
begin 
  insert into t(a,b,c) values(new.a,new.b,new.c); 
end
;

.import input.dat procinput



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there permanent link to the latest SQLite amalgamation source?

[E.Pasma]

John Found wrote:
> In order to write an autoupdater, I need to download the latest SQLite 
> amalgamation.
> Is there a permanent link to the subject, or the only way is to parse the 
> download page
> for links to "sqlite-amalgamation-*.zip" or to build it from the fossil 
> checkout?

The apsw installer, setup.py, can parse the download page. This has likely been 
tested very well:

# work out the version
if self.version is None:
write("  Getting download page to work out current SQLite version")
page=self.download("https://sqlite.org/download.html;, text=True, 
checksum=False)

match=re.search(r'sqlite-amalgamation-3([0-9][0-9])([0-9][0-9])([0-9][0-9])\.zip',
 page)
if match:
self.version="3.%d.%d.%d" % tuple([int(match.group(n)) for n in 
range(1,4)])
if self.version.endswith(".0"):
self.version=self.version[:-len(".0")]
else:
write("Unable to determine current SQLite version.  Use 
--version=VERSION", sys.stderr)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Default Values Pragma bug

[E.Pasma]

Hello Ryan,

Your already moderate complaint needs further moderation
After reading  https://www.sqlite.org/lang_createtable.html#dfltval 
 I see that the default 
value may be a function name (when written inside parenthesis) or a special 
name like current_date. 
So it is not just a constant and needs parsing anyway.
This may make it better acceptable as it is, with comments included in the 
output of pragma table_info().

E. Pasma

PS there is a small typing mistake in above document:
..constant if it does contains
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] idea for joining a complex view

[E.Pasma]

Hello, I still want to mention an idea for joining a complex view that I used 
to for the sudoku solver 
https://www.sqlite.org/lang_with.html#sudoku 
It is a virtual table with a single row and a column that just echos the value 
it gets passed in. It is named magnet here. The view is as below. The source of 
the extension is not included.
Drawbacks:
- need to compile an extension
- a unique sort is applied to the result set
- current version only deals with a single column
E. Pasma

.load sqlite_magnet

CREATE VIEW sudsol 
AS
SELECT m1.value AS sud, m2.value AS sol
 FROM magnet m1, magnet m2
WHERE m2.value IN (
WITH RECURSIVE
  digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  ),
  x(s, ind) AS (
SELECT m1.value AS sud, instr(m1.value, '.')
UNION ALL
SELECT
  substr(s, 1, ind-1) || z || substr(s, ind+1),
  instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
 FROM x, digits AS z
WHERE ind>0
  AND NOT EXISTS (
SELECT 1
  FROM digits AS lp
 WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
 )
  )
SELECT s FROM x WHERE ind=0
)
;

SELECT sol FROM sudsol WHERE sud=
   '53.'||'.7.'||'...'
|| '6..'||'195'||'...'
|| '.98'||'...'||'.6.'

|| '8..'||'.6.'||'..3'
|| '4..'||'8.3'||'..1'
|| '7..'||'.2.'||'..6'

|| '.6.'||'...'||'28.'
|| '...'||'419'||'..5'
|| '...'||'.8.'||'.79'
;

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

[E.Pasma]

Hello,

The new example is clear, and therefore also raises a question:

> CREATE TABLE people (name, age);
> INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
> 
> CREATE VIEW older PARAMETERS (name, otherName) AS
> SELECT t1.age > t2.age AS older
>  FROM people AS t1 WHERE t1.name = parameters.name
> , people AS t2 WHERE t2.name = parameters.otherName;
> 
> SELECT t1.name AS name
> , t2.name AS otherName
> , older(t1.name, t2.name)
>  FROM people AS t1
> , people AS t2
> WHERE t1.name != t2.name;
> 
> name  otherName  older
>   -  -
> Bob   Jen1
> Bob   Liz1
> Jen   Bob0
> Jen   Liz0
> Liz   Bob0
> Liz   Jen1

The function "older" is used here in the SELECT part. But as a table-valued 
function I think it must be in the FROM part:

SELECT t1.name AS name, t2.name AS otherName, older --column name
 FROM people AS t1, people AS t2, older(t1.name, t2.name)
WHERE t1.name != t2.name;

My question is: do you envisage a regular function, returning a single value, 
or a table-valued function. (that behaves as a table, even the parameters may 
be passed as predicates in the WHERE clause)? 

If the subject is "defining regular functions directly in SQL" then CREATE 
FUNTION may be considered. This seems easier to understand and develop. but in 
the end it can have an enormous impact, like in postgres  
sql-createfunction.html 
 

For the sudoku-solver you do use the table-valued function notation:

SELECT s FROM x(sud) WHERE NOT ind;

and the function should possibly return multiple rows here. Like if a sudoku is 
ambiguous or if changing the WHERE clause for development.

The idea would help me. I wonder if it can also be applied in inline views. 
That could be imagined if PARAMETERS is written after the query like was an 
earlier idea.

SELECT name, nolder
FROM people
LEFT JOIN  (--inline view
  SELECT COUNT(*) 
  FROM people p2 
  WHERE age>parameter.age
  PARAMETERS (age)
) USING (age)
;

E. Pasma 

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

[E.Pasma]

ehaviour is also intrinsic to user-defined functions, unless extremely 
carefully programmed. The outcomes above are equivalent to what generate_series 
does with these sort of predicates. 
Not sure how bad this is when achievable in plain SQL.

I leave out the remainder of the message and give some personal motivation 
instead. I want to make a view of the sudoku solver

https://www.sqlite.org/lang_with.html#sudoku

That becomes like below. I have in mind to combine it with a further view to 
format the input or output.

Thanks, E.Pasma
  
CREATE VIEW sudsol(sud,sol) AS 
WITH RECURSIVE
  digits(z, lp) AS (
VALUES('1', 1)
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  ),
  x(s, ind) AS (
SELECT PARAMETERS.sud, instr(PARAMETERS.sud, '.')
UNION ALL
SELECT
  substr(s, 1, ind-1) || z || substr(s, ind+1),
  instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
 FROM x, digits AS z
WHERE ind>0
  AND NOT EXISTS (
SELECT 1
  FROM digits AS lp
 WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
OR z.z = substr(s, (((ind-1)/3) % 3) * 3
+ ((ind-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6, 1)
 )
  )
SELECT PARAMETERS.sud, s FROM x WHERE ind=0;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database file with a nonstandard sqlite_seuence schema causes crash.

[E.Pasma]

This crash (bus error) also occurs when the experiment is done the  
other way around. A standard sqlite3_sequence table is then renamed  
and no longer exists as such. And it will not reappear..
It is generous that a pragma writable_schema lets us do these  
experiments.



$ rm test.db; sqlite3 test.db< test1.sql
CREATE TABLE ttt(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
INSERT INTO ttt(data) VALUES('xx');
PRAGMA writable_schema=1;
UPDATE sqlite_master
SET name='x_sequence',
tbl_name='x_sequence',
sql=REPLACE(sql, 'sqlite_sequence', 'x_sequence')
WHERE name= 'sqlite_sequence';
$
$ sqlite3 test.db< test2.sql
INSERT INTO ttt(data) VALUES('yy');
Bus error
$

Abroży Nieprzełoży wrote:


I'm not working on anything specific, just experimenting, so you don't
have to rush :)


2018-05-20 21:53 GMT+02:00, Richard Hipp:

Thank you for the bug report.

I agree that this is a problem that needs to be fixed, and it will be
fixed before the next release.  However, the problem arises in a part
of the code where we must move cautiously to avoid a performance
regression. Further, your specific problem suggests an entire new
class of problems that need to be investigated and carefully tested.
So fixing this will take some time.

The bug has been in the code since version 3.8.7 (2014-10-17).  I
think you can wait a few days for the fix.



On 5/19/18, Abroży Nieprzełoży  
 wrote:

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> .version
SQLite 3.24.0 2018-05-18 17:58:33
c6071ac99cfa4b6272ac4d739fc61a85acb544f6c1c2ae67b31e92aadcc995bd
zlib version 1.2.11
msvc-1912
sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq
INTEGER) WITHOUT ROWID;
sqlite> INSERT INTO xqlite_sequence VALUES('ttt', 1);
sqlite> PRAGMA writable_schema=1;
sqlite> UPDATE sqlite_master SET name='sqlite_sequence',
tbl_name='sqlite_sequence', sql='CREATE TABLE sqlite_sequence(name
TEXT PRIMARY KEY, seq INTEGER) WITHOUT ROWID' WHERE
name='xqlite_sequence';
sqlite> .exit

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> CREATE TABLE ttt(id INTEGER PRIMARY KEY AUTOINCREMENT,  
data TEXT);

sqlite> .exit

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> INSERT INTO ttt(data) VALUES('xx');




--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[E.Pasma]

Cezary H. Noweta wrote:


CREATE TABLE points AS WITH cte(x,y,n) AS (SELECT (random() % 10 +  
10) % 10 + 1, (random() % 10 + 10) % 10 + 1, 1 UNION ALL SELECT  
(random() % 10 + 10) % 10 + 1, (random() % 10 + 10) % 10 + 1, n + 1  
FROM cte WHERE n < 100) SELECT x, y FROM cte;



and


CREATE TABLE xaxis AS SELECT x, COUNT(x) AS n FROM points GROUP BY x;
CREATE TABLE yaxis AS SELECT y, COUNT(y) AS n FROM points GROUP BY y;

For example for two points (1,2); (1,-4)

xaxis(x, n):
1 2

yaxis(y, n):
-4 1
2 1

xaxis <== points(x, y) ==> yaxis:
 1 -4
 1  2

``points(x, y)'' could have (CASCADING) FOREIGN KEY x=>xaxis.x and  
y=>yaxis.y and DELETE TRIGGERs which could adjust counters in  
``*axis'' tables (DELETEing FROM xaxis WHERE x==OLD.x AND n==0 and  
FROM yaxis WHERE y==OLD.y AND n==0; alternatively ``*axis'' could  
have UPDATE TRIGGERs which could DELETE FROM points when ``n''  
column had achieved 0 or less then nX/nY -- triggers must be  
supressed while creating a ``*axis'' table in the latter case).


Then ``iteratively'' (not ``recusively'') DELETE from ``*axis''  
WHERE n < threshold until there are no too small Xs/Ys or tables are  
empty.


(1)
DELETE FROM xaxis WHERE n < nX;
DELETE FROM yaxis WHERE n < nY;

(2)
which fire CASCADE DELETE of relations FROM points

(3)
which fires DELETE TRIGGERs of points

(4)
which adjust referenced xaxis.n and y.axis.n

(4a)
and further DELETE FROM *axis WHERE n == 0

(4b)
and further DELETE FROM *axis WHERE n < nX/nY

(4c)
and further fire UPDATE TRIGGERs of *axis, which in turn further do  
(4a) or (4b).


Cezary, how about this solution with a trigger only on xaxis and on  
yaxis?

AFTER DELETE FROM xaxis:
- update yaxis set n=n-1 where y in (select y from points where x=old.x)
- delete points where x=old.x
- delete yaxis where nNow, with pragma recursive_triggers this might do the whole job if the  
initial deletes(1) are executed. There is a limit on the recursion (/ 
iteration) depth. And this solution requires at most nx+ny iterations,  
where nx is the number of xaxis rows and ny the number of yaxis rows,


On the other hand, if this is not a constest in purely sql  
programming, why not have a programme loop with a single delete, and  
no derived *axis tables. For instance using tclsh:



while 1 {
db eval {
DELETE FROM points
WHERE x IN (
SELECT x FROM points GROUP BY x HAVING COUNT(*)<(SELECT nx FROM  
params)

)
OR y IN (
SELECT y FROM points GROUP BY y HAVING COUNT(*)<(SELECT ny FROM  
params)

)
;
}
if {![db changes]} break
} ;# end loop


E.Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[E.Pasma]

Cezary H. Noweta wrote:


At the beginning I would like to agree with that the problem is  
iterative rather then recursive one. However

R. Smith wrote:


LOL, that might be the hackiest query I ever seen, but kudos mate,  
that's bloody marvellous!


Cezary, thanks for the diverting solution. I've been looking into  
solving sudokus along the same lines. A function GROUP_SPLIT as an  
inverse of GROUP_CONCAT would be handy here. Below is the query as it  
looks when there was such a function, or actually a (function like)  
virtual table with column elem. It is exactly half the size.  
Nevertheless the fun is to achieve the goal purely in SQL.

Thanks, E Pasma.

CREATE TABLE points AS WITH cte(x,y,n) AS (SELECT (random() % 10 + 10)  
% 10 + 1, (random() % 10 + 10) % 10 + 1, 1 UNION ALL SELECT (random()  
% 10 + 10) % 10 + 1, (random() % 10 + 10) % 10 + 1, n + 1 FROM cte  
WHERE n < 100) SELECT x, y FROM cte;


WITH
  params(nx, ny) AS (SELECT 6, 8),
  main(elem, rest) AS (
SELECT NULL, (
  WITH
state(clock, points, xaxis, yaxis, nxmin, nxmax, nymin,  
nymax) AS (

  SELECT
0,
(SELECT GROUP_CONCAT(x || ' ' || y) FROM points),
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM  
points GROUP BY x)), (SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT  
y, COUNT(y) n FROM points GROUP BY y)), (SELECT MIN(n) FROM (SELECT x,  
COUNT(x) n FROM points GROUP BY x)), (SELECT MAX(n) FROM (SELECT x,  
COUNT(x) n FROM points GROUP BY x)), (SELECT MIN(n) FROM (SELECT y,  
COUNT(y) n FROM points GROUP BY y)), (SELECT MAX(n) FROM (SELECT y,  
COUNT(y) n FROM points GROUP BY y))

  UNION ALL
  SELECT
(clock + 1) % 3,
CASE clock WHEN 0 THEN
(SELECT GROUP_CONCAT(x || ' ' || y) FROM (
  SELECT
CAST(elem AS INTEGER) x,
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
  FROM GROUP_SPLIT (state.points)
)
WHERE (x NOT IN (
  SELECT x FROM (
SELECT
  CAST(elem AS INTEGER) x,
  CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
  FROM GROUP_SPLIT (state.xaxis)
  ) WHERE n < (SELECT nx FROM params)
)) AND (y NOT IN (
  SELECT y FROM (
SELECT
  CAST(elem AS INTEGER) y,
  CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
  FROM GROUP_SPLIT (state.yaxis)
  ) WHERE n < (SELECT ny FROM params)
))) ELSE points END,
CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM (
  SELECT
CAST(elem AS INTEGER) x
  FROM GROUP_SPLIT (state.points)
) GROUP BY x)) ELSE xaxis END,
CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT y, COUNT(y) n FROM (
  SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
  FROM GROUP_SPLIT (state.points)
) GROUP BY y)) ELSE yaxis END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
  SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
  FROM GROUP_SPLIT (state.xaxis)
)) ELSE nxmin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
  SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
  FROM GROUP_SPLIT (state.xaxis)
)) ELSE nxmax END,
CASE clock WHEN 2 THEN
(SELECT MIN(n) FROM (
  SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
  FROM GROUP_SPLIT (state.yaxis)
)) ELSE nymin END,
CASE clock WHEN 2 THEN
(SELECT MAX(n) FROM (
  SELECT
CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
  FROM GROUP_SPLIT (state.yaxis)
)) ELSE nymax END
  FROM state
  WHERE (SELECT nx FROM params) BETWEEN nxmin + 1 AND nxmax OR
(SELECT ny FROM params) BETWEEN nymin + 1 AND nymax
) SELECT points FROM state WHERE nxmin >= (SELECT nx FROM params) AND  
nymin >= (SELECT ny FROM params)

)
UNION ALL
SELECT
SUBSTR(rest, 1, IFNULL(NULLIF(INSTR(rest, ','), 0) - 1, LENGTH(rest))),
  SUBSTR(rest, NULLIF(INSTR(rest, ','), 0) + 1)
FROM main
WHERE rest IS NOT NULL
  )
SELECT
  CAST(elem AS INTEGER) x,
  CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
  FROM main WHERE elem IS NOT NULL
;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI thoughts

[E.Pasma]

Martin wrote:
...
 Example (maybe via .read):
   .once .dat
   select date('now');
   .let f system echo "words-`cat .dat`.txt"
   .once -let f
   select word from words order by 1;
..

Sorry for replying only to this single point. The proposed ".let"  
command is not essential here. As a student I was impressed by the  
SNOBOL programming language because it allowed "dynamic code  
generation" (writing before the foot). From the SQLite command line  
this works too:


 .once tmp1.sql
values ('.once words.' || date() || '.dat'),
   ('select * from words;');
sqlite> .read tmp1.sql


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] An artificial query whose outcome is changed after left join reduction

[E.Pasma]

Hello, the nature of this case is purely artificial and I thought it  
is worth considering in the light of real world problem as reported by  
Raphael Michel. E Pasma


.version
SQLite 3.23.0 2018-03-24 13:24:02  
cf171abe954a5f25262161dd69f2e8cecdbf9446c3f6b298201507dbc743567e

zlib version 1.2.3
gcc-4.0.1 (Apple Inc. build 5465)
create table t1(a integer primary key);
create table t2(a integer primary key, b);
insert into t1 values(1),(2),(3);
insert into t2 values(1,10),(2,2);
select a, b
from t1
left join t2 using (a)
where a in (b,3)
;
2|2

In version 3.22:
2|2
3|



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generate_series can theoretically behave differently in SQLite 3.23.0

[E.Pasma]

Petern wrote:
I think your left join reduction regression change happens on any  
vtable
hidden column filter reference to an outer scope column.  A CTE  
duplicates

your finding below...
Hello Peter, from your message I realize that generate_series is no  
longer essentiall since SQLite offers CTE's. The query below uses no  
extension (and is not relevant for the issue). Hope I did not miss  
anything. E. Pasma


WITH
t1(x) AS (VALUES(1),(2)),
t2(x,y) AS (VALUES(1,4)),
rec_serie AS (
SELECT x, y AS value
FROM t1 JOIN t2 USING (x)
UNION ALL
SELECT x, value+1
FROMrec_serie
WHERE   value<5
ORDER BY x
)
SELECT * FROM rec_serie
;











___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generate_series can theoretically behave differently in SQLite 3.23.0

[E.Pasma]

Richard Hipp wrote:
...


I'm testing a patch now that causes the LEFT JOIN strength reduction
optimization to assume that NULL arguments to a virtual table
constraint can return a TRUE result.  But I'm wondering, since this is
really a work-around to problems in virtual table implementations, if
this change warrants a patch release?

Your thoughts?

Should we issue 3.23.1 just to work around dodgy virtual table
implementations?  Or should we just check-in the change and let those
who want to continue using their dodgy virtual tables either patch the
issue themselves or wait for 3.24.0?


3.24




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] non-returned column aliases for repeating expressions?

[E.Pasma]

24 mrt 2018, Wout Mertens:
...

SELECT "id" AS _1,"json" AS _2 FROM "testing"
WHERE json_extract(json, '$.foo') < 50
ORDER BY json_extract(json, '$.foo') DESC,"id"

...

SELECT _1, _2 FROM (
SELECT "id" AS _1,"json" AS _2, json_extract(json, '$.foo') AS _3 FROM
"testing"
WHERE _3 < 50
ORDER BY _3 DESC,"id")

...

24 mrt 2018, Don V Nielsen
...

WITH data AS (
SELECT
  "id"AS _1,
  "json"  AS _2,
  json_extract(json, '$.foo') AS _3
FROM "testing"
)
SELECT *
FROM data
where _3 < 50
ORDER BY _3 DESC, _1

...
Assuming an expression index on data (json_extract(json, '$.foo')),
above queries have identical execution plans.
Moreover, in each case the result of the expression is obtained  
exclusively form the index. Nowhere it is reevaluated, I confirmed  
that using a test function.
Even when the result of the expression is included in the selection,  
like in the last query, it is obtained from the index as used for the  
WHERE or ORDER BY part. That is actually quite good of the optimizer!

The queries must be equally fast.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[E.Pasma]

Hello Adrián, as you say

 (I wonder whether the performance is very different from what one  
gets by manually inserting the WHERE clause in the base case of the  
recursive CTE.)


I wonder too. Still the trick is meant to make a view (without  
manually inserted predicates inside)


Thanks for the reply. E. Pasma

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

[E.Pasma]

Adrián Medraño Calvo wrote:
The following SQL script shows a query selecting data from a  
recursive
CTE and filtering it.  I expected the optimizer to apply the filter  
to

the recursive CTE directly, and indeed the documentation of
pushDownWhereTerms (src/select.c:3833) indicates this possibility  
when

various conditions are satisfied.



Clemens Ladisch wrote:


Rule 22 of  forbids
subquery flattening in this case.  I suspect pushDownWhereTerms() is  
not

called at all.



Hello, "push down where terms" into a complex view can sometimes be  
achieved by correlation. The view/CTE must then be wrapped in a new  
query that is joinable via indexes. Your example is just perfect to  
show the trick. E. Pasma.



.eqp on
WITH eqgrseq(initial, next) AS (
SELECT push.v, pull.v
FROM   t push, t pull
WHERE  pull.v IN (
WITH RECURSIVE r AS (
SELECT push.v
UNION ALL
SELECT t.v
FROM   r
JOIN   t
ON t.v = r.v + 1)
SELECT v FROM r))
SELECT initial, next
FROM   eqgrseq
WHERE  initial = 1; --:initial;

Output:
--EQP-- 0,0,0,SEARCH TABLE t AS push USING COVERING INDEX  
sqlite_autoindex_t_1 (v=?)
--EQP-- 0,1,1,SEARCH TABLE t AS pull USING COVERING INDEX  
sqlite_autoindex_t_1 (v=?)

--EQP-- 0,0,0,EXECUTE CORRELATED LIST SUBQUERY 1
--EQP-- 4,0,0,SCAN TABLE r
--EQP-- 4,1,1,SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1  
(v=?)

--EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)
--EQP-- 1,0,0,SCAN SUBQUERY 2
1|1
1|2
1|3
1|4
1|5


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] missing subquery flattening

[E.Pasma]

Mark Brand wrote:




On 26/01/18 19:35, Clemens Ladisch wrote:

Mark Brand wrote:

Shouldn't we expect subquery flattening to happen in V2 below?

-- no flattening
CREATE VIEW V2 AS
SELECT * FROM X
LEFT JOIN (
SELECT * FROM X
LEFT JOIN Y ON Y.a = X.a
) Z
ON Z.a = X.a;

-- manually flattened version of V2
CREATE VIEW V2_FLATTENED AS
SELECT *
FROM X
LEFT JOIN X X2
   ON X2.a = X.a
LEFT JOIN Y
   ON Y.a = X2.a;
In the general case, left outer joins are not associative.   
Apparently,

SQLite does not try to prove the opposite for special cases.



The simplified case above does not make obvious the usefulness of  
flattening. A real world situation is where the right operand of a  
LEFT JOIN is a handy reusable view with a LEFT JOIN whose left side  
provides a useful index. For example:


  -- handy reusable view

CREATE VIEW W AS
 SELECT X.*, X.cost + IFNULL(Y.fee, 0) price
 FROM X
 LEFT JOIN Y
 ON Y.c = X.c;

   SELECT *
   FROM X
   LEFT JOIN W -- based on X
 ON W.a = X.a
   WHERE X.color = 'red';

W, by itself, might produce many more rows than the outer query and  
be expensive. In cases like this, it's critical for performance that  
the index of W be used for the LEFT JOIN in the outer query.


Without flattening, we have to go to some trouble to avoid using  
otherwise handy logic-encapsulating views on the right side of a  
LEFT JOIN. I've only recently realized this.


Would it make sense for sqlite to flatten this pattern? As far as I  
can see, it satisfies all the conditions listed in http://www.sqlite.org/optoverview.html#flattening 
.


Mark



Hello, may be I'm wrong or the documentation is wrong:
If the subquery is not the right operand of a LEFT JOIN then
the subquery may not be a join, and
the FROM clause of the subquery may not contain a virtual table, and
the outer query may not be an aggregate.

The "not" in the first line does not belong there?
Thanks.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.22.0 coming soon

[E.Pasma]

Dear SQLite developers and eventual PowerPC users,
using a likely outdated compiler:
powerpc-apple-darwin9-gcc-4.0.1
a compile error occors:
shell.c:10062: error: conflicting types for ‘integerValue’
	shell.c:9169: error: previous implicit declaration of ‘integerValue’  
was here
it is still to overcome by an explicit declaration of IntegerValue  
before expertDotCommand.
This must be be the only place where this order of reference and  
definition occurs. Is that alright?

Thanks, E. Pasma

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[E.Pasma]

op 05-01-2018 17:23 schreef David Raymond op david.raym...@tomtom.com:

>> Anyway the two queries return the same set of rows.
> 
>> This test also show a small semantic difference in the two queries.
>> The set of rows is the same but the second query leaves certain
>> details null if only one of the OR conditions is true. That occurs in
>> row 1.
> 
> You're contradicting yourself there. If there's a difference in the results
> then they're not the same set of rows.
> 
> We'll just look at the "all 1" case. a has (1, 1, 1), b has (1, 1) and c has
> (1, 1)
> 
> Best if viewed in a fixed-width font
> 
> Step by step version 1:
> 
> a left join b on b = ab
> 
> a b
> a ab ac   b  d
> (1, 1, 1) (1, 1)
> 
> result:
> a ab ac  b  d
> (1, 1, 1, 1, 1)
> 
> left join c on c = ab
> 
> c
> a ab ac  b  d   c  d
> (1, 1, 1, 1, 1) (1, 1)
> 
> result:
> a ab ac  b  d  c  d
> (1, 1, 1, 1, 1, 1, 1)
> 
> where b.d in (1, 2, 3) or c.d in (4, 5)
> 
> b.d is 1, so it passes
> result:
> 
> a ab ac  b  d  c  d
> (1, 1, 1, 1, 1, 1, 1)
> 
> The OR worked. We got the values from both tables b, and c and because one of
> them was correct.
> 
> 
> 
> Now, Step by step version 2:
> 
> a left join b on b = ab and b.d in (1, 2, 3)
> 
> a b
> a ab ac   b  d
> (1, 1, 1) (1, 1)
> 
> result:
> a ab ac  b  d
> (1, 1, 1, 1, 1)  same so far (but only by coincidence)
> 
> left join c on c = ac and c.d in (4, 5)
> 
> c
> a ab ac  b  d   c  d
> (1, 1, 1, 1, 1) (1, 1)
> 
> the ON condition doesn't match. Since this is an outer join, and there were no
> matches for the row in the left side, nulls are included
> 
> result:
> a ab ac  b  dc d
> (1, 1, 1, 1, 1,  null, null)
> 
> where b.d is not null or c.d is not null
> 
> b.d isn't null, so that passes.
> result:
> 
> a ab ac  b  dc d
> (1, 1, 1, 1, 1,  null, null)
> 
> So we get a row saying that a matched something in b, but we're throwing out
> the value from the c table, which is not what we wanted.

Thanks, all clear except this last line. Did we not want the value to be
thrown out. Or not want the value?
It depends on that which query is favourite.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[E.Pasma]

Dinu wrote:


Hi all,
I've ran into an optimisation problem with a double-left join that  
works as

an "either" clause.

The query is as follows:

SELECT *
FROM
 a
LEFT JOIN
 b ON 
LEFT JOIN
 c ON 
WHERE
 b.someId IN (1,2,3) OR
 c.someId IN (4,5)

This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX

However, the semantically equivalent:
SELECT *
FROM
 a
LEFT JOIN
 b ON  AND b.someId IN (1,2,3)
LEFT JOIN
 c ON AND c.someId IN (4,5)
WHERE
 b.someId IS NOT NULL OR
 c.someId IS NOT NULL

Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY




Hello, the discussion about whether the two queries are equivalent is  
not satisfactory to me. What Keith sais


Your "where" condition is effectively converted an OUTER JOIN into  
an INNER JOIN ..


is true. But the OR condition makes this true for either the one or  
the other outer join. I hope this is what Dinu means here:


And the two queries are algebrically identical. "(X ∊ S or X:=null)  
AND (X is not null)" is
equivalent to "X ∊ S is not null". The two queries might look  
different only

from an imperative programming point of view.


Anyway the two queries return the same set of rows.

Furthermore: what is a "bloated" execution plan?
I set up some test data and the query deamed bloated appears just as  
fast. See below.


This test also show a small semantic difference in the two queries.  
The set of rows is the same but the second query leaves certain  
details null if only one of the OR conditions is true. That occurs in  
row 1.

The outcome of the "bloated" execution plan is more complete.

Possibly I am too pragmatical and don't understand the discussion.

E. Pasma


My test script:

create table a (a integer primary key, ab, ac);
create table b (b integer primary key, d);
create table c (c integer primary key, d);
insert into a values (null,1,1);
insert into a select null, 2,2 from a;
insert into a select null, 3,2 from a;
insert into a select null, 4,4 from a;
insert into a select null, 5,5 from a;
insert into a select null, 6,6 from a;
insert into a select null, 7,7 from a;
insert into a select null, 8,8 from a;
insert into a select null, 9,9 from a;
insert into a select null, 10,10 from a;

insert into b values (1,1),(2,2),(3,3),(4,3),(6,3);
insert into c values (1,1),(4,5),(5,5),(7,1);
.eqp on
.timer on
SELECT *
FROM
 a
LEFT JOIN
 b ON b=ab
LEFT JOIN
 c ON c=ac
WHERE
 b.d IN (1,2,3) OR
 c.d IN (4,5)
;

SELECT *
FROM
 a
LEFT JOIN
 b ON b=ab AND b.d IN (1,2,3)
LEFT JOIN
 c ON c=ac AND c.d IN (4,5)
WHERE
 b.d IS NOT NULL OR
 c.d IS NOT NULL
;

Output:

--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,1,1,SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,2,2,SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 1
1|1|1|1|1|1|1
2|2|2|2|2||
3|3|2|3|3||
4|3|2|3|3||
5|4|4|4|3|4|5
6|4|4|4|3|4|5
7|4|4|4|3|4|5
8|4|4|4|3|4|5
9|5|5|||5|5
10|5|5|||5|5
11|5|5|||5|5
12|5|5|||5|5
13|5|5|||5|5
14|5|5|||5|5
15|5|5|||5|5
16|5|5|||5|5
17|6|6|6|3||
18|6|6|6|3||
19|6|6|6|3||
20|6|6|6|3||
21|6|6|6|3||
22|6|6|6|3||
23|6|6|6|3||
24|6|6|6|3||
25|6|6|6|3||
26|6|6|6|3||
27|6|6|6|3||
28|6|6|6|3||
29|6|6|6|3||
30|6|6|6|3||
31|6|6|6|3||
32|6|6|6|3||
Run Time: real 0.003 user 0.001587 sys 0.000358
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,1,1,SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 1
--EQP-- 0,2,2,SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
1|1|1|1|1||
2|2|2|2|2||
3|3|2|3|3||
4|3|2|3|3||
5|4|4|4|3|4|5
6|4|4|4|3|4|5
7|4|4|4|3|4|5
8|4|4|4|3|4|5
9|5|5|||5|5
10|5|5|||5|5
11|5|5|||5|5
12|5|5|||5|5
13|5|5|||5|5
14|5|5|||5|5
15|5|5|||5|5
16|5|5|||5|5
17|6|6|6|3||
18|6|6|6|3||
19|6|6|6|3||
20|6|6|6|3||
21|6|6|6|3||
22|6|6|6|3||
23|6|6|6|3||
24|6|6|6|3||
25|6|6|6|3||
26|6|6|6|3||
27|6|6|6|3||
28|6|6|6|3||
29|6|6|6|3||
30|6|6|6|3||
31|6|6|6|3||
32|6|6|6|3||
Run Time: real 0.002 user 0.001560 sys 0.000296








___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

[E.Pasma]

Clemens Ladisch wrote:


Luuk wrote:

On 01-01-18 03:14, Shane Dev wrote:
select * from nodes where not exists (select * from edges where  
child=nodes.id);


Changing this to:

select * from nodes where not exists (select 1 from edges where  
child=nodes.id);


saved in my test about 10% of time


Then I have to doubt your test; the generated code (see the EXPLAIN
output) is exactly the same.



Yes, the execution plans are the same. Still the second is faster!

Another query that uses EXCEPT instead of NOT IN and that has yet  
another execution plan:


select id from nodes except select child from edges;
 

___

sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

[E.Pasma]

Shane Dev wrote:


Hi Clemens,

Your query is much faster on my system - thanks!

Apart from visual inspection and testing, is there anyway to be sure  
your

query selects the same results as my query?

From https://sqlite.org/queryplanner.html "When programming in SQL  
you tell
the system what you want to compute, not how to compute it". Is this  
an
exception to the rule where the query planner must be told how to  
compute

the result?


select * from nodes where not exists (select * from edges where  
child=nodes.id);


select * from nodes where not exists (select 1 from edges where  
child=nodes.id);


 select * from nodes where id not in (select child from edges);



The first two queries look more 'procedural' than the last. So this  
may confirm "When programming in SQL you tell the system what you want  
to compute, not how to compute it"


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to prevent the insertion of cycles into a hierarchical table?

[E.Pasma]

On 24/12/2017 11:56, Shane Dev wrote:


Related to my previous question
https://www.mail-archive.com/sqlite-users@mailinglists.sqlit
e.org/msg107527.html,
I want to prevent the client from inserting a cycle.

For example -

sqlite> .sch edges
CREATE TABLE edges(parent integer not null, child integer not null,
constraint self_reference check (parent<>child));

sqlite> select * from edges;
parent  child
1   2
1   3
2   4

insert into edges select 2, 5; -- ok
insert into edges select 2, 1; -- should not be allowed.
insert into edges select 4, 1; -- should not be allowed.

Many kinds of insertions can be prevented using triggers. Existing  
cycles
can be detected using a recurisve common table expression. However,  
since
CTEs are not supported inside triggers, I assume they can't be used  
for

this purpose. Is there another way?




Sorry for ignoring the two earlier repiies, but it looks that WITH can  
be used inside triggers. Like


create trigger ins_edges before insert on edges
begin
with recursive r as (
select  new.child
union all
select  edges.child
fromr
joinedges on edges.parent=r.child
)
select raise (FAIL, 'example error')
fromr where child=new.parent;
end
;



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to detect cycles in a hierarchical table?

[E.Pasma]

Lifepillar wrote:


On 20/12/2017 22:31, Shane Dev wrote:

Hello,
I have an edges table -
sqlite> .sch edges
CREATE TABLE edges(parent, child);
sqlite> select * from edges;
parent  child
1   2
1   3
2   4
3   1
4   5
5   2
Here we have two cycles -
1) 1 => 3 => 1 (length 1)
2) 2 => 4 => 5 => 2 (length 3)
Cycles cause recursive common table expression queries to become  
infinite

loops.

Maybe you could show an example of such queries? This:

 with recursive Visit(node) as (
   select parent from Edges where parent = 1
   union
   select child from Edges join Visit on parent = node
 )
 select node from Visit;

returns a finite result (note that use of 'union' rather than 'union
all').

Life.


Brilliant. Now I see the difference between UNION and UNION ALL in  
recursion. It is documented as below. Although it needs careful  
reading to understand that UNION effectively eliminates loops.


https://www.sqlite.org/lang_with.html#recursivecte
If a UNION operator connects the initial-select with the recursive- 
select, then only add rows to the queue if no identical row has been  
previously added to the queue. Repeated rows are discarded before  
being added to the queue even if the repeated rows have already been  
extracted from the queue by the recursion step. If the operator is  
UNION ALL, then all rows generated by both the initial-select and the  
recursive-select are always added to the queue even if they are  
repeats. When determining if a row is repeated, NULL values compare  
equal to one another and not equal to any other value.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[E.Pasma]

Op 27 nov 2017, om 20:51 heeft x het volgende geschreven:


So if I build a view that includes look-ups in other tables, the
optimizer may skip these at places where not selected. However only  
if

the look-ups are written as outer joins. Then it may be good practice
allways doing that. For instance:



create view vtrack as
select  trackname, artistname
from track
left join artist ON trackartist=artistid -- note left join

;
~
~

Yeah, I was thinking always to use left joins when it doesn’t make  
any difference. I’m using a C++ wrapper for SQLite I wrote myself.  
It contains a QueryGrid type that automatically splits the SQL into  
RowSQL and ColSQL as described earlier. It is still possible to  
build a vector of BaseTbl.RowIDs when the original query contains  
inner joins provided all columns of the unique index used in the  
join are satisfied (see ***Example). If it’s unable to do that (due  
to 1 to many joins)  it computes ‘select count(*) from .’ and  
sets ColSQL to ‘select ColList from ... limit ?1, ?2’ so that it  
still fits in with the ‘fetch a range of data’ modus operandi and  
also allows a record counter and vertical scrollbar positioning.


***Example

select ColList
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

Provided Tbl_2 has unique index on Y the RowID list could be had from

Select BaseTbl.RowID
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

which the SQLite query optimiser would presumably reduce to

Select BaseTbl.RowID
from BaseTbl
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?

I’ve still to write code to check the query contains no joins that  
are ‘1 to many’. I don’t suppose there’s an easy way of determining  
this from explain or explain query plan? At the moment all i can  
think of is to scrape the index name from the explain query plan  
Detail column, check the number of variables involved (i.e. number  
of ? marks) and compare with the number of columns in the index.



What about changing the remaining inner join to left join

Select BaseTbl.RowID
from BaseTbl
left join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?

and see if the SQLiter optimizer now leaves Tbl_2 out from the query  
plan.  It will only do that if it is not a 1-to-n join.
But that leaves you with the change that the join is 1-to-0 so to say.  
I understood that was a show stopper.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[E.Pasma]

So if I build a view that includes look-ups in other tables, the  
optimizer may skip these at places where not selected. However only if  
the look-ups are written as outer joins. Then it may be good practice  
allways doing that. For instance:


create view vtrack as
select  trackname, artistname
from track
left join artist ON trackartist=artistid -- note left join
;
~
~

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[E.Pasma]

x wrote:


From: E.Pasma<mailto:pasm...@concepts.nl>
Sent: 26 November 2017 17:30
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Getting an advance list of RowIDs for a query  
result set



If step 3 is xxx-ed and only left-joins remain to be considered then
the SQLite3 engine is likely to fullfill  this optimization in its
next release.
I tested this on the current  (2017-11-17) pre-release snapshot. As
far as I see any outer joined table may be discarded from the query
plan if only rowid from the base table is selected. Using SELECT
DISTINCT applies this also to one-to-many joins.


Thanks E. Pasma. Fixing the left join optimisation will be enough  
for me and will allow me to delete a fair amount of difficult code.  
My confusion over the inner joins arose out of trying to find a way  
of ensuring the BaseTbl RowIDs wouldn’t contain any duplicates.  
Somewhere along the line I forgot SQLite would have to still check  
the record existed in the inner joined table.



Thanks to you, this topic has inspired a useful change or changes.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[E.Pasma]

x wrote:


I proceed as follows


 1.  Omit a table join from the SQL and try preparing it.
 2.  If it prepares OK then the table isn’t involved in the WHERE or  
ORDER BY.
 3.  If it’s joined to the BaseTbl by an integer primary key or  
FULLY joined by a unique index then the table is redundant. By FULLY  
I mean ALL fields of the index are included in the join as otherwise  
the BaseTbl’s relationship with it is of a ONE TO MANY nature which  
means the BaseTbl RowID won’t uniquely identify a row of the  
original query. [Two things I’m unsure about are a) how nulls affect  
unique index  joins and b) how to deal with tables that aren’t  
directly linked to the BaseTbl (i.e. they’re linked via an  
intermediate table)].

 4.  If the table is needed reintroduce it into the SQL.

Do this in turn for each of the joins.

If step 3 is xxx-ed and only left-joins remain to be considered then  
the SQLite3 engine is likely to fullfill  this optimization in its  
next release.
I tested this on the current  (2017-11-17) pre-release snapshot. As  
far as I see any outer joined table may be discarded from the query  
plan if only rowid from the base table is selected. Using SELECT  
DISTINCT applies this also to one-to-many joins.



I’m wondering if the second of those trunk changes is in any way  
related to what I’m trying to do. The above is a bit long winded and  
not easy to code so it would be great if the SQLite query optimizer  
did it all for me.



It looks relevant, but I did not test that.

To me the time taken to grab a grid page of data is negligible if  
you know where to look for it on disc. I tend therefore to time  
queries by how fast I can get all the BaseTbl RowIDs into a vector.  
The biggest table in my database has 2.4 million rows and yet wait  
cursors are a very rare sight.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View is not flattened when inside an IN sub-query

[E.Pasma]

Thanks very much for finding this worth a change. I found that in the  
SQLite3 timeline and I tested the change. As written in an other topic:


.. Just sit tight and again wait and see if Dr Hipp agrees the  
behavior should change or not.


It is comforting that this is even true for an unanswered and  
undiscussed message.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] View is not flattened when inside an IN sub-query

[E.Pasma]

Hello, below are two equivalent delete statements. The difference is  
that the second version uses views, actually sub-queries, on the base  
tables. These are simple one to one views that could be flattened out,  
as in

http://www.sqlite.org/optoverview.html#flattening
The second query plan has a full scan on t2 instead of direct access  
via the existing index sqlite_autoindex_t2_1. The view is apparently  
not flattened. Is it possible changing this? Thanks, E. Pasma



.version
SQLite 3.21.0 2017-10-02 02:52:54  
c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a

create table t1 (a, b, primary key(a,b));
create table t2 (b, c, primary key(b,c));
create table t3 (a, c, primary key(a,c));

.eqp on
delete from t3 where (a,c) in (
select  a,c
fromt1
joint2 using(b)
);
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?  
AND c=?)

--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 0,0,0,SCAN TABLE t1
--EQP-- 0,1,1,SEARCH TABLE t2 USING COVERING INDEX  
sqlite_autoindex_t2_1 (b=?)
"issue2.txt" 35L, 1393C
1,1   Top


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Article about using sqlite3 in Python

[E.Pasma]

22 okt 2017, 18:47, Simon Slavin:

I don’t know enough about Python to evaluate this, but the sqlite3  
side is sound, and some readers might find it useful.




Simon.


It is written very well. However for readers with an SQLite background  
someting must still be added. The built-in sqlite3 module begins a  
transaction behind the scenes as soon as a DML statement is executed.  
And expects a commit to be programmed or a connection to be explicitly  
closed. Or a WITH construction. From an SQLite point of view one is  
better of with the Other Sqlite Wrapper (apsw). 
___

sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon... _rl_completion_matches undefined on old iMac

[E.Pasma]

Richard Hipp wrote:


E.Pasma wrote:

Is there any otrher choice except  ./configure --disable-readline.


Other options:

(1) You can upgrade the readline library on your PPC to something more
recent that supports tab completion.

(2) You can compile the shell using linenoise instead of readline.
(Doing so will require manual changes to the Makefile as it does not
appear to be supported by ./configure at this time - something that we
should fix for the next release.)

I still power up a PPC mac from time to time in order to verify that
SQLite works on big-endian platforms.  But it is painfully slow.  Why
are you still using a PPC mac?


Thanks for pointing this out and I managed to use linenoise from 
https://github.com/antirez/linenoise
I was pleased to find that the use of the linenoise library is already  
anticipated in shell.c (HAVE_LINENOISE)

Indeed only Makefile need to be changed manually aftter .configure.
The difference in size of the library source code is impressing:

$ du -hs readline-7.0/
 11Mreadline-7.0/
$ du -hs linenoise/
144Klinenoise/

Point about new PC taken. Even download from github seems not to work  
on a PPC mac.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon... _rl_completion_matches undefined on old iMac

[E.Pasma]

Hello, when building the pre-release snapshot an error occurs. It is  
likely due to my outdated Mac OS version, 10.5.8.


shell.c: In function ‘readline_completion’:
shell.c:4286: warning: return makes pointer from integer without a cast
...
Undefined symbols:
  "_rl_completion_matches", referenced from:
  _readline_completion in sqlite3-shell.o

I can not upgrade Mac OS X on a PPC any further.
Is there any otrher choice except  ./configure --disable-readline.
Thanks, E. Pasma



Op 13 jul 2017, om 20:49 heeft Richard Hipp het volgende geschreven:


The 3.20.0 release of SQLite is expected in about a week.  Please
report any issues that you have with the beta as soon as possible.

Code is available in the "prerelease snapshot" at
https://sqlite.org/download.html

An overview of changes is at https://sqlite.org/draft/releaselog/3_20_0.html

You can send issues to this email list, or directly to me.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension to query/set environment variables?

[E.Pasma]

Nelson, Erik - 2 wrote:
> Hello, I could imagine a virtual table that held all the environment
> variables, or a user-defined function to get or set environment variables.
> Does anyone know of anything in the wild along these lines?  I know how I
> would implement it, wanted to see if someone else already
> had. Erik
Hello Erik, my self-defined function pass_mprint covers only a single
environment variable. See the temporary link below.
To me it is not obvious how to deal with a set of environment variables. If
these will be identified by names, I fear the overhead of looking up names
in a list.
Edzard Pasma.


http://h1972688.stratoserver.net/sqlite_mprint/170714/

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slow query, with correlated sub-sub-query

[E.Pasma]

Thanks David for the alernative solution, that is 500 times faster or  
any times faster depending on the number of rows. I hope this does not  
derive attention from my point that the original construction is not  
very well delt with.
It was only a theoretical query derived from a more complex one. The  
atrribute "size" is in this complex query yet an other sub-query,  
counting rows in yet another table. I need to see if the alternative  
solution is still faster there.

E. Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slow query, with correlated sub-sub-query

[E.Pasma]

Hello, below is a theoretical query that becomes slow when the number  
of rows increases. What it does is:

- scan input cases in table a
- for each input case:
-- determine the smallest value of attribute size of elements in table  
ab

-- count the number of elements having this smallest size
With 3 rows in table a and 3*1000 in ab this takes already several  
seconds.
I'm not so much interested in an alternative solution, though  
interesting, and merely want to show an inefficient construction. That  
is a sub-sub-query correlated directly to the main query.

Thanks, E. Pasma

.version
SQLite 3.19.3 2017-06-08 14:26:17 ...

create table a (a, primary key (a))
;
create table ab (a, b, size, primary key (a,b))
;
insert into a
with i as (select 1 as i union all select i+1 from i where i<3)
select i from i
;
insert into ab
with i as (select 1 as i union all select i+1 from i where i<1000)
select a, i as b, random()%10 as size from a, i
;
.eqp on
.timer on
select  a,
   (
   select  count(*)
   fromab
   where   a=a.a
   and size=(select min(size) from ab where a=a.a)
   )
froma
;
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE ab USING INDEX sqlite_autoindex_ab_1 (a=?)
--EQP-- 1,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 2
--EQP-- 2,0,0,SEARCH TABLE ab USING INDEX sqlite_autoindex_ab_1 (a=?)
1|56
2|53
3|49
Run Time: real 2.678 user 2.597794 sys 0.008801

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[E.Pasma]

Thomas Flemming Tue, 30 May 2017 09:43:15 -0700


>> Try putting a "+" symbol before "styleid".  Like this:
>>
>> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)


> THATS IT !! :-)))
> 
> 50ms with +, and 15000ms without the +
>
> How is that possible?

Hello, best explained here:
http://sqlite.org/optoverview.html#uplus
(a most interesting document anyway)
Regards, E.  Pasma

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generalized SQLite stored procedure style pivot table exhibit.

[E.Pasma]

15 mei 2017, 07:34 petern:

Here I revisit the pivot table problem using the SQLite stored  
procedure
pattern and pure unmodified SQLite.  I am seeking feedback for  
improving

the brevity of the stored procedure presented here.


Hi, initially I got: near "eval": syntax error. This is after saving  
the mail message as a text file and taking that in the editor two  
become an sql script. This apparently leaves some two-byte spaces,  
causing this humanly invisable error.

How to fix this in VI I don't know.
Downloading the text by simple copy and paste cures fixed the problem.
The procedure works great and can easily be customized.
I'd generate a temporary view as one must not be tempted to keep it  
(and miss new fruits).

Thanks, E. Pasma

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table-naming-expression impact on sqlite3_prepare

[E.Pasma]

27 mrt 2017, petern:
In general I've been thinking about materializing data dependent  
temporary
tables and even using them in CTE's. The tremendous expressive  
economy of
TCL and somewhat built-in support within SQLite got me thinking.   
Consider
the problem of pivot table function for creating temp.crosstab_*  
summary

tables...


I left out the example command line script from you reply. This  
clarifies your somewahat abstract requirement. Now I also see why  
earlier on you wished eval() to return query result. But, as eval()  
does ddl too I just make it define a temp view. That can be selected  
from afterwards. See below. Thanks for the TCL example that helps me  
learn. Best regards, E. Pasma, sponsor of https://facebook.com/RadioParadijs


.version
SQLite 3.18.0 2017-03-06 20:44:13  
ec529bf11b16c801ea438e57d208ff7e4cedf1f9

select load_extension('eval');

drop table if exists sales;
create table sales(year integer,fruit text,tons integer);
insert into sales
values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7), 
(2017,'peaches',6),(2016,'mangos',12);

--select * from sales;
select ddl,  eval(ddl)
from(
select  'create temp view vttt as select year, '
||  group_concat('sum(case when fruit='''
||  fruit
||  ''' then tons end) as '
||  fruit)
||  ' from sales group by year'
as ddl
from( select fruit from sales group by fruit)
)
;
create temp view vttt as select year, sum(case when fruit='apples'  
then tons end) as apples,sum(case when fruit='mangos' then tons end)  
as mangos,sum(case when fruit='peaches' then tons end) as peaches from  
sales group by year|

.header on
select * from vttt;
year|apples|mangos|peaches
2016||12|2
2017|7|3|6


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table-naming-expression impact on sqlite3_prepare

[E.Pasma]

26-03-2017 petern :

> The table-naming-expression, if
> normal expressions are allowed, would obviously require sqlite3_prepare to
> consult the database in situations where the name string expression depended
> on a SQL statement being evaluated. Is this the main problem with allowing
> dynamically named tables SQLite?

Hi, in an earliar post you mentioned the loadable extension eval.c that adds
the eval() SQL function.  Just for  my understanding, may I assume that you
currently use that as a work around. E.g.
select eval(printf("create table %s (a,b,c)', tablename) from mytables;

Anyway thanks for mentioning the existense of eval(). E. Pasma

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group contiguous rows (islands)

[E.Pasma]

Jean-Luc Hainaut:


On 15/02/2017 18:34, E.Pasma wrote:


Hello,  the query below is simpler. May be slower. But looks pretty  
relational. Thanks, E Pasma.


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
   select t.*, max(t2.date) as key2
   from t
   left join t t2
   on t2.datet.test
   group by t.date
   )
group by key2


Quite nice solution indeed!
For those who may feel uncomfortable with outer joins, the from  
clause could be written as a subquery:


from (select date, test, (select  max(date)
 fromt t2
 where  t2.date < t.date
 and  t2.test <> t.test)  
as key2)


Thanks

J-L

this way you may also try to optimise speed by using ORDER BY & LIMIT  
1 instead of MAX


from (select date, test, (select t2.date
  from  t t2
  where t2.date < t.date
  and t2.test <>  t.test
  order by t2.date desc limit 1)  
as key2

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group contiguous rows (islands)

[E.Pasma]

15 feb 2017, Jean-Luc Hainaut:



You could try this, inspired by classic algorithms of temporal  
databases:


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

create table TT(seq integer not null primary key autoincrement,date  
integer,test char(12));

insert into TT(date,test) select * from T order by date;

select T1.date, T3.date, T1.test
from   TT T1, TT T3
-- More efficient than "where  T1.date <= T3.date"
where  T1.seq <= T3.seq
andT1.test = T3.test
andnot exists(select * from TT where seq = T1.seq-1 and test =  
T1.test)
andnot exists(select * from TT where seq = T3.seq+1 and test =  
T3.test)

andnot exists(select *
from   TT T2
-- More efficient than "where  T2.date between  
T1.date and T3.date"

where  T2.seq between T1.seq and T3.seq
andT2.test <> T1.test);

Result:

+--+--+--+
| date | date | test |
+--+--+--+
| 1| 3| clim |
| 7| 10   | amb  |
| 12   | 12   | xxx  |
| 13   | 20   | clim |
| 22   | 25   | amb  |
+--+--+--+

Hello,  the query below is simpler. May be slower. But looks pretty  
relational. Thanks, E Pasma.


create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select min(date) as fromdate, max(date) as enddate, test
from(--get closest preceeding different key
select t.*, max(t2.date) as key2
from t
left join t t2
on t2.datet.test
group by t.date
)
group by key2
;


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[E.Pasma]

10 feb 2017, Dominique Devienne:


There's
http://sqlite.1065341.n5.nabble.com/printf-with-thousands-separator-td85022.html

And my feeble attempt below. But there's got to be a better way, no?
What would be the shortest and/or most efficient way to do this in  
SQL?

..

sqlite> with s(v) as (
  ...>   select 23
  ...>   union all
  ...>   select 1097
  ...>   union all
  ...>   select 123456789
  ...>   union all
  ...>   select 4123456789
  ...> )
  ...> select v,
  ...> case
  ...> when v < 1000 then cast(v as text)
  ...> when v < 100 then printf("%d,%03d", v/1000, v%1000)
  ...> when v < 10 then printf("%d,%03d,%03d", v/100,
v%100/1000, v%1000)
  ...> else printf("%d,%03d,%03d,%03d", v/10,
v%10/100, v%100/1000, v%1000)
  ...> end
  ...> from s
  ...> ;
23|23
1097|1,097
123456789|123,456,789
4123456789|4,123,456,789
sqlite>


Hello, I reply to the original mail as the question for a shorter/ 
moree efficient SQL solution isn't touched in the further discussion,   
Below is my attempt. Thanks, Edzard Pasma


SQLite version 3.16.2
select ltrim(substr(x,-9,3)||','||substr(x,-6,3)||','|| 
substr(x,-3,3),'0,') from (select 1234 as x union select 7 union  
select 123456789);

7
1,234
123,456,789

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] can not use row values from inside a trigger

[E.Pasma]

I'm using SQLite only for private purpose. This also allows me to try
incredably complex queries. And find that SQLite is reliable indeed and
fast. Currently I try to execute a script of 30 statements from inside a
trigger. And amazed to not have any error. Except one, when using the new
functionality for row values from inside a trigger. The example below  shows
an update that works as a plain statement and fails from inside a trigger.
This case luckily raises an error. When supplying just two values the error
is not reported. Possibly something is still wrong then.

SQLite version is 3.15.2.


create table t(a,b,c)
;
insert into t values(1,2,3)
;
update t set (a,b,c)=(select 4,5,6)
;
select * from t
;
4|5|6

create table step (step integer primary key)
;
create trigger trg_step insert on step
begin update t set (a,b,c)=(select 7,8,9); end
;
Error: near line 17: Expression tree is too large (maximum depth 1000)
select * from t
;
4|5|6


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] group_replace

[E.Pasma]

11 aug 2016, Dominique Devienne:

On Thu, Aug 11, 2016 at 1:10 PM, Anthony Lansbergen  


wrote:


Hello,

I needed a way to make parameterized texts for logging in my current
project.
For this purpose I missed a function in sqlite: group_replace, so I  
made

an extension.
The function is just like group_concat, an aggregate function, but it
replaces keys with values in a string instead of concattenating  
strings.


I put the code online, it's publicly available at:

https://github.com/adesys/sqlite3_group_replace_extension

It seems to work fine, but since this is my first extension, can  
someone

please take a look at it and check if it is bug free :-)



From https://www.sqlite.org/c3ref/value_blob.html:
Please pay particular attention to the fact that the pointer  
returned from

[...] sqlite3_value_text(),
[...] can be invalidated by a subsequent call to [...]
sqlite3_value_text(), [...]

I'd also check sqlite3_value_type() explicitly for SQLITE_TEXT to  
avoid

implicit conversions.

Who frees p->result in _final()? No one IMHO. So leak I think.

I didn't check the rest of the logic deeply. --DD

PS: I'm also a little wary of aligned issues from the allocation  
done by

sqlite3_aggregate_context()
but I guess that's the C++ dev talking. Back in my C days (a long time
ago...) I don't remember
worrying about it, so maybe that's a C++ thing only.



UpdateL the memory issue is resolved in yodays commit in 
https://github.com/adesys/sqlite3_group_replace_extension
at least in my test.
E, Pasma
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Good way for CEIL, or is there a better way

[E.Pasma]

09-05-2016, OBones:
> Isn't Ceil(Value) simply Round(Value + 0.5) ?
But Round(0.5) = 1
May be Round(Value+0.4) is good enough? 

[sqlite] Is it possible that dropping a big table takes very long

[E.Pasma]

23 apr 2016, E.Pasma:

> Hello,
> I tried the scripts but..
>
> createBigTable.sh is beyond the capacity of my system. Instead I  
> used SQL script like in
> www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html
>
> My point is that the definition of the table is a waste of capacity,  
> even though it serves on many systems. Because it has a primary key  
> and also rowid which are not the same. Is there any practical use of  
> retaining the rowid?
>
> I tested the 1E7 case WITHOUT ROWID. The size of the database is  
> then reduced to 222M. Drop table is a matter of seconds (for me too  
> now).
>
> I may do further testing with more rows. Until then I have the  
> feeling that this will scale linearly and not show instable timings  
> any longer.
>
> Below is the output of my tests.
>
> Thanks, E Pasma

testing with more rows learned me that the "WITHOUT ROWID" version  
does not scale linearly either (case 2b below).
Started yet another version, where the uuid column is not defined as  
primary key and where a tradional unique index is added instead. This  
DOES scale linearly (case 3 and 3b).
I can't see why this new version (in case 3b) does not suffer from  
excessive response times. After all the UI is bigger as the PK-only  
index.
But it is awesome that these tests are possible on a minimal system.
Thanks, E Pasma


caseindexes #rows   DBsize  insert  drop
1   rowid+PK1E7 505M6043s   241s
2   PK only 1E7 222M1141s   2s
3   rowid+UI1E7 480M304s44s
2b  PK only 2E7 445M58035s  886s
3b  rowid+UI2E7 970M802s61s


log of case 1

create table uuid (uuid blob, primary key (uuid))
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<1000)
select randomblob(16) from r
;
Run Time: real 6043.491 user 332.250625 sys 671.583469
.sys du -h west1.db*
505Mwest1.db
begin
;
drop table uuid
;
Run Time: real 241.746 user 2.296482 sys 5.978103
rollback
;



log of case 2

create table uuid (uuid blob, primary key (uuid)) without rowid
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<1000)
select randomblob(16) from r
;
Run Time: real 1141.098 user 294.535994 sys 573.902807
.sys du -h west2.db*
222Mwest2.db
begin
;
drop table uuid
;
Run Time: real 1.974 user 0.844361 sys 1.095968
rollback
;



log of case 3

create table uuid (uuid blob)
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<1000)
select randomblob(16) from r
;
Run Time: real 65.923 user 44.141960 sys 2.980705
.sys du -h west3.db*
238Mwest3.db
create unique index uuidprime on uuid(uuid)
;
Run Time: real 304.453 user 70.280531 sys 11.903746
.sys du -h west3.db
480Mwest3.db
begin
;
drop table uuid
;
Run Time: real 44.634 user 2.050981 sys 2.988656
rollback
;



log of case 2b

create table uuid (uuid blob, primary key (uuid)) without rowid
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<2000)
select randomblob(16) from r
;
Run Time: real 58035.455 user 673.901377 sys 1518.352532
445Mwest2b.db
.sys du -h west2b.db*
begin
;
drop table uuid
;
Run Time: real 886.570 user 2.287946 sys 9.943783
rollback
;



log of case 3b

create table uuid (uuid blob)
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<2000)
select randomblob(16) from r
;
Run Time: real 119.143 user 88.404970 sys 6.073330
.sys du -h west3b.db*
479Mwest3b.db
create unique index uuidprime on uuid(uuid)
;
Run Time: real 683.631 user 145.164905 sys 25.329360
.sys du -h west3b.db
970Mwest3b.db
begin
;
drop table uuid
;
Run Time: real 61.205 user 4.096998 sys 5.253853
rollback
;

[sqlite] Is it possible that dropping a big table takes very long

[E.Pasma]

Hello,
I tried the scripts but..

createBigTable.sh is beyond the capacity of my system. Instead I used  
SQL script like in
www.mail-archive.com/sqlite-users%40mailinglists.sqlite.org/msg08044.html

My point is that the definition of the table is a waste of capacity,  
even though it serves on many systems. Because it has a primary key  
and also rowid which are not the same. Is there any practical use of  
retaining the rowid?

I tested the 1E7 case WITHOUT ROWID. The size of the database is then  
reduced to 222M. Drop table is a matter of seconds (for me too now).

I may do further testing with more rows. Until then I have the feeling  
that this will scale linearly and not show instable timings any longer.

Below is the output of my tests.

Thanks, E Pasma


case 1, like original (sqlite version 3.12 and page size 4096 here)

.timer on
create table uuid (uuid blob, primary key (uuid))
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<1000)
select randomblob(16) from r
;
Run Time: real 6043.491 user 332.250625 sys 671.583469
.sys du -h west1.db*
505Mwest1.db
begin
;
drop table uuid
;
Run Time: real 40.378 user 2.259595 sys 5.500557
rollback
;
.quit



case 1, drop once again (completely different timing)

.sys du -h west1.db*
505Mwest1.db
begin
;
delete from uuid
;
Run Time: real 241.711 user 2.246336 sys 5.981215
drop table uuid
;
Run Time: real 0.000 user 0.000567 sys 0.000230
rollback
;
.quit




case 2, without rowid


.timer on
create table uuid (uuid blob, primary key (uuid)) without rowid
;
insert into uuid
with r as (select 1 as i union all select i+1 from r where i<1000)
select randomblob(16) from r
;
Run Time: real 1141.098 user 294.535994 sys 573.902807
.sys du -h west2.db*
222Mwest2.db
begin
;
drop table uuid
;
Run Time: real 1.974 user 0.844361 sys 1.095968
rollback
;
begin
;
delete from uuid
;
Run Time: real 1.924 user 0.829793 sys 1.060908
drop table uuid
;
Run Time: real 0.006 user 0.000734 sys 0.002387
rollback
;

[sqlite] Is it possible that dropping a big table takes very long

[E.Pasma]

22 apr 2016, Cecil Westerhof:
>
> ?With createBigTable.sh ...
Can you paste the svript in the message? Attachments are not sent.
Regards, E.Pasma

[sqlite] Is it possible that dropping a big table takes very long

[E.Pasma]

21 apr 2016, Cecil Westerhof:
>
> ?I think it is an edge case. On my real system I only got this when  
> there
> where 1E8 records. I am now testing on very old (8 year) hardware to  
> and
> from work.

Hello,
the answer to Cecils question is YES here. I tested on a computer with  
just 512 Mb RAM. It took almost an hour to drop the testuniqueUUID  
table. Same result for delete. This is "very long" in terms of SQLite.  
I also tested dropping a 500Mb table with only integer data and that  
took a minute. Below is the output of both tests.
Note the integer test is done on a more recent SQLite version as to  
use the series extension. I believe the version does not matter as I  
started drop testuniqueUUID db also on this later version.  
Unfortunately interrupted that after 10 minutes.
Thanks for all the info, E. Pasma

$ uname -aDarwin mac-mini-van-epasma.local 9.8.0 Darwin Kernel Version  
9.8.0: Wed Jul 15 16:57:01 PDT 2009; root:xnu-1228.15.4~1/RELEASE_PPC  
Power Macintosh

$ sqlite3 checkUUID.sqlite
SQLite version 3.8.11 2015-07-27 13:49:41
Enter ".help" for usage hints.
sqlite> begin;
sqlite> .timer on
sqlite> drop table testUniqueUUID;
Run Time: real 3846.582 user 5.418944 sys 25.144210
sqlite> rollback;
Run Time: real 36.348 user 0.036132 sys 0.218740
sqlite> .quit



$ sqlite3 westerhof2.db
SQLite version 3.12.0 2016-03-22 15:26:03
Enter ".help" for usage hints.
sqlite> create table t (t integer primary key);
sqlite> .load series
sqlite> insert into t select value from generate_series(1,6000);
sqlite> .sys du -h westerhof2*
516Mwesterhof2.db
sqlite> begin;
sqlite> .timer on
sqlite> drop table t;
Run Time: real 52.705 user 6.383107 sys 5.870330
sqlite> rollback;
Run Time: real 0.085 user 0.001651 sys 0.006027
sqlite> .quit


1,1   Top

[sqlite] User-defined SQL functions

[E.Pasma]

23 feb 2016, Dan Kennedy:

> On 02/23/2016 07:36 PM, E.Pasma wrote:
>> 22 feb 2016, Dan Kennedy:
>>
>>> On 02/23/2016 01:33 AM, E.Pasma wrote:
>>>>
>>>> I reproduced the memory leak and added a test in the sql script.
>>>> An alternative fix, instead of adding the missing break, is:
>>>>
>>>>  case SQLITE_TEXT:
>>>>  case SQLITE_BLOB:
>>>>pval->n=sqlite3_value_bytes(arg);
>>>>if (!pval->n) {
>>>>  pval->z="";
>>>>} else {
>>>>  pval->z=sqlite3_malloc(pval->n);
>>>>  assert (pval->z); /* TODO: SQLITE_NOMEM*/
>>>> memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
>>>>}
>>>>break;
>>>>
>>>> Thus sqlite3_value_blob is used to get both text or blob value  
>>>> (like in sqlite3.c at line ~93615 in routine  attachFunc).
>>>>
>>>> If no response I opt for the alternative fix and place it at
>>>> http://h1972688.stratoserver.net/sqlite_mprint/160223
>>>
>>>
>>> Suggest testing with text values and a utf-16 database.
>>>
>>> Dan.
>>
>> Thanks, testing with a utf-16 database does not yield any  
>> differences. I inserted some 2-byte character and checked that it  
>> comes out unchanged from mprint. Hope this will do.
>
> Very good.
>
> I think it's working because the call to sqlite3_value_bytes() is  
> transforming the utf-16 text in the sqlite3_value to utf-8. And then  
> value_blob() just returns whatever encoding the sqlite3_value has  
> stored - turns out that's utf-8 by the time it's called.
>
> Dan.
And the original "type" (utf-16) is restored when the value is used  
via sqlite3_result_text.
When returning text via sqlite3_result_blob:
- In a utf-8 database it can still be casted as the original text
- in a utf-16 database this is no longer true, a != cast (mprint(a) as  
text)
Thanks and sorry for diverting from the subject,

[sqlite] User-defined SQL functions

[E.Pasma]

22 feb 2016, Dan Kennedy:

> On 02/23/2016 01:33 AM, E.Pasma wrote:
>>
>> I reproduced the memory leak and added a test in the sql script.
>> An alternative fix, instead of adding the missing break, is:
>>
>>   case SQLITE_TEXT:
>>   case SQLITE_BLOB:
>> pval->n=sqlite3_value_bytes(arg);
>> if (!pval->n) {
>>   pval->z="";
>> } else {
>>   pval->z=sqlite3_malloc(pval->n);
>>   assert (pval->z); /* TODO: SQLITE_NOMEM*/
>>   memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
>> }
>> break;
>>
>> Thus sqlite3_value_blob is used to get both text or blob value  
>> (like in sqlite3.c at line ~93615 in routine  attachFunc).
>>
>> If no response I opt for the alternative fix and place it at
>> http://h1972688.stratoserver.net/sqlite_mprint/160223
>
>
> Suggest testing with text values and a utf-16 database.
>
> Dan.

Thanks, testing with a utf-16 database does not yield any differences.  
I inserted some 2-byte character and checked that it comes out  
unchanged from mprint. Hope this will do.

sqlite> .version
SQLite 3.11.0 2016-02-12 00:13:38  
b5d771991686bf86a679b7dff9f16301a5029c8b
sqlite> pragma encoding='utf-16';
sqlite> pragma encoding;
UTF-16be
sqlite> create table t (a);
sqlite> insert into t values ('abc');
sqlite> insert into t values ('d'||char(1000)||'f');
sqlite> select typeof(a), length(a), length(cast (a as blob)), a from t;
text|3|6|abc
text|3|6|d?f
sqlite> .load ttt_mprint
sqlite> select * from t where a=mprint(a);
bytes: 3, memcmp: 0
abc
bytes: 4, memcmp: 0
d?f

ttt_mprint is compiled to compare sqlite3_value_blob and  
sqlite3_value_text and print the outcome of memcmp. The relevant part  
is:

 case SQLITE_TEXT: /* use sqlite3_value_blob */
 case SQLITE_BLOB: /* use sqlite3_value_blob */
   pval->n=sqlite3_value_bytes(arg);
   if (!pval->n) {
 pval->z="";
 z2=""; //testing
   } else {
 pval->z=sqlite3_malloc(pval->n);
 z2=sqlite3_malloc(pval->n); //testing
 memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
 assert (pval->z); /* TODO: SQLITE_NOMEM*/
 memcpy(z2,sqlite3_value_text(arg),pval->n); //testing
   }
   printf("bytes: %d, memcmp: %d \n",
 pval->n,
 memcmp(pval->z, z2, pval->n)); //testing
   break;

[sqlite] User-defined SQL functions

[E.Pasma]

Hi,
forget to mention that a function like this was earlier considered as  
being tricky and living dangerously.
Also we found a bug (missing break) between line 80 and 81 which will  
lead to a memory leak every time a text value is stored ...

  switch (pval->t) {
case SQLITE_INTEGER:
  pval->n=0;
  pval->i=sqlite3_value_int64(arg);
  break;
case SQLITE_TEXT:
  pval->n=sqlite3_value_bytes(arg);
  if (!pval->n) {
pval->z="";
  } else {
pval->z=sqlite3_malloc(pval->n);
assert (pval->z); /* TODO: SQLITE_NOMEM*/
memcpy(pval->z,sqlite3_value_text(arg),pval->n);
  }
  break;  /* << MISSING break << */
case SQLITE_BLOB: /* sqlite3_value_blob instead of _text */
  pval->n=sqlite3_value_bytes(arg);
  if (!pval->n) {
pval->z="";
  } else {
pval->z=sqlite3_malloc(pval->n);
assert (pval->z); /* TODO: SQLITE_NOMEM*/
memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
  }
  break;
case SQLITE_FLOAT:
  pval->n=0;
  pval->r=sqlite3_value_double(arg);
  break;
  }
}

I reproduced the memory leak and added a test in the sql script.
An alternative fix, instead of adding the missing break, is:

case SQLITE_TEXT:
case SQLITE_BLOB:
  pval->n=sqlite3_value_bytes(arg);
  if (!pval->n) {
pval->z="";
  } else {
pval->z=sqlite3_malloc(pval->n);
assert (pval->z); /* TODO: SQLITE_NOMEM*/
memcpy(pval->z,sqlite3_value_blob(arg),pval->n);
  }
  break;

Thus sqlite3_value_blob is used to get both text or blob value (like  
in sqlite3.c at line ~93615 in routine  attachFunc).

If no response I opt for the alternative fix and place it at
http://h1972688.stratoserver.net/sqlite_mprint/160223

E. Pasma

[sqlite] User-defined SQL functions

[E.Pasma]

21 feb 2016, Igor Tandetnik:

> On 2/21/2016 12:09 PM, Olivier Mascia wrote:
>> Is it possible to implement a SQL function 
>> (https://www.sqlite.org/c3ref/create_function.html 
>> ), which implementation would be able to return the same value for  
>> the duration of the current transaction?
>>
>> In other words, assume I'd like to implement some function which I  
>> could name 'now()' which would return the current_timestamp as  
>> julian day, but would return exactly the same value for any call  
>> made during the same transaction?
>
> When I needed a stable version of now(), this is what I did. I had a  
> global (well, actually, per-connection) time_t variable. I passed  
> its address to sqlite3_create_function, to be retrieved within the  
> custom function's implementation by means of sqlite3_user_data.
>
> The custom function would check this variable. If it's zero, it  
> would obtain the current timestamp save it in that variable, and  
> return it as its result; otherwise, it would return the previously- 
> cached value.
>
> Whenever I started a new transaction, I reset the variable to zero.  
> Luckily, I had a single spot issuing BEGIN statements (a C++ class  
> wrapping SQLite connection handle; the time_t variable was also part  
> of that class).
> -- 
> Igor Tandetnik

Hello Oliver, I have a tool that is purely within SQLite. I once named  
it sqlite_mprint. It is far from the requirement but may be a start.

sqlite> .load sqlite_mprint
sqlite> select mprint (datetime('now')); --set time
2016-02-22 14:30:03
sqlite> select mprint() as t0; --get old time
2016-02-22 14:30:03

The code is temporarily at  
http://h1972688.stratoserver.net/sqlite_mprint/160222

Best regards, E. Pasma

[sqlite] Best way to store only date

[E.Pasma]

30-01-2016 14:59, R Smith:

> 
> 
> On 2016/01/30 3:22 PM, E.Pasma wrote:
>> The diagram got broken in my email and here is another try:
>> 
>> Needs to be light | Needs to be| Needs to do  |
>> (small footprint) | Human-Readable | calculations |
>> - | ---|  |
>> YES   | YES| NO   | Integer as
>> ||  | Igor's suggestion
>> ||  |
>> YES   | NO | YES  | Float/Int
>> ||  | Julianday
>> ||  |
>> NO| YES| YES  | Datetime/Numeric
>> ||  | ISO Standard
> 
> Thank you for the fix.
> 
>> 
>> With respect to Igor's suggestion, mmdd (as integer), why not leave out
>> the century? I prefer the oldfashoned yymmdd.
> 
> When dealing with a localized context around the current period, a two
> digit date is often enough - so if you see '12 or '16 or '20 you can
> easily assume that to mean 2012, 2016 etc. But what if you see '51?
> Would that be 1951 or 2051?
> The context would probably enlighten the meaning, but it's best to leave
> context to the users and not pre-empt it during the design phase. A
> four-digit year is best for standard human reference. (You won't need to
> also add the AD. bit)  :)
> 
My private use is adding yymmdd as an extension to document names on my
computer. You can leave it to me to know what 550501 refers to.
Professionally this sounds very bad, agreed with the others. But a system
may deal with a two-digit date according to clear rules. See the Oracle RR
date format

 http://oracleeducation.blogspot.nl/2007/05/oracle-date-format_22.html

But I may bring this up after a couple of decades again :-)

[sqlite] Best way to store only date

[E.Pasma]

The diagram got broken in my email and here is another try:

 Needs to be light | Needs to be| Needs to do  |
 (small footprint) | Human-Readable | calculations |
 - | ---|  |
 YES   | YES| NO   | Integer as
   ||  | Igor's suggestion
   ||  |
 YES   | NO | YES  | Float/Int
   ||  | Julianday
   ||  |
 NO| YES| YES  | Datetime/Numeric
   ||  | ISO Standard

With respect to Igor's suggestion, mmdd (as integer), why not leave out
the century? I prefer the oldfashoned yymmdd.

Thanks, E. Pasma
30-01-2016 00:31, R Smith:

> 
> On 2016/01/29 5:23 PM, Igor Tandetnik wrote:
>> 
>> Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other
>> words, storing calendar dates as integers like 20160129.
> 
> The main advantage of this format is that it is of course
> human-readable, even as an integer.
> The important disadvantage is that you cannot do date calculations
> without first casting and translating - something the Julian day or more
> expensive 19-char ISO format (-MM-DD HH:NN:SS which is
> human-readable AND in most systems calculatable) is better at.
> 
> My point being: when I decide which date format to use, I first try to
> establish whether I will use it for calculations or simply record/log
> purposes, and if readability (from data source) would be needed/helpful
> or not. The decision matrix ends up something like this:
> 
> 
> Needs to be light (small footprint)| Needs to be Human-Readable
> | Needs to do calculations   |
> -- | --
> | -- | --
> YES  | YES |
> NO | Integer (as Igor's suggestion)
> YES  |NO |
> YES|  Float/Int Julianday
> NO   | YES |
> YES| Datetime/Numeric ISO Standard
> -- | --
> | -- | --
> 
> If you can say "No" to two of these criteria, go for the most efficient.
> 
> If you can say "No" to all three criteria, perhaps reconsider whether
> you really need that column in your table.
> 
> 
> Cheers,
> Ryan
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

[E.Pasma]

06-01-2016, Scott Perry:

The SQLite built into OS X does not support cache sharing for performance
reasons?, which is probably why your results are statistically identical and
the OP's results are wildly different.

You can verify this by checking the return value of
sqlite3_enable_shared_cache; on OS X it returns SQLITE_MISUSE.
-
On multicore systems, memory barriers are extremely expensive; not sharing
caches allows them to run lock-free

Hello,
to recall the timings once again:

sanhua.zh's test (objective C)


without shared cache mode
2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914
2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914
2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964
2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958

shared cache mode
2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480
2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449
2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768
2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169


My Python test:

Timings for default mode:
steps 10 connect+fetch 4.39 connect 0.0
steps 10 connect+fetch 4.52 connect 0.0
steps 10 connect+fetch 4.62 connect 0.0
steps 10 connect+fetch 4.51 connect 0.0
elapsed 5.21

Timings in shared cache mode:
steps 10 connect+fetch 6.4 connect 0.0
steps 10 connect+fetch 6.17 connect 0.0
steps 10 connect+fetch 6.56 connect 0.0
steps 10 connect+fetch 6.46 connect 0.0
elapsed 6.85

At least the tests in non-shared cache mode should be comparable. And I try
to explain why on my poor Mac the test is 100-200 times slower there (4.5 /
0.03 seconds). This must be the overhead of the Python wrapper. After
sqlite_step the row is converted for Python. This overhead is practically
neglectable but apparently not in an exagerated test (fetching rows without
any further action).

Further it would be interesting to know if Sanhua tested on a multicore
system. Then cache sharing is indeed very expensive there  (if I summarize
this correctly)

Thanks, E. Pasma

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

[E.Pasma]

20 dec 2015, 14:29, sanhua.zh:

> Here is the test result for selecting 100,000 items in original test  
> case.
>
>
> shared cache mode
> 2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480
> 2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449
> 2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768
> 2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169
>
>
> without shared cache mode
> 2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914
> 2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914
> 2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964
> 2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958
>
>
> May be your python code does not run the same thing as mine, I am  
> poor in python so that I could not figure it out.
Indeed, I had changed the query to just scan the table in the database  
and not return all  rows. Now I chaged that and also set the  
corresponding number of rows. Still cache sharing does not make such a  
mega differencr. Below are the timings.
I have no clu now for your mega difference.
SQLite version? 3.8 here
hardware? I have (only) Mac OS X 10.5.8 with a 1.22 GHz PowerPC

Timings for default mode:
$ python3 larry3.py [B
sqlite version 3.8.3.1
cache sharing 0 cache size 2000 rowcount 10
after split 0.0037369728088378906
after start 0.8668131828308105
steps 10 connect+fetch 4.39 connect 0.0
steps 10 connect+fetch 4.52 connect 0.0
steps 10 connect+fetch 4.62 connect 0.0
steps 10 connect+fetch 4.51 connect 0.0
elapsed 5.21

Timings in shared cache mode:
sqlite version 3.8.3.1
cache sharing 1 cache size 2000 rowcount 10
after split 0.0035581588745117188
after start 0.7083160877227783
steps 10 connect+fetch 6.4 connect 0.0
steps 10 connect+fetch 6.17 connect 0.0
steps 10 connect+fetch 6.56 connect 0.0
steps 10 connect+fetch 6.46 connect 0.0
elapsed 6.85

Python script:

import random, os, time, threading
import sqlite3 as sqlite
print ("sqlite version", sqlite.sqlite_version)

TESTDB='larry.tmp'
SHARED=0
SIZE=2000
ROWCOUNT=10
print('cache sharing', SHARED, 'cache size', SIZE, "rowcount", ROWCOUNT)
sqlite.enable_shared_cache(SHARED)

def connect():
 con= sqlite.Connection (TESTDB, isolation_level=None,  
check_same_thread=0)
 con.execute ("pragma cache_size=%i"%(SIZE,))
 return con

def e_str (e):
 " format exception as string "
 return "%s: %s" % (e.__class__.__name__, e)

class Testthread (threading.Thread):
 """
 execute query in a thread
 """
 def __init__ (self, qq, con = None):
 self.con = con
 self.qq = qq
 self.out = "thr%i.out" % id (self)
 open (self.out, 'w').close ()
 os.remove (self.out)
 threading.Thread.__init__ (
 self,
 target=self.__target,
 )
 def start (self):
 threading.Thread.start (self)
 for retry in range (5):
 time.sleep (2 ** retry * .05)
 if os.access (self.out, os.R_OK):
 break
 else:
 print("Testthread: spoolfile does not appear")
 time.sleep (.10) # for the SQL to start
 def __target (self):
 subt0=time.time()
 if not self.con:
 self.con = connect ()
 dt1=round(time.time()-subt0,2)
 f = open (self.out, 'w')
 try:
 try:
 n=0
 for q in self.qq.split (';'):
 i=None
 for i in self.con.execute(q):
 n+=1
 continue
 f.write (str(i)+'\n') # write last line only
 dt2=round(time.time()-subt0,2)
 print("steps", n, "connect+fetch", dt2, "connect", dt1)
 except Exception as e:
 f.write (e_str (e) + '\n')
 finally:
 f.close()
 self.con.close ()
 def join (self, timeout=None):
 if timeout is not None:
 threading.Thread.join (self, timeout)
 else:
 timeout = 7.5 # respond to keyboard interrupts
 while self.isAlive ():
 threading.Thread.join (self, timeout)
 return self
 def get_result (self):
 try:
 return open (self.out, 'r').read ().strip ()
 except IOError as e:
 return None

def main ():
 # create some test data
 con = connect ()
 try:
 n=con.execute("select count(*) from test").fetchone()[0]
 except sqlite.OperationalError:
 n=None
 con.close()
 if n!=ROWCOUNT:
 print ("n!=ROWCOUNT",n ,ROWCOUNT, "creating new test  
database..")
 open (TESTDB, 'w').close ()
 os.remove (TESTDB)
 con = connect ()
 con.execute ("create table test (id integer)")
 con.execute ("begin")
 con.executemany(
 "insert into test(id) values(:1)",
 ((i+1,) for i in range(ROWCOUNT)))

[sqlite] {Spam?} SQLite take lower performance while using shared cache on iOS/Mac

[E.Pasma]

17 dec 2015, sanhua.zh:

> I try to use shared cache to optimize my code.  
> Sincesqlite3_enable_shared_cache is deprecated on iOS/Mac, I  
> usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag to open shared  
> cache mode.
> 4 threads select is running in my code, while each thread has its  
> own sqlite connection and do the same thing - select all 10 item  
> from ?test? table.
> 
>  for (int i = 0; i  100; i++) {
>.."insert into test values(%d);"..
> ...


Hello,

A rude question: is there any chance that the test in shared cache  
mode was taken with 1.000.000 rows instead of 100.000?
Because I have  quite different results:
regular mode: 6.2 seconds
cache sharing: 6.35  seconds

Thanks for posting the program source. I had to use a Python program  
by lack of objective C. I'll include the source too, see below.

Another thing: I found that  sqlite3_open takes significant time when  
connecting to a shared cache that is in use. Therefore the Python test  
measures the overall elapsed time. Python offers a thread.join method  
to know exactly when a thread is finished.

Tnanks, E.Pasma

import random, os, sys, time, threading, subprocess, socket, socket
import sqlite3 as sqlite

TESTDB='larry.db'
SHARED=0
SIZE=2000
print('cache sharing', SHARED, 'cache size', SIZE)
sqlite.enable_shared_cache(SHARED)

def connect():
 con= sqlite.Connection (TESTDB, isolation_level=None,  
check_same_thread=0)
 con.execute ("pragma cache_size=%i"%(SIZE,))
 return con

def e_str (e):
 " format exception as string "
 return "%s: %s" % (e.__class__.__name__, e)

class Testthread (threading.Thread):
 """
 execute query in a thread
 """
 def __init__ (self, qq, con = None):
 self.con = con
 self.qq = qq
 self.out = "thr%i.out" % id (self)
 open (self.out, 'w').close ()
 os.remove (self.out)
 threading.Thread.__init__ (
 self,
 target=self.__target,
 )
 def start (self):
 threading.Thread.start (self)
 for retry in range (10):
 time.sleep (2 ** retry * .05)
 if os.access (self.out, os.R_OK):
 break
 else:
 print("Testthread: spoolfile does not appear")
 time.sleep (.10) # for the SQL to start
 def __target (self):
 if not self.con:
 self.con = connect ()
 f = open (self.out, 'w')
 try:
 try:
 for q in self.qq.split (';'):
 for i in self.con.execute(q).fetchall():
 f.write (str(i)+'\n')
 except Exception as e:
 f.write (e_str (e) + '\n')
 finally:
 f.close()
 self.con.close ()
 def join (self, timeout=None):
 if timeout is not None:
 threading.Thread.join (self, timeout)
 else:
 timeout = 7.5 # respond to keyboard interrupts
 while self.isAlive ():
 threading.Thread.join (self, timeout)
 return self
 def get_result (self):
 try:
 return open (self.out, 'r').read ().strip ()
 except IOError as e:
 return None

def main ():
 # create some test data
 ###try: os.remove (TESTDB)
 ###except OSError: pass
 t0=time.time()
 con = connect ()
 cur = con.cursor ()
 cur.execute ("begin")
 try:
 cur.execute ("create table test (id integer)")
 except sqlite.OperationalError:
 pass
 else:
 for i in range (100):
 cur.execute ("insert into test(id) values(:1)",(i,))
 cur.execute ("end")
 cur.close ()
 con.close ()
 print("na insert", time.time()-t0)
 qq="SELECT COUNT(*) FROM test WHERE +id<>-1"
 qq+=';'+qq
 tthh=[]
 for i in range(4):
 con = None ###connect()
 tthh.append (Testthread (qq, con))
 print("na splits", time.time()-t0)
 for th in tthh:
 th.start ()
 print("na start", time.time()-t0)
 for th in tthh:
 res=th.join ().get_result().split('\n')
 assert res[0]=='(100,)', res[0]
 print(time.time()-t0, 'seconds')

if __name__ == "__main__":
 main ()

[sqlite] Problem with accumulating decimal values

[E.Pasma]

16 dec 2015, 16:17, Bernardo Sulzbach:
>
> On Wed, Dec 16, 2015 at 12:05 PM, E.Pasma  wrote:
>
>> Ok this does not work of any scale of numbers. But a solution with  
>> integers
>> neither does
>>
>> E.Pasma
>>
> ...I like integer better than floating points and text for  
> currencies ...


Good taste.
I now see a counter example where a solution with rounded floating  
point columns goes wrong. This is with aggregate functions. Using SUM  
adds up the errors before rounding and that may be too late.
Tnanks.

[sqlite] Problem with accumulating decimal values

[E.Pasma]

16 dec 2015, Keith Medcalf:
>> BEGIN;
>> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
>> (repeat a 1.000.001 times
>> END;
>> SELECT bal FROM fmtemp;
>> 123450123.45
>
> You should NEVER round as you have done above.  You may get lucky  
> and the errors may cancel each other out, or you may get more usual  
> results where the error equals the theoretical max of the sum of the  
> absolute value of all the truncated values, which can be quite  
> significant depending on the scale of the number you are dealing  
> with (and theior scales relative to each other).


Hello, I was only trying to digest JKL's post and the result looks  
good. The example prints the value as it is in the database and shows  
that there is no accumulated error there. I do not see a counter  
example (not yet).

Ok this does not work of any scale of numbers. But a solution with  
integers neither does

E.Pasma

[sqlite] Problem with accumulating decimal values

[E.Pasma]

16 dec 2015,  James K. Lowden:

> On Fri, 11 Dec 2015 16:21:30 +0200
> "Frank Millman"  wrote:
>
>> sqlite> UPDATE fmtemp SET balance = balance + 123.45;
>> sqlite> SELECT bal FROM fmtemp;
>> 5925.599
>
> To a question like that you'll receive a lot of answers about  
> numerical
> accuracy.  And it's true that there are ways to "do the math" without
> using floating point representation.  It's also true that it's rarely
> necessary, which is why floating point representation exists and *is*
> widely used.  You may find it works for you too, unless you have to
> adhere to a specific rounding policy.
>
> Per your example, you're working with 2 decimal places of precision.
> 5925.599 rounds off to 5925.60; it even rounds off to
> 5925.60, not too shabby.  If you keep adding 123.45 to it,
> you'll find you can go on forever before the answer is wrong in the
> second decimal place.
>
> IEEE 754 is a solid bit of engineering.  It's capable of representing
> 15 decimal digit of precision.  That's good enough to measure the
> distance to the moon ... in millimeters.
>
> You could have an exceptional situation, but that would be
> exceptional.  Usually, double-precision math works just fine, provided
> you have some form of round(3) at your disposal when it comes time to
> render the value in decimal form.
>
> --jkl
Hello, so in short, rounding the column anywhere it is used, is  
another solution. I confirmed this below. Thanks, E. Pasma.

BEGIN;
UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45;
(repeat a 1.000.001 times
END;
SELECT bal FROM fmtemp;
123450123.45

[sqlite] Virtual tables and table-valued functions

[E.Pasma]

6 dec 2015, Charles Leifer:

> In working on a Python wrapper around virtual tables, I thought it  
> might be
> beneficial if SQLite provided an official C API for creating simple
> table-valued functions. The wrapper could build on the existing  
> virtual
> table APIs and would consist of:
>
> * user supplied list of parameters, which would be converted to HIDDEN
> columns in a virtual table.
> * user supplied list of column names, which would constitute the  
> return
> values of the table-valued function
> * user supplied initialization function which would accept the  
> parameters
> passed, i.e. SELECT * FROM user_func(p1, p2, p3), the init function  
> would
> receive (p1, p2, p3).
> * user supplied iterate function which would accept a zero-based  
> index and
> a result context, returning 1 to indicate EOF and 0 to indicate more
> results exist.
>
> I think this might lead to a larger ecosystem of quality open-source
> table-valued functions. Given the fact that creating even a simple  
> vtable
> is cumbersome, providing an API that required the bare minimum seems  
> like a
> good idea to me.
>
> Thoughts?
>
> PS if you're curious about the Python wrapper, I posted on it a day  
> or two
> ago.
One thought that is only a change in the current class, is to have a  
default xBestIndex method. This would just optimize for plans with the  
highest number of usable parameter values, starting from number 1..  
The function may then become optional. In xFIlter the number of  
parameters is still available from the argc argument.
The xBestIndex is rather verbose and it looks that in most cases it  
should do just the above.

[sqlite] Remarks about vtab generate_series.

[E.Pasma]

Hello, I have two mini minor remarks about the series.c example as of  
2015-08-21
  and referred to from the Table-Valued Functions paragraph of 
https://www.sqlite
.org/vtab.html#tabfunc2

1. comment above seriesDisconnect
< ** This method is the destructor for series_cursor objects.
 > ** This method is the destructor for series_vtab objects.

2. estimatedCost in seriesBestIndex
A plan where all 3 parameters are passed in is now assigned the same  
cost as a plan where just the first 2 parameters are set. I can  
construct a case where the wrong plan is choosen, although it looks  
exaggerated:
select value from t join generate_series(1,10,t.step)

Possibly my remarks can be considered when the example is maintained  
for a future release. Thanks a lot. E. Pasma.
~
~
~ 

[sqlite] optimization for outer join with most simple views

[E.Pasma]

Hello, I like to post this remark again as it seems closely related to  
"Query flattening for left joins involving subqueries on the right- 
hand side".

I have a complete different reason though. For playing with sudoku  
solving, I have a table representing the digits 1..9:

   CREATE TABLE digit (z integer primary key, i integer);

Besides I have an entity part for representing squares on a line and  
this has excatly the same content as digit. For the sake of economy I  
made it a view:

   CREATE VIEW part AS SELECT z AS p, i FROM digit AS part;

This executes just as fast as a table and the addition "AS part"  
conveniently appears in query plan explanations.

It is just the outer join that is treated differently:

   CREATE TABLE t (x);
   EXPLAIN QUERY PLAN SELECT * FROM t LEFT OUTER JOIN digit ON z=x;
0|0|0|SCAN TABLE t
0|1|1|SEARCH TABLE digit USING INTEGER PRIMARY KEY (rowid=?)

   EXPLAIN QUERY PLAN SELECT * FROM t LEFT OUTER JOIN part ON q=x;
1|0|0|SCAN TABLE digit AS part
0|0|0|SCAN TABLE t
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (p=?)

This is indeed well documented in rule 3 of 
.
I also wonder if this rule could be relaxed.
May be a query that does not include any of the special cases  
mentioned in the document,  might be tolerated.
Or may be for a casual reader this things look easier than they are.

Thanks, E. Pasma

[sqlite] Select values from a time series spaced at least a mininum distance apart

[E.Pasma]

20 nov 2015, 09:19, Clemens Ladisch:

> E.Pasma wrote:
>> An aggregate function can still be used in a sub-query for a column  
>> value.  For the example with integers:
>>
>> WITH RECURSIVE
>> breaks(t) AS (
>>   SELECT 1
>>   UNION
>>   SELECT (SELECT min(x) FROM test WHERE x > t + 2 AND x < 10)
>>   FROM   breaks
>> )
>> SELECT t FROM breaks
>> ;
>
> In SQLite, a scalar subquery returns NULL when nothing is found; this
> needs to be filtered out in the outermost query:
>
> WITH RECURSIVE breaks(t) AS (
>  SELECT min(x) FROM test
>  UNION
>  SELECT (SELECT min(x)
>  FROM test
>  WHERE x > t + 2)
>  FROM breaks
> )
> SELECT t
> FROM breaks
> WHERE t IS NOT NULL;


OK. We can also filter the NULL straight away within the CTE:

WITH RECURSIVE
  breaks(t) AS (
SELECT 1
UNION
SELECT (SELECT min(x) FROM test WHERE x > t + 2 AND x < 10) AS t2
FROM   breaks
WHERE  t2 IS NOT NULL
  )
SELECT t FROM breaks

[sqlite] Select values from a time series spaced at least a mininum distance apart

[E.Pasma]

19 nov 2015, 11:20 Ilja Heckmann:

> I have a dataset of events with timestamps, and want to extract a  
> subset of them so that there is at least, say, an hour between items  
> in the result. It would be trivial to solve this in an imperative  
> language, e.g. the solution in Python would be:
>
> result = [table[0]]
> for row in table[1:]:
>if row['timestamp'] >= result[-1]['timestamp'] +  
> timedelta(hours=1):
>result.append(row)
>
> But I would like to have a solution in pure SQL. The closest I came  
> to a solution was this code, with integers instead of datetimes, and  
> a minimum distance of 3:
>
> CREATE TABLE test (x INT)
> INSERT INTO test VALUES(1)
> INSERT INTO test VALUES(3)
> INSERT INTO test VALUES(5)
> INSERT INTO test VALUES(6)
> INSERT INTO test VALUES(7)
> INSERT INTO test VALUES(8)
>
> WITH RECURSIVE
>  breaks(t) AS (
>SELECT 1
>UNION
>SELECT min(x) FROM test, breaks WHERE x > t + 2 AND x < 10
>  )
> SELECT t FROM breaks
>
> The result I would expect would be 1,5,8. The above query worked  
> with the sqlite3 executable that ships with OSX (I think, its  
> version is around 3.8.11), but when a tried it on a different  
> machine with 3.9.2, there was an error having to do with aggregate  
> function (min) in the recursive subquery.

An aggregate function  can still be used in a sub-query for a column  
value.  For the example with integers:

WITH RECURSIVE
  breaks(t) AS (
SELECT 1
UNION
SELECT (SELECT min(x) FROM test WHERE x > t + 2 AND x < 10)
FROM   breaks
  )
SELECT t FROM breaks
;


> The solution I thought would work was
>
> WITH RECURSIVE
>  breaks(t) AS (
>SELECT 1
>UNION
>SELECT x FROM test, breaks WHERE x > t + 2 LIMIT 1
>  )
> SELECT t FROM breaks
>
> with the reasoning being that the recursive subquery would only  
> return the first value that satisfies the condition of being greater  
> than the last one plus 2, but "LIMIT 1" gets applied the the whole  
> statement instead of just the subquery, limiting the output to just  
> one item.
>
> A solution can be approximated by grouping items into bins and then  
> selecting one value from each bin, but this will lead to some wrong  
> results, e.g. in the example above, if we build bins by the division  
> result of 3, and then take the smallest value from each bin, we'll  
> have:
>
> [1,3], [5,6], [7,8] -> 1,5,7
>
> with 5 and 7 being too close to each other.
>
> Can this be done?
I don't know and have no experience with dates in SQLite.

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

[E.Pasma]

op 18-11-2015 21:23 schreef Richard Hipp op drh at sqlite.org:

> On 11/18/15, Nico Williams  wrote:
>> Consider two tables with the same columns and primary keys, and then
>> consider this query:
>> 
>> SELECT 'added., a.* FROM
>> (SELECT a.* FROM a EXCEPT SELECT b.* FROM b) a
>> UNION ALL
>> SELECT 'deleted', d.* FROM
>> (SELECT b.* FROM b EXCEPT SELECT a.* FROM a) d;
>> 
>> If only the optimizer could recognize
>> this pattern, then it could surely do as well as diff(1).  This sort of
>> query strikes me as a common enough kind of query that implementing such
>> an optimization might be worthwhile, though the SQLite3 team will have
>> better knowledge of that.  (No, Postgres 9.4.4 doesn't seem to optimize this
>> substantially better either.)
>> 
> 
> If it's so common, why are you the first to request it :-)  And, yeah,
> according to the WWPD principal, if Postgres doesn't do it, we
> probably won't be in a big hurry to do it either...
> 
> Note that you can get efficient performance by tweaking the query slightly.
> 
> The "sqldiff.exe" command-line utility has an (undocumented and
> unsupported) "--debug 2" option that will show you the SQL that it
> uses to do a diff between two tables.  Maybe look at that and tweak it
> for your use?
> 
Does that look like this:
SELECT * FROM
(
SELECT 'added' AS what, a.* FROM a
UNION ALL 
SELECT 'deleted', b.* FROM b
)
GROUP BY 
HAVING COUNT (*)<>2
;

[sqlite] Array or set type or some other indexable data?

[E.Pasma]

op 12-11-2015 17:35 schreef J Decker op d3ck0r at gmail.com:
> On Thu, Nov 12, 2015 at 7:16 AM, E.Pasma  wrote:
>> 12 nov 2015, om 07:02, J Decker:
>> 
>>> So I've used CTE to solve a simple problem... I'm tempted to use it to
>>> fix more problems... but I'm wondering how to select different values
>>> at different levels.  I know there's like 'select * from table where
>>> column in ( set,of,things) ' but can I index into the set?  is there
>>> some sort of way I can specify an array of values?
>>> 
>>> would like to do something like ' select value from option_map where
>>> name is ["system","device","com port", "1" ] '  where the program
>>> statement would look more like
>>> 
>>> GetOptionValue ( "/system/device/com port/1" ) where the name is
>>> really sort of registry like and variable in length...
>>> 
>>> I could probably do some sort of indexing passing that exact string
>>> through and parsing in SQL the substrings of interest based on the
>>> level of the query... but would rather preparse the string.
>> 
>> 
>> Below is another possible answer. This uses a recursive cte to split an
>> input full path name into seperate names.
...
>> with walk as (
...
>>
> 
> was hoping to not have to do the substr part in the query
> and would like the path to be more on the external usage of 'walk' in
> this case than inside the expression
> 
So something like "select value from option_map_view where path is "?
A path name like '/system/device/com port/1' is used as an array of names
here. Only the indexing with intst and substr is laborious. Maybe some
future SQLite version includes a group_split function to make this easier.
It seems impossible to me in plain SQL to write an efficient view for a
query like this. As you say below the whole map need to be walked at each
query to find a match.
User defined functions, virtual tables or function based indexes may offer
an efficient solution.

> would be better than building up the address to get a comparison at
> the end since the whole map would have to be walked.
> 

[sqlite] Array or set type or some other indexable data?

[E.Pasma]

12 nov 2015, om 07:02, J Decker:

> So I've used CTE to solve a simple problem... I'm tempted to use it to
> fix more problems... but I'm wondering how to select different values
> at different levels.  I know there's like 'select * from table where
> column in ( set,of,things) ' but can I index into the set?  is there
> some sort of way I can specify an array of values?
>
> would like to do something like ' select value from option_map where
> name is ["system","device","com port", "1" ] '  where the program
> statement would look more like
>
> GetOptionValue ( "/system/device/com port/1" ) where the name is
> really sort of registry like and variable in length...
>
> I could probably do some sort of indexing passing that exact string
> through and parsing in SQL the substrings of interest based on the
> level of the query... but would rather preparse the string.


Below is another possible answer. This uses a recursive cte to split  
an input full path name into seperate names.
create table map (id, name, parent);
insert into map values
(0, '',''),
(1, 'system', 0),
(2, 'device', 1),
(3, 'com port', 2),
(4, '1', 3),
(5, '2', 3)
;
with walk as (
 select  0 as parent, --root
 '/system/device/com port/1' as path
 union
 select  id,
 substr(path,length(name)+2)
 fromwalk
 joinmap using(parent)
 where   name = substr(path,2,instr(substr(path,2)||'/','/')-1)  -- 
oef
 )
select parent as id from walk
where   path='' -- nothing left
;

[sqlite] crash

[E.Pasma]

12 okt 2015, om 20:22, R.Smith:

>
> On 2015-10-12 07:40 PM, Richard Hipp wrote:
>> On 10/12/15, R.Smith  wrote:
>>> More explicitly - would these be valid queries:
>>>
>>> SELECT props.*
>>>FROM (
>>>  SELECT  1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION  
>>> ALL
>>>  SELECT  2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
>>>  SELECT  3, JSON_OBJECT('i',7,'n','John')
>>> ) AS T1, JSON_EACH(T1.obj) AS props
>>> WHERE 1
>>>
>> Yes.
>>
>>> and in future (without JSON):
>>>
>>> SELECT B.* FROM
>>> (SELECT (C1 || C2 || C3) AS CX FROM T) AS A,
>>> (SELECT CX FROM A) AS B
>>> WHERE 1
>>>
>> This is not valid, and has never been valid.  I'm sorry, but I don't
>> understand the connection to the previous query.  They are completely
>> different, as far as I can see.
>
> Yes, I guess what I am after is quantifying the difference. In The  
> first query, an alias to a completely made-up table is passed to a  
> table-valued function (the T1 alias) and it knows to reference/read  
> from that made up table designated as T1.
>
> In the second query, a completely made up table A is used in a  
> second table definition (but this time not a table-valued function -  
> which is the only real difference) to produce B.
>
> Of course they are not the same, and the first works but the second  
> doesn't - so is it ONLY because the first is a Table-valued function  
> (and thus will this be common workings for Table-valued functions  
> henceforth), or perhaps it has to be an eponymous virtual table, or  
> is it specifically because it's a JSON function which allows this  
> uniquely?
>
> I think/hope this will be common workings for all table-valued  
> functions - this is essentially what I'm asking.
>
> (The other question about whether this would work with normal table  
> functions is already answered and understood to be: Never).
>
> Thank you kindly,
> Ryan
Hello, I still wish to add something and would like to hear if I'm  
wrong.
The question if table aliasses are visible within json functions  
becomes easier when using an alternative notation. It looks as if  
table-like functions do have "column-like" parameters that can be  
passed in regular WHERE clauses.
The common notation:
 json_each(t1.obj) as props
is then:
json_each as props where props.json=t1.obj
Note that I'm uninvolved and only found this while experimenting with  
SQLite 3.9.0.
Ryan Smith's example query:

SELECT t1.*, props.*
  FROM (
SELECT  1 as id, JSON_OBJECT('i',5,'n','James') AS obj UNION ALL
SELECT  2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT  3, JSON_OBJECT('i',7,'n','John')
) AS t1
INNER JOIN JSON_EACH(t1.obj) AS props

can be written as

SELECT t1.id, props.*
  FROM (
SELECT  1 as id, JSON_OBJECT('i',5,'n','James') AS json UNION ALL
SELECT  2, JSON_OBJECT('i',6,'n','Jill') UNION ALL
SELECT  3, JSON_OBJECT('i',7,'n','John')
) AS t1
JOIN (SELECT '$.n' AS root) t2
JOIN JSON_EACH AS props
WHERE props.json=t1.json

and that should clear the initial question marks.
I assume it remains so in future versions but actually don't care.
Regards, Edzard.

[sqlite] trying a recursive view with version 3.9.0

[E.Pasma]

11 okt 2015, 17:41, R.Smith:

> On 2015-10-11 05:23 PM, E.Pasma wrote:
>> 11 okt 2015, om 15:27, R.Smith:
>>>
>>> You cannot reference a View within itself - this is what  
>>> circularly means. This is very different to being able to  
>>> reference a table that doesn't exist yet.
>>
>>
>> That was only meant as an introduction. My only real point is that  
>> the particular error is not always raised.
>> Here is a more to the point example:
>>
>> SQLite version 3.8.12 2015-10-07 13:24:27
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> create view v as select c+1 from v;
>> sqlite> select *from v;
>> Error: view v is circularly defined
>> sqlite> create view v2(c) as select c+1 from v2;
>> sqlite> select *from v2;
>> Segmentation fault
>> $
>>
>> Sorry for being too verbose in the original mail.
>
> You were not too verbose.
>
> The problem is still the same - You still cannot reference a View  
> within itself - that is still what Circularly means. It is still  
> very different to referencing a not-yet-existing table.
>
> Open Excel or Calc.
> Go to Cell A1 - type 100 in there.
> Now go to Cell A2, and type in there a function like this:
> = A1 + A2
>
> When you try to commit it, an error will pop up saying you cannot  
> use a circular reference - meaning you cannot compute the value of  
> A2 by using A2 itself as one of the source fields (which will cause  
> an infinite loop). This is exactly the same reason you cannot create  
> a view V by a query that uses fields from within V itself. So it is  
> not that you are simply trying to read a table that does not exist  
> yet, it is that you are reading the same table that you are creating  
> right now - which, as I've mentioned, is a very different thing.
>
> To put it differently: V is not /missing/ at the time of creation,  
> it is /self-referencing/ - the first is allowed, the second is not.
>
> What in the documentation for 3.9.0 makes you think this should work?
>
>
> (Apologies for the long-winded explanation, but the short one missed  
> a trick).
>
Hello again, sorry for not highlighting the Segmentation Fault. In  
WIndows it is likely a dr Watson case and in unix a bus error. The  
segmentation fault comes in the bash unix shelll from OS X.
And in terms of spreadsheets the issue is that such error MAY occur  
when computing A2 from some formula including A2.
I would not even care if it occured consistently, but it only occurs  
when the column names of the views are given after the view's name.  
Back in terms of SQL again.
E. Pasma

[sqlite] trying a recursive view with version 3.9.0

[E.Pasma]

11 okt 2015, om 15:27, R.Smith:
>
> On 2015-10-11 03:14 PM, E.Pasma wrote:
>> Hello,
>>
>> One of the expected changes in 3..9.0 is:
>> A view may now reference undefined tables and functions when  
>> initially created. Missing tables and functions are reported when  
>> the VIEW is used in a query.
>> I could not resist trying a recursive view like in a CTE:
>>
>> SQLite version 3.8.12 2015-10-07 13:24:27
>> create view v as select 1 as c union all select c+1 from v where  
>> c<10;
>> select c from v;
>> Error: view v is circularly defined
>>
>> So this is clearly not intended.
>
> You cannot reference a View within itself - this is what circularly  
> means. This is very different to being able to reference a table  
> that doesn't exist yet.


That was only meant as an introduction. My only real point is that the  
particular error is not always raised.
Here is a more to the point example:

SQLite version 3.8.12 2015-10-07 13:24:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view v as select c+1 from v;
sqlite> select *from v;
Error: view v is circularly defined
sqlite> create view v2(c) as select c+1 from v2;
sqlite> select *from v2;
Segmentation fault
$

Sorry for being too verbose in the original mail.

[sqlite] trying a recursive view with version 3.9.0

[E.Pasma]

Hello,

One of the expected changes in 3..9.0 is:
A view may now reference undefined tables and functions when initially  
created. Missing tables and functions are reported when the VIEW is  
used in a query.
I could not resist trying a recursive view like in a CTE:

SQLite version 3.8.12 2015-10-07 13:24:27
create view v as select 1 as c union all select c+1 from v where c<10;
select c from v;
Error: view v is circularly defined

So this is clearly not intended.

One minor shortcoming however. If combined with the other new feature:
The CREATE VIEW statement now accepts an optional list of column names  
following the view name.
the clarifying error message no longer appears and instead comes a  
kind of bus error. I suppose this should be fixed.

I am still puzzled by this error message (view %s is circularly  
defined) itself as it already exists in earlier sqlite versions. But I  
can not make it appear there. Above case raises  "no such table:  
main.v".

Thanks, Edzard

[sqlite] Performance issue with CTE

[E.Pasma]

Op 1 okt 2015, om 04:10 heeft Philippe Riand wrote:

> I have a table with 500,000+ records. The table has a date column,  
> that I?m using to sort my queries (the columns has an index). Simple  
> queries on the table work very well, using ORDER BY, LIMIT & OFFSET.  
> I?m actually extracting ?pages? of rows that I?m displaying in a web  
> page. Great!.
>
> Now, instead of a simple select, I?d like to execute a recursive  
> query using CTE, because the rows are organized in an hierarchy. And  
> I?m only interested by a page, let's say the first n root records.
> Unfortunately, the doc says that the ?initial-select? in a recursive  
> common table exception may not include ORDER BY, LIMIT or OFFSET. As  
> a result SQLIte probably scans the whole table, which leads to very  
> poor performance? With other databases (like PostgreSQL), I don?t  
> have the problem because they accept ORDER BY, LIMIT and OFFSET on  
> the initial-select, which limits the scan for the initial-select.
>
> What would be the proper solution with SQLite? One would involve a  
> first query that selects the initial row ids, and then pass them as  
> a condition to the initial-select in the recursive. But does anyone  
> has a better proposal?

Is the documentation really right? Yes, it is a syntax error to write  
ORDER BY or LIMIT before UNON.
Semantically however there is no limitation. The ORDER BY must be  
inside a subquery (an inline view):

select * from (select * from t order by k)
union ...

My SQLite version is 3.6.11