[sqlite] How to use ORDER BY on FTS5 table ?
Have a fts5 table with 2 indexed columns. Where the idea is to match by one col and sort using the other one. Something like : "select id from fts where col1 match '50' order by price " This is slow. 0.07 seconds. Removing the order by clause - 0.001 seconds. How do I fix this ? I have a feeling I am using this fts table in an incorrect way. One way is to run 2 queries. First on the fts table, to return ids. Second on the regular table with the order by clause. " select * from normaltable where id in (Ids) order by price " . This approach is fast. But the id list could be large sometimes. Any other way ? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
It's a long story, but that project is done. It's a reporting tool. It worked
well, until the process changed. I will keep it in mind for future projects...
:-)
From: Igor Korot
Sent: Friday, April 5, 2019 02:55 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
satisfy all of the query
Hi,
On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera wrote:
>
>
> Thanks, Simon. Works like a charm...
Unless backwards compatibility is important (do you expect to go back to
pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that...
Thank you.
>
>
> From: Simon Davies
> Sent: Friday, April 5, 2019 12:24 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
> satisfy all of the query
>
> On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
> >
> > Greetings.
> >
> > I have a few tables that I am bringing data from, but I found a bug in my
> > logic, which I am trying to see if I can make it work. Please look at this
> > scenario
> >
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
> > '2019-02-13');
> >
> > select * from t;
> >
> > create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> > insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
> > '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
> > '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
> > '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
> > '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
> > '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
> > '2019-02-18');
> >
> > select * from z;
> >
> > I can do this,
> >
> > select
> > a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p001'
> > AND
> > a.idate = (select max(idate) from t where a = a.a)
> > AND
> > b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > and get the correct output,
> >
> > 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
> >
> > without any problem. But, when I do this,
> >
> > insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5,
> > '2019-03-01');
> >
> > and then do this,
> >
> > select
> > a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p006'
> > AND
> > a.idate = (select max(idate) from t where a = a.a)
> > AND
> > b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > I get nothing. I know why, but what will save my logic is, to be able to
> > fix the query above and get something like this,
> >
> > 16|p006|e|8|n|5
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
Hi,
On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera wrote:
>
>
> Thanks, Simon. Works like a charm...
Unless backwards compatibility is important (do you expect to go back to
pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that...
Thank you.
>
>
> From: Simon Davies
> Sent: Friday, April 5, 2019 12:24 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
> satisfy all of the query
>
> On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
> >
> > Greetings.
> >
> > I have a few tables that I am bringing data from, but I found a bug in my
> > logic, which I am trying to see if I can make it work. Please look at this
> > scenario
> >
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
> > '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
> > '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
> > '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
> > '2019-02-13');
> >
> > select * from t;
> >
> > create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> > insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
> > '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
> > '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
> > '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
> > '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
> > '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
> > '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
> > '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
> > '2019-02-18');
> >
> > select * from z;
> >
> > I can do this,
> >
> > select
> > a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p001'
> > AND
> > a.idate = (select max(idate) from t where a = a.a)
> > AND
> > b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > and get the correct output,
> >
> > 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
> >
> > without any problem. But, when I do this,
> >
> > insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5,
> > '2019-03-01');
> >
> > and then do this,
> >
> > select
> > a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p006'
> > AND
> > a.idate = (select max(idate) from t where a = a.a)
> > AND
> > b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > I get nothing. I know why, but what will save my logic is, to be able to
> > fix the query above and get something like this,
> >
> > 16|p006|e|8|n|5|2019-03-01|||
> >
> > in other words, NULL values instead. This will fix my "logic". :-) and the
> > world will be at peace again. :-) Is this even possible? Thanks.
>
> left join:
>
> select
> a.*, b.* from t as a left join z as b on a.a = b.f
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idat
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
A request for a teachable moment.. :-)
Why does this work,
select
a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;
and this one does not,
select
a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
where a.a = 'p006'
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
ORDER BY a.a
;
Thanks.
From: sqlite-users on behalf of
Jose Isaias Cabrera
Sent: Friday, April 5, 2019 02:36 PM
To: Simon Davies; SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
satisfy all of the query
Thanks, Simon. Works like a charm...
From: Simon Davies
Sent: Friday, April 5, 2019 12:24 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
satisfy all of the query
On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my
> logic, which I am trying to see if I can make it work. Please look at this
> scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
> '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
> '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem. But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5,
> '2019-03-01');
>
> and then do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing. I know why, but what will save my logic is, to be able to fix
> the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||
>
> in other words, NULL
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
Thanks, Simon. Works like a charm...
From: Simon Davies
Sent: Friday, April 5, 2019 12:24 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not
satisfy all of the query
On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my
> logic, which I am trying to see if I can make it work. Please look at this
> scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
> '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
> '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem. But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5,
> '2019-03-01');
>
> and then do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing. I know why, but what will save my logic is, to be able to fix
> the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||
>
> in other words, NULL values instead. This will fix my "logic". :-) and the
> world will be at peace again. :-) Is this even possible? Thanks.
left join:
select
a.*, b.* from t as a left join z as b on a.a = b.f
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;
> josé
Regards,
Simon
___
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] Returning NULL or empty values when the SELECT does not satisfy all of the query
Folks, this is called a "Teachable Moment", by James K. Lowden. ;-) From: James K. Lowden Sent: Friday, April 5, 2019 12:31 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query On Fri, 5 Apr 2019 14:01:20 + Jose Isaias Cabrera wrote: > The owners of the business said that "there will never be...", The perfect opportunity for a CHECK constraint or to enforce a foreign key. --jkl ___ 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] Prepared Statement Without a Database Instance
On 5 Apr 2019, at 7:07pm, Lee, Jason wrote: > Is there any way to create a prepared statement without an associated > database instance? I have one fixed insert that I am running on many separate > databases that are inserting different data sets, and would like to amortize > the cost of compiling the insert if possible. Sorry, but part of the preparation process includes looking up details about the database to work out the best way to do things. Knowing the text of the SQL statement isn't enough. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prepared Statement Without a Database Instance
Hi. Is there any way to create a prepared statement without an associated database instance? I have one fixed insert that I am running on many separate databases that are inserting different data sets, and would like to amortize the cost of compiling the insert if possible. Jason Lee ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
Stop On Fri, Apr 5, 2019, 11:31 AM James K. Lowden wrote: > On Fri, 5 Apr 2019 15:45:10 +0300 > Arthur Blondel wrote: > > > The data is always the same. That's why removing one row should be > > enough to insert a new one. > > My problem is that some times I need to remove many rows to add one > > new one. > > SQLite *could* avoid that problem by pre-allocating space in the > journal sufficient to permit a single row to be deleted. But it's not > obvious to me that the complexity is worth it, given the size of disks > these days and consequent rarity of the problem. > > If I were in your shoes, I'd consider maintaining a "dummy" file that's > expendable in the event of a SQLITE_FULL error. > > Compute how much space SQLite needs to delete a row. Maybe double that > for safety's sake. Create a file that size, and fill it with deadbeef > just to be sure. Write functions to create and delete that file, > because you'll want to do it consistently. > > When you encounter SQLITE_FULL, delete the file, do the deed, and > re-create the file. If you can't recreate the file, you have an > unrecoverable error, but an intact database. > > It's not a perfect solution. To guard against other processes seizing > the space while you're trying to use it, you'd have to wall off the > space, maybe with a loopback filesystem. But it'd get you further down > the road than you are now. > > --jkl > ___ > 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] Remove row to insert new one on a full database
On Apr 5, 2019, at 12:31 PM, James K. Lowden wrote: > > On Fri, 5 Apr 2019 15:45:10 +0300 > Arthur Blondel wrote: > >> The data is always the same. That's why removing one row should be >> enough to insert a new one. >> My problem is that some times I need to remove many rows to add one >> new one. > > SQLite *could* avoid that problem by pre-allocating space in the > journal sufficient to permit a single row to be deleted. But it's not > obvious to me that the complexity is worth it, given the size of disks > these days and consequent rarity of the problem. > > If I were in your shoes, I'd consider maintaining a "dummy" file that's > expendable in the event of a SQLITE_FULL error. > > Compute how much space SQLite needs to delete a row. Maybe double that > for safety's sake. Create a file that size, and fill it with deadbeef > just to be sure. Write functions to create and delete that file, > because you'll want to do it consistently. > > When you encounter SQLITE_FULL, delete the file, do the deed, and > re-create the file. If you can't recreate the file, you have an > unrecoverable error, but an intact database. > > It's not a perfect solution. To guard against other processes seizing > the space while you're trying to use it, you'd have to wall off the > space, maybe with a loopback filesystem. But it'd get you further down > the road than you are now. > > --jkl > First, the OP has indicated that the FULL message isn’t because the disk is out of space, but they have done something to put a hard limit on the size of the database, so there is room to create the journal to delete the row as there is room for the journal file. Second, I am not sure SQLite can now exactly how much space will be needed to delete any arbitrary row in the database (or at least be able to figure it out cheaply). Remember it needs to save everything that is going to be changed, including the indexes. Also, deleting one row may not actually free up any useful space, as has been shown, though if you can delete one row, you could commit that transaction and then delete another (though I can’t be sure if there could be a corner case where deleting a row might increase the size of the database, maybe some trigger fires???) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite3 Decimal Extension
On 2019-04-03 11:44, Warren Young wrote: > As for the non-ASCII characters, they're UTF-8, which is the de facto > standard character set on the Internet since around the time of The > Bubble. Ignoring the embedded world, I can't think of an in-support > OS that doesn't have built-in support for UTF-8. The only place I'd > caution against using such characters is in printf() output and such, > and then only because the Windows Console defaults to UTF-16LE. The LANG and LC_* environment variables exist for a reason. -- Please don't Cc: me privately on mailing lists and Usenet, if you also post the followup to the list or newsgroup. To reply privately _only_ on Usenet and on broken lists which rewrite From, fetch the TXT record for no-use.mooo.com. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
On Fri, 5 Apr 2019 15:45:10 +0300 Arthur Blondel wrote: > The data is always the same. That's why removing one row should be > enough to insert a new one. > My problem is that some times I need to remove many rows to add one > new one. SQLite *could* avoid that problem by pre-allocating space in the journal sufficient to permit a single row to be deleted. But it's not obvious to me that the complexity is worth it, given the size of disks these days and consequent rarity of the problem. If I were in your shoes, I'd consider maintaining a "dummy" file that's expendable in the event of a SQLITE_FULL error. Compute how much space SQLite needs to delete a row. Maybe double that for safety's sake. Create a file that size, and fill it with deadbeef just to be sure. Write functions to create and delete that file, because you'll want to do it consistently. When you encounter SQLITE_FULL, delete the file, do the deed, and re-create the file. If you can't recreate the file, you have an unrecoverable error, but an intact database. It's not a perfect solution. To guard against other processes seizing the space while you're trying to use it, you'd have to wall off the space, maybe with a loopback filesystem. But it'd get you further down the road than you are now. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
On Fri, 5 Apr 2019 14:01:20 + Jose Isaias Cabrera wrote: > The owners of the business said that "there will never be...", The perfect opportunity for a CHECK constraint or to enforce a foreign key. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera wrote:
>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my
> logic, which I am trying to see if I can make it work. Please look at this
> scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
> '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
> '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
> '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
> '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
> '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
> '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem. But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5,
> '2019-03-01');
>
> and then do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
> a.idate = (select max(idate) from t where a = a.a)
> AND
> b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing. I know why, but what will save my logic is, to be able to fix
> the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||
>
> in other words, NULL values instead. This will fix my "logic". :-) and the
> world will be at peace again. :-) Is this even possible? Thanks.
left join:
select
a.*, b.* from t as a left join z as b on a.a = b.f
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;
> josé
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
> On 4/5/19 11:14 AM, Arthur Blondel wrote: > > I have enough disk space. I just limit the database file size artificially > > for testing purpose as you can see. > > There is no problem of privilege and there is nothing else than the code I > > sent. No other access to the DB. > > I'm using sqlite 3.16.2 > > As has been pointed out, one issue is that not all records, even if the > 'same' take the same space, so deleting one record may not make enough > room for another. > And I would also point out, the data for each row is not the same. The id is incrementing. So id 4000 may not be able to be put on the same page as the id 1 you have just deleted. Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
On 4/5/19 11:14 AM, Arthur Blondel wrote: > I have enough disk space. I just limit the database file size artificially > for testing purpose as you can see. > There is no problem of privilege and there is nothing else than the code I > sent. No other access to the DB. > I'm using sqlite 3.16.2 As has been pointed out, one issue is that not all records, even if the 'same' take the same space, so deleting one record may not make enough room for another. Another issue is that for indexes, not all free space are the same, indexes keep similar values together in the index, so adding a row may need to find related space for an index, or you need to delete enough rows to either open space where needed or to free a full page of the index to let that page be used in the newly needed space for the index. I will admit that these are in a way esoteric implementation dependent details, so might not seem obvious, but they do explain the 'strangeness' that you see. Many data structures when running at a capacity limit can demonstrate these sorts of strangeness. Trying to tightly control resource usage is a tricky problem, and sometimes you need to think carefully about what you goal actually is (not what solution you think will work). Putting your hard limit on the base size of the database does put a hard limit on the size (in bytes) of the database, but may provide an unexpectedly low capacity of records in the worse case (and running data structures at this sort of limit tends to create at times conditions close to worse case), at the cost that the time to insert a record can grow significantly. If you really have plenty of disk space, than establishing a record limit in the database, and when you are at it removing one record for every record added, will smooth out the access time, at the cost of possibly higher disk usage at times (but maybe a better ratio of size per records). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
I have enough disk space. I just limit the database file size artificially
for testing purpose as you can see.
There is no problem of privilege and there is nothing else than the code I
sent. No other access to the DB.
I'm using sqlite 3.16.2
On Fri, Apr 5, 2019 at 3:59 PM Chris Locke wrote:
> Arthur - are you running SQLite in parallel runs?
> If you access the database file using the sqlite3 command-line tool, and
> try to execute the same SQL commands, do you get the same error ?
>
> SQLite makes a temporary 'journal' file while it's working. I think that,
> on your platform, by default it will be in the same directory as the
> database file. Does your application have enough privileges to create new
> files in that directory ?
> What version of SQLite are you using? It might be an old version.
> Also, removing rows doesn't necessarily remove space in the database file.
> If you're running out of disk space, it could be you need to vacuum your
> database file. How large is the database? How much disk space do you have
> left?
>
>
> Thanks,
> Chris
>
> On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel
> wrote:
>
> > OK, I wasn't clear.
> > I'm limited in space so when the DB is full (when sqlite3_exec() returns
> > SQLITE_FULL when I try to insert a new row), I remove the oldest row and
> > retry to insert the new one.
> > The data is always the same. That's why removing one row should be enough
> > to insert a new one.
> > My problem is that some times I need to remove many rows to add one new
> > one.
> > This is basically my code:
> >
> > main()
> > {
> > sqlite3* db;
> > int rc;
> > char *err_msg = 0;
> > int counter;
> > bool full = false;
> > int id;
> >
> > /* --- Create DB --- */
> > rc = sqlite3_open("db_file.db", &db);
> > printf("1. rc = %d\n", rc);
> >
> > rc = sqlite3_exec(db,
> > "CREATE TABLE IF NOT EXISTS data_table"
> > "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER,
> col2
> > INTEGER, col3 INTEGER)",
> > 0, 0, &err_msg);
> > printf("2. rc = %d\n", rc);
> >
> > /* --- Limit database size to 50 K --- */
> > rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
> > printf("3. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
> > printf("4. rc = %d\n", rc);
> > rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
> > printf("5. rc = %d\n", rc);
> >
> >
> > /* --- Fill DB --- */
> > for (int i = 0 ; i < 5000 ; i++) {
> > counter = 0;
> > do {
> > rc = sqlite3_exec(db,
> > "INSERT INTO data_table"
> > "(col1, col2, col3) VALUES(1, 2, 3)",
> > 0, 0, &err_msg);
> >
> > if (rc == SQLITE_FULL) {
> > if (!full) {
> > printf("%d - DB full\n", id);
> > full = true;
> > }
> > counter++;
> > // delete oldest row
> > int stat = sqlite3_exec(db,
> > "DELETE FROM data_table WHERE id IN "
> > "(SELECT id FROM data_table ORDER BY id
> LIMIT
> > 1)",
> > 0, 0, &err_msg);
> > if (stat != SQLITE_OK) {
> > printf("Delete error %d\n", stat);
> > }
> > } else if (rc == SQLITE_OK) {
> > id = sqlite3_last_insert_rowid(db);
> > } else /*if (rc != SQLITE_OK)*/ {
> > printf("Insert error %d\n", rc);
> > }
> > } while (rc == SQLITE_FULL);
> >
> > if (counter > 2) {
> > printf("%d - %d rows was removed\n", id, counter);
> > }
> > }
> >
> > printf("close -> %d\n", sqlite3_close(db));
> > }
> >
> >
> > Following the output:
> >
> > 1. rc = 0
> > 2. rc = 0
> > 3. rc = 0
> > 4. rc = 0
> > 5. rc = 0
> > 3959 - DB full
> > 3960 - 109 rows was removed
> > 4044 - 92 rows was removed
> > 4128 - 86 rows was removed
> > 4212 - 85 rows was removed
> > 4296 - 85 rows was removed
> > 4380 - 84 rows was removed
> > 4464 - 84 rows was removed
> > 4548 - 84 rows was removed
> > 4632 - 84 rows was removed
> > 4716 - 84 rows was removed
> > 4800 - 84 rows was removed
> > 4884 - 84 rows was removed
> > 4968 - 84 rows was removed
> > close -> 0
> >
> > Thanks
> >
> >
> > On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel
> > wrote:
> >
> > >
> > > Hello
> > >
> > > When I try to insert new data to a full SQLite database, I need to
> remove
> > > much more than really needed. I'm doing the following:
> > >
> > > while(1) {
> > > do {
> > > status = insert_1_row_to_db();
> > > if (status == full) {
> > > remove_one_row_from_db();
> > > }
> > > } while (status == full);}
> > >
> > > The inserted data has always t
Re: [sqlite] ANN: SQLite3 Decimal Extension
On 4/5/19 9:50 AM, Joshua Wise wrote: > Julian dates are definitely floating point numbers, not integers. Julian dates, if being used to represent a time on a given date would be a floating point number. A Julian date, if only needing to express a time to the precision of a whole Day, could be represented as an integer (with some rule to define which of the likely integers you would chose, due to the natural fuzziness of plain dates). Thus Apr 5, 2019 could be expressed as an integer (and in fact to express it as a float requires adding a lot of detail about the date, verse maybe 1 bit of information) -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
Yeah, that is what I am doing now, but that results in two selects, and it's causing me to have to rewrite other pieces of the program. It's a long story... The owners of the business said that "there will never be...", WRONG! So, with that in mind, I wrote the reporting tool. Now, it's missing that record, and I want to leave all of my other SELECTs from other places intact, and I just have to deal with the empty/NULL values, which was already 95% in place. I just have to address a few lines on a bunch of places to address searches, etc... From: Simon Slavin Sent: Friday, April 5, 2019 09:50 AM To: SQLite mailing list Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query On 5 Apr 2019, at 2:45pm, Jose Isaias Cabrera wrote: > then do this, > > select > a.*, b.* from t as a join z as b on a.a = b.f > where a.a = 'p006' > AND > a.idate = (select max(idate) from t where a = a.a) > AND > b.idate = (select max(idate) from z where f = a.a) > ORDER BY a.a > ; For testing purposes, break your SELECT down into single operations. First do select max(idate) from t where a = 'p006' select max(idate) from z where f = 'p006' Are those values the answers you expected ? Then plug those answers into > select > a.*, b.* from t as a join z as b on a.a = b.f > where a.a = 'p006' > AND > a.idate = X > AND > b.idate = Y > ORDER BY a.a and see whether it does what you expect. ___ 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] Returning NULL or empty values when the SELECT does not satisfy all of the query
On 5 Apr 2019, at 2:45pm, Jose Isaias Cabrera wrote: > then do this, > > select > a.*, b.* from t as a join z as b on a.a = b.f > where a.a = 'p006' > AND > a.idate = (select max(idate) from t where a = a.a) > AND > b.idate = (select max(idate) from z where f = a.a) > ORDER BY a.a > ; For testing purposes, break your SELECT down into single operations. First do select max(idate) from t where a = 'p006' select max(idate) from z where f = 'p006' Are those values the answers you expected ? Then plug those answers into > select > a.*, b.* from t as a join z as b on a.a = b.f > where a.a = 'p006' > AND > a.idate = X > AND > b.idate = Y > ORDER BY a.a and see whether it does what you expect. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite3 Decimal Extension
Julian dates are definitely floating point numbers, not integers. On Thu, Apr 4, 2019, 3:37 PM James K. Lowden wrote: > On Thu, 4 Apr 2019 11:21:41 -0400 > Joshua Wise wrote: > > > > On the other hand, what table has a floating point number in its > > > key? > > > > > > How do you even express the value of such a key for an exact > > > match? > > > > Well I imagine it can be very useful for range queries. Imagine > > Julian dates, coordinate points, rankings, etc. > > Julian dates are integers. The tm structure is all integers, too. > > I suppose you could store lat/lon as floating point. It's exactly the > kind of data that calls out of a tm-like structure, though, because > officially there are 60 minutes in a degree, and 60 seconds in a minute. > Just as with time, the governing authorities use a non-decimal > notation; decimal fractions of a degree are mere computational > convienience. And, again, it's not part of the key. > > In financial analysis, range queries over large datasets are common. If > it's not a range of dates, it's a range of > returns/price/earning/capitalization over time. Yet Microsoft SQL > Server never suggested we use anything other than IEEE to store the > data. Perhaps that's because, more often than not, floating point data > are manipulated as part of the query. > > If you're joining the table to itself to select price change over time > to compute, say, variance, the absolute magnitude of the data are > uninteresting. You find the stocks by date, subtract the prices and > compute the variance, in IEEE format, of course, because that's what > the CPU supports. Then you sort and filter the top quintile, or > whatever. In such a case, the overhead of floating-point conversion > will be significant: twice for every row, overhead that is nonexistent > today. > > I'm skeptical of the claimed advantage. The downside is clear. If the > advantage can be shown, its use would be specialized. OTOH, a > compiete BCD implementation would be ... interesting. > > --jkl > > ___ > 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] Returning NULL or empty values when the SELECT does not satisfy all of the query
Greetings.
I have a few tables that I am bringing data from, but I found a bug in my
logic, which I am trying to see if I can make it work. Please look at this
scenario
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
'2019-02-13');
select * from t;
create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
'2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
'2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
'2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
'2019-02-16');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
'2019-02-16');
insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
'2019-02-18');
select * from z;
I can do this,
select
a.*, b.* from t as a join z as b on a.a = b.f
where a.a = 'p001'
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
ORDER BY a.a
;
and get the correct output,
11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
without any problem. But, when I do this,
insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5,
'2019-03-01');
and then do this,
select
a.*, b.* from t as a join z as b on a.a = b.f
where a.a = 'p006'
AND
a.idate = (select max(idate) from t where a = a.a)
AND
b.idate = (select max(idate) from z where f = a.a)
ORDER BY a.a
;
I get nothing. I know why, but what will save my logic is, to be able to fix
the query above and get something like this,
16|p006|e|8|n|5|2019-03-01|||
in other words, NULL values instead. This will fix my "logic". :-) and the
world will be at peace again. :-) Is this even possible? Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
On 5 Apr 2019, at 1:45pm, Arthur Blondel wrote: > I'm limited in space so when the DB is full (when sqlite3_exec() returns > SQLITE_FULL when I try to insert a new row), I remove the oldest row If SQLite returns SQLITE_FULL you cannot reliably do anything else to the database. Because even if your next command is DELETE, SQLite needs to temporarily use /more/ disk space for the journal until that transaction is committed. So your DELETE command can fail too. Do not use this behaviour. Instead monitor the free space on the database volume and delete rows when you have only a little space free. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove row to insert new one on a full database
Ignoring for the moment the issues of journals, logs, etc.
When you delete a record you free up the space it used *on the page that it was
on* (well, pages if there are indexes)
Each page is part of the overall B-tree layout of the table/index. When you go
to add a new record, if it doesn't belong on the page you just made some room
on, then it will look for room on the page it does belong on, or create a new
page to put it on.
Oversimplified example: If you have a full phone book and get an error trying
to add someone with a last name beginning with "B" you can't just remove the
line of someone with a last name beginning with "H". That page is still full of
"H"'s and won't let you add a "B" in there completely out of order. You would
have to remove all of the names on the "H" page before it would become
re-usable for "B" names.
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Arthur Blondel
Sent: Friday, April 05, 2019 8:45 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Remove row to insert new one on a full database
OK, I wasn't clear.
I'm limited in space so when the DB is full (when sqlite3_exec() returns
SQLITE_FULL when I try to insert a new row), I remove the oldest row and
retry to insert the new one.
The data is always the same. That's why removing one row should be enough
to insert a new one.
My problem is that some times I need to remove many rows to add one new one.
This is basically my code:
main()
{
sqlite3* db;
int rc;
char *err_msg = 0;
int counter;
bool full = false;
int id;
/* --- Create DB --- */
rc = sqlite3_open("db_file.db", &db);
printf("1. rc = %d\n", rc);
rc = sqlite3_exec(db,
"CREATE TABLE IF NOT EXISTS data_table"
"(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
INTEGER, col3 INTEGER)",
0, 0, &err_msg);
printf("2. rc = %d\n", rc);
/* --- Limit database size to 50 K --- */
rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
printf("3. rc = %d\n", rc);
rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
printf("4. rc = %d\n", rc);
rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
printf("5. rc = %d\n", rc);
/* --- Fill DB --- */
for (int i = 0 ; i < 5000 ; i++) {
counter = 0;
do {
rc = sqlite3_exec(db,
"INSERT INTO data_table"
"(col1, col2, col3) VALUES(1, 2, 3)",
0, 0, &err_msg);
if (rc == SQLITE_FULL) {
if (!full) {
printf("%d - DB full\n", id);
full = true;
}
counter++;
// delete oldest row
int stat = sqlite3_exec(db,
"DELETE FROM data_table WHERE id IN "
"(SELECT id FROM data_table ORDER BY id LIMIT
1)",
0, 0, &err_msg);
if (stat != SQLITE_OK) {
printf("Delete error %d\n", stat);
}
} else if (rc == SQLITE_OK) {
id = sqlite3_last_insert_rowid(db);
} else /*if (rc != SQLITE_OK)*/ {
printf("Insert error %d\n", rc);
}
} while (rc == SQLITE_FULL);
if (counter > 2) {
printf("%d - %d rows was removed\n", id, counter);
}
}
printf("close -> %d\n", sqlite3_close(db));
}
Following the output:
1. rc = 0
2. rc = 0
3. rc = 0
4. rc = 0
5. rc = 0
3959 - DB full
3960 - 109 rows was removed
4044 - 92 rows was removed
4128 - 86 rows was removed
4212 - 85 rows was removed
4296 - 85 rows was removed
4380 - 84 rows was removed
4464 - 84 rows was removed
4548 - 84 rows was removed
4632 - 84 rows was removed
4716 - 84 rows was removed
4800 - 84 rows was removed
4884 - 84 rows was removed
4968 - 84 rows was removed
close -> 0
Thanks
On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel
wrote:
>
> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
Re: [sqlite] Query Regression IN and Virtual Tables - followup
On 5/4/62 16:44, Hick Gunter wrote:
I patched my SQlite 3.24 code to include the fix from the ticket
< if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
---
// from SQLite bugfix
if( rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 || bIn) ){
and changed the xBestIndex return value to be lower if the equality constraint
from IN is not usable
The generated code as reported is invalid (instruction 16 with the init of R6
is not shown)
So, after applying the patch to 3.24 you executed the EXPLAIN statement
shown below in the shell tool and it mysteriously omitted instruction 16
from the output?
Are there any other problems? Does the SQL statement return the correct
results if you execute it without the EXPLAIN?
Dan.
explain select lsn from atx_txlog where period_no between 7300 and 7313 and
event_Type in (140001,180001);
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Init 0 16000 Start at 16
1 VOpen 0 0 0 vtab:B90B5000
2 Explain2 0 0 SCAN TABLE atx_txlog VIRTUAL TABLE INDEX
1:
00
3 Integer7300 3 000 r[3]=7300
4 Integer7313 4 000 r[4]=7313
5 Integer1 1 000 r[1]=1
6 Integer2 2 000 r[2]=2
7 VFilter0 151
00 iplan=r[1] zplan='
'
8 Noop 0 0 000 begin IN expr
9 VColumn0 15500 r[5]=vcolumn(15);
atx_txlog.event_type
10 Eq 5 126 (BINARY) 43 if r[6]==r[5] goto
12
11 Ne 5 147 (BINARY) 53 if r[7]!=r[5] goto
14; end IN expr
12 VColumn0 21800 r[8]=vcolumn(21);
atx_txlog.lsn
13 ResultRow 8 1 000 output=r[8]
14VNext 0 8 000
15Halt 0 0 000
17Integer180001 7 000 r[7]=180001
18Goto 0 1 000
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Dan Kennedy
Gesendet: Freitag, 29. März 2019 14:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables
On 29/3/62 14:32, Hick Gunter wrote:
When upgrading from 3.7.14.1 to 3.24 I noticed the following problem
Given a virtual table like
CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER,
attr1 INTEGER,...);
whose xBestIndex function simulates (in unsupported syntax)
CREATE VIRTUAL INDEX vt_key ON vt (key1, key2, key3);
but also handles simple comparisons internally, the query
SELECT * FROM vt WHERE key1 = AND key2 BETWEEN AND
AND attr1 IN ();
Thanks for reporting this.
What is supposed to happen in this scenario is that xBestIndex() is invoked
once with all 4 constraints marked as usable. The IN(...) is represented as an
SQLITE_INDEX_CONSTRAINT_EQ constraint. If the
xBestIndex() implementation elects to use the IN(...) operator, then
xBestIndex() is invoked a second time, this time with the IN(...) marked as not
usable. SQLite evaluates both plans, considering the cost estimates provided by
the virtual table implementation and its own estimate of the cardinality of the
IN(...) operator. And chooses the most efficient plan overall.
There was a bug preventing the second call to xBestIndex() from being made in
some circumstances - including for your query. Now fixed here:
https://sqlite.org/src/info/f5752517f590b37b
So if you upgrade to trunk, or else apply the patch linked above to 3.27.2, and
the virtual table implementation provides relatively accurate cost estimates,
SQLite should make an intelligent decision about which plan to use.
Dan.
SQLite 3.7.14.1 asks for (key1,'='), (key2,'>='), (key2,'<=') and
xBestIndex accepts all 3 constraints yielding query plan
- materialize IN as anonymous ephemeral table
- search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ?)
- retrieve column attr1
- search anonymous ephemeral table
i.e. perform a single partial table scan on vt and check attr1
SQLite3.24 asks for ...,(attr1,'=') and xBestIndex accepts all 4
constraints yielding
- materialize IN () as anonymous ephemeral table
- scan anonymous ephemeral table
- search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ? AND
attr1 = ?)
i.e. perform a partial table scan of vt FOR EACH attr1, which is
slower by the cardinality of the IN list
Fortunately, CTEs come to the rescue:
WITH attrs (attr1) AS (VALUES <(list)>) SELECT * FROM vt CROSS JOIN
attrs a ON (a.attr1 = vt.a
Re: [sqlite] Remove row to insert new one on a full database
Arthur - are you running SQLite in parallel runs?
If you access the database file using the sqlite3 command-line tool, and
try to execute the same SQL commands, do you get the same error ?
SQLite makes a temporary 'journal' file while it's working. I think that,
on your platform, by default it will be in the same directory as the
database file. Does your application have enough privileges to create new
files in that directory ?
What version of SQLite are you using? It might be an old version.
Also, removing rows doesn't necessarily remove space in the database file.
If you're running out of disk space, it could be you need to vacuum your
database file. How large is the database? How much disk space do you have
left?
Thanks,
Chris
On Fri, Apr 5, 2019 at 1:46 PM Arthur Blondel
wrote:
> OK, I wasn't clear.
> I'm limited in space so when the DB is full (when sqlite3_exec() returns
> SQLITE_FULL when I try to insert a new row), I remove the oldest row and
> retry to insert the new one.
> The data is always the same. That's why removing one row should be enough
> to insert a new one.
> My problem is that some times I need to remove many rows to add one new
> one.
> This is basically my code:
>
> main()
> {
> sqlite3* db;
> int rc;
> char *err_msg = 0;
> int counter;
> bool full = false;
> int id;
>
> /* --- Create DB --- */
> rc = sqlite3_open("db_file.db", &db);
> printf("1. rc = %d\n", rc);
>
> rc = sqlite3_exec(db,
> "CREATE TABLE IF NOT EXISTS data_table"
> "(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
> INTEGER, col3 INTEGER)",
> 0, 0, &err_msg);
> printf("2. rc = %d\n", rc);
>
> /* --- Limit database size to 50 K --- */
> rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
> printf("3. rc = %d\n", rc);
> rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
> printf("4. rc = %d\n", rc);
> rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
> printf("5. rc = %d\n", rc);
>
>
> /* --- Fill DB --- */
> for (int i = 0 ; i < 5000 ; i++) {
> counter = 0;
> do {
> rc = sqlite3_exec(db,
> "INSERT INTO data_table"
> "(col1, col2, col3) VALUES(1, 2, 3)",
> 0, 0, &err_msg);
>
> if (rc == SQLITE_FULL) {
> if (!full) {
> printf("%d - DB full\n", id);
> full = true;
> }
> counter++;
> // delete oldest row
> int stat = sqlite3_exec(db,
> "DELETE FROM data_table WHERE id IN "
> "(SELECT id FROM data_table ORDER BY id LIMIT
> 1)",
> 0, 0, &err_msg);
> if (stat != SQLITE_OK) {
> printf("Delete error %d\n", stat);
> }
> } else if (rc == SQLITE_OK) {
> id = sqlite3_last_insert_rowid(db);
> } else /*if (rc != SQLITE_OK)*/ {
> printf("Insert error %d\n", rc);
> }
> } while (rc == SQLITE_FULL);
>
> if (counter > 2) {
> printf("%d - %d rows was removed\n", id, counter);
> }
> }
>
> printf("close -> %d\n", sqlite3_close(db));
> }
>
>
> Following the output:
>
> 1. rc = 0
> 2. rc = 0
> 3. rc = 0
> 4. rc = 0
> 5. rc = 0
> 3959 - DB full
> 3960 - 109 rows was removed
> 4044 - 92 rows was removed
> 4128 - 86 rows was removed
> 4212 - 85 rows was removed
> 4296 - 85 rows was removed
> 4380 - 84 rows was removed
> 4464 - 84 rows was removed
> 4548 - 84 rows was removed
> 4632 - 84 rows was removed
> 4716 - 84 rows was removed
> 4800 - 84 rows was removed
> 4884 - 84 rows was removed
> 4968 - 84 rows was removed
> close -> 0
>
> Thanks
>
>
> On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel
> wrote:
>
> >
> > Hello
> >
> > When I try to insert new data to a full SQLite database, I need to remove
> > much more than really needed. I'm doing the following:
> >
> > while(1) {
> > do {
> > status = insert_1_row_to_db();
> > if (status == full) {
> > remove_one_row_from_db();
> > }
> > } while (status == full);}
> >
> > The inserted data has always the same size. When the database is full,
> > removing only one row is enough to insert the new one. But after a
> while, I
> > need to remove 30, 40 and even more the 100 rows to be able to insert one
> > new row. Is it the correct behavior of SQLite? Is there a way to remove
> > only what is needed and no more? Thanks
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing l
Re: [sqlite] Remove row to insert new one on a full database
OK, I wasn't clear.
I'm limited in space so when the DB is full (when sqlite3_exec() returns
SQLITE_FULL when I try to insert a new row), I remove the oldest row and
retry to insert the new one.
The data is always the same. That's why removing one row should be enough
to insert a new one.
My problem is that some times I need to remove many rows to add one new one.
This is basically my code:
main()
{
sqlite3* db;
int rc;
char *err_msg = 0;
int counter;
bool full = false;
int id;
/* --- Create DB --- */
rc = sqlite3_open("db_file.db", &db);
printf("1. rc = %d\n", rc);
rc = sqlite3_exec(db,
"CREATE TABLE IF NOT EXISTS data_table"
"(id INTEGER PRIMARY KEY AUTOINCREMENT, col1 INTEGER, col2
INTEGER, col3 INTEGER)",
0, 0, &err_msg);
printf("2. rc = %d\n", rc);
/* --- Limit database size to 50 K --- */
rc = sqlite3_exec(db, "PRAGMA page_size=1024", 0, 0, &err_msg);
printf("3. rc = %d\n", rc);
rc = sqlite3_exec(db, "PRAGMA max_page_count=50", 0, 0, &err_msg);
printf("4. rc = %d\n", rc);
rc = sqlite3_exec(db, "VACUUM", 0, 0, &err_msg); // resize file
printf("5. rc = %d\n", rc);
/* --- Fill DB --- */
for (int i = 0 ; i < 5000 ; i++) {
counter = 0;
do {
rc = sqlite3_exec(db,
"INSERT INTO data_table"
"(col1, col2, col3) VALUES(1, 2, 3)",
0, 0, &err_msg);
if (rc == SQLITE_FULL) {
if (!full) {
printf("%d - DB full\n", id);
full = true;
}
counter++;
// delete oldest row
int stat = sqlite3_exec(db,
"DELETE FROM data_table WHERE id IN "
"(SELECT id FROM data_table ORDER BY id LIMIT
1)",
0, 0, &err_msg);
if (stat != SQLITE_OK) {
printf("Delete error %d\n", stat);
}
} else if (rc == SQLITE_OK) {
id = sqlite3_last_insert_rowid(db);
} else /*if (rc != SQLITE_OK)*/ {
printf("Insert error %d\n", rc);
}
} while (rc == SQLITE_FULL);
if (counter > 2) {
printf("%d - %d rows was removed\n", id, counter);
}
}
printf("close -> %d\n", sqlite3_close(db));
}
Following the output:
1. rc = 0
2. rc = 0
3. rc = 0
4. rc = 0
5. rc = 0
3959 - DB full
3960 - 109 rows was removed
4044 - 92 rows was removed
4128 - 86 rows was removed
4212 - 85 rows was removed
4296 - 85 rows was removed
4380 - 84 rows was removed
4464 - 84 rows was removed
4548 - 84 rows was removed
4632 - 84 rows was removed
4716 - 84 rows was removed
4800 - 84 rows was removed
4884 - 84 rows was removed
4968 - 84 rows was removed
close -> 0
Thanks
On Thu, Apr 4, 2019 at 9:07 AM Arthur Blondel
wrote:
>
> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Regression IN and Virtual Tables - followup
I patched my SQlite 3.24 code to include the fix from the ticket
< if( rc==SQLITE_OK && (mBest = (pNew->prereq & ~mPrereq))!=0 ){
---
> // from SQLite bugfix
> if( rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 || bIn) ){
and changed the xBestIndex return value to be lower if the equality constraint
from IN is not usable
The generated code as reported is invalid (instruction 16 with the init of R6
is not shown)
explain select lsn from atx_txlog where period_no between 7300 and 7313 and
event_Type in (140001,180001);
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Init 0 16000 Start at 16
1 VOpen 0 0 0 vtab:B90B5000
2 Explain2 0 0 SCAN TABLE atx_txlog VIRTUAL TABLE INDEX
1:
00
3 Integer7300 3 000 r[3]=7300
4 Integer7313 4 000 r[4]=7313
5 Integer1 1 000 r[1]=1
6 Integer2 2 000 r[2]=2
7 VFilter0 151
00 iplan=r[1] zplan='
'
8 Noop 0 0 000 begin IN expr
9 VColumn0 15500 r[5]=vcolumn(15);
atx_txlog.event_type
10 Eq 5 126 (BINARY) 43 if r[6]==r[5] goto
12
11 Ne 5 147 (BINARY) 53 if r[7]!=r[5] goto
14; end IN expr
12 VColumn0 21800 r[8]=vcolumn(21);
atx_txlog.lsn
13 ResultRow 8 1 000 output=r[8]
14VNext 0 8 000
15Halt 0 0 000
17Integer180001 7 000 r[7]=180001
18Goto 0 1 000
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Dan Kennedy
Gesendet: Freitag, 29. März 2019 14:30
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables
On 29/3/62 14:32, Hick Gunter wrote:
> When upgrading from 3.7.14.1 to 3.24 I noticed the following problem
>
> Given a virtual table like
>
> CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER,
> attr1 INTEGER,...);
>
> whose xBestIndex function simulates (in unsupported syntax)
>
> CREATE VIRTUAL INDEX vt_key ON vt (key1, key2, key3);
>
> but also handles simple comparisons internally, the query
>
> SELECT * FROM vt WHERE key1 = AND key2 BETWEEN AND
> AND attr1 IN ();
Thanks for reporting this.
What is supposed to happen in this scenario is that xBestIndex() is invoked
once with all 4 constraints marked as usable. The IN(...) is represented as an
SQLITE_INDEX_CONSTRAINT_EQ constraint. If the
xBestIndex() implementation elects to use the IN(...) operator, then
xBestIndex() is invoked a second time, this time with the IN(...) marked as not
usable. SQLite evaluates both plans, considering the cost estimates provided by
the virtual table implementation and its own estimate of the cardinality of the
IN(...) operator. And chooses the most efficient plan overall.
There was a bug preventing the second call to xBestIndex() from being made in
some circumstances - including for your query. Now fixed here:
https://sqlite.org/src/info/f5752517f590b37b
So if you upgrade to trunk, or else apply the patch linked above to 3.27.2, and
the virtual table implementation provides relatively accurate cost estimates,
SQLite should make an intelligent decision about which plan to use.
Dan.
>
> SQLite 3.7.14.1 asks for (key1,'='), (key2,'>='), (key2,'<=') and
> xBestIndex accepts all 3 constraints yielding query plan
>
> - materialize IN as anonymous ephemeral table
> - search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ?)
>- retrieve column attr1
>- search anonymous ephemeral table
>
> i.e. perform a single partial table scan on vt and check attr1
>
>
> SQLite3.24 asks for ...,(attr1,'=') and xBestIndex accepts all 4
> constraints yielding
>
> - materialize IN () as anonymous ephemeral table
> - scan anonymous ephemeral table
>- search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ? AND
> attr1 = ?)
>
> i.e. perform a partial table scan of vt FOR EACH attr1, which is
> slower by the cardinality of the IN list
>
> Fortunately, CTEs come to the rescue:
>
> WITH attrs (attr1) AS (VALUES <(list)>) SELECT * FROM vt CROSS JOIN
> attrs a ON (a.attr1 = vt.attr1) WHERE key1 = AND key2 BETWEEN
> AND
>
> This prevents SQLite 3.24 from adding the last constraint, yielding
>
> - materialize IN (<(list)>) as epehemeral table attrs
> - search vt using index 1 (key1 = ? AND key2 >= ? AND key2 <= ?)
>- retrieve column attr1
>
[sqlite] Error message "Attempt to write to read-only database"
Hi,
first post here. Got a perplexing problem with SQLite and no, the database is
perfectly writable.
In my app I use a different database as main database that unfortunately
doesn't have an FTS. Therefore, I'm using an SQLite database as index. The
SQLite database is very simple:
CREATE VIRTUAL TABLE bodyindex USING fts4(tokenize=unicode61,content='',
messagebody);
I noticed for large messagebody fields that I see the beachball. Another SQLite
library offers multithreaded methods to get rid of the beachball. Now, if I
start writing to the database I get the above error. If I create the SQLite
database with the old SQLite library and then start writing everything works
fine.
Here is the code for writing:
dim thePreparedStatement as SQLitePreparedStatement =
SQLiteIndexDB.Prepare("INSERT INTO bodyindex(docid, messagebody) VALUES(" +
str(theRecID) + ", ?)")
thePreparedStatement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
thePreparedStatement.SQLExecute(MessageBody)
If SQLiteIndexDB.Error Then
globals.theErrorLog.LogItem(CurrentMethodName)
Globals.theErrorLog.DialogErrorProceed kErrorWrite + " " +
SQLiteIndexDB.ErrorMessage
globals.StopArchiving = True
Return
End If
thePreparedStatement = Nil
I made an example and - of course - everything works fine there.
I think that the problem is thread-related. Does anyone have an idea what might
cause this odd problem?
macOS 10.13 + 10.14. I'm using a language + IDE called Xojo.
And just because I like fun I get some SQLite related zombies on 10.14:
0 MBS_SQL_SQLite_Plugin_19846.dylib 0x00010d6e515c
sqlite3LeaveMutexAndCloseZombie + 1596
1 MBS_SQL_SQLite_Plugin_19846.dylib 0x00010d6e454e sqlite3Close + 878
2 MBS_SQL_SQL_Plugin_19846.dylib 0x00010d460a1f
Isl3Connection::Disconnect() + 31
3 MBS_SQL_SQL_Plugin_19846.dylib 0x00010d409307
SAConnection::Disconnect() + 87
4 MBS_SQL_SQL_Plugin_19846.dylib 0x00010d48297a closeDB(dbDatabase*)
+ 42
5 XojoFramework 0x000109b10206 databaseClose + 58
6 Mail Archiver X.debug 0x000105aa002b
Database.Close%%o + 11
7 Mail Archiver X.debug 0x000106c949e4
SQLiteIndex.CloseSQLite%%o + 580 (/SQLiteIndex:376)
8 Mail Archiver X.debug 0x00010767bf7f
MaxModel.CloseArchive%%o + 1343 (/MaxModel:696)
9 Mail Archiver X.debug 0x000108088439
MainWindowController.CloseArchive%%o + 329
(/MainWindowController:358)
10 Mail Archiver X.debug 0x0001080d66ec
MainWindow.MainWindow.CloseDB%%ob + 6476
(/MainWindow:1253)
11 Mail Archiver X.debug 0x0001080be27f
MainWindow.MainWindow.Event_CloseDatabase%%ob + 671
(/MainWindow:714)
12 Mail Archiver X.debug 0x00010815935e
DBWindow.CloseArchive%%o + 958 (/DBWindow:579)
13 Mail Archiver X.debug 0x00010814f611
DBWindow._FileClose_Action%b%o + 337 (/DBWindow:235)
14 XojoFramework 0x000109b58979
RuntimeMenuItemClick(RunMenuItem*, unsigned char, Window*, unsigned char*) +
1063
15 XojoFramework 0x000109a5bef8
CocoaMenu::_MenuItemAction(NSMenuItem*) + 72
10.13 just dies without crashlog. Sometimes and not always.
Mit freundlichen Grüßen/Regards
Beatrix Willius
http://www.mothsoftware.com
Re: [sqlite] ANN: SQLite3 Decimal Extension
Minor correction: > On 5 Apr 2019, at 09:52, Lifepillar wrote: > > select decStr(decAdd(a,60)), case dec(b) when dec(c) then 1 else 0 end from > t1; > select decStr(decAdd(a,70)), case dec(c) when dec(b) then 1 else 0 end from > t1; > […] > select count(*), count(dec(b)), decStr(decSum(b)), decStr(decAvg(b)), > decStr(decMin(b)), decStr(decMax(b)) from t1; should be: select decStr(decAdd(a,60)), case b when c then 1 else 0 end from t1; select decStr(decAdd(a,70)), case c when b then 1 else 0 end from t1; […] select count(*), count(b), decStr(decSum(b)), decStr(decAvg(b)), decStr(decMin(b)), decStr(decMax(b)) from t1; Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite3 Decimal Extension
On 5 Apr 2019, at 00:18, Simon Slavin wrote:
>
> On 4 Apr 2019, at 10:12pm, Lifepillar wrote:
>
>> This is essentially a pragmatic choice, as the semantics of NULLs is
>> unspecified and ambiguous.
>
> The way SQL handles NULLs may sometimes appear inconsistent, but is the
> result of SQL handling rows as sets. Some of the behaviour is, carefully
> designed, with the knowledge that the NULL that results from one step of an
> expression is going to be fed into the next step of the expression.
I beg to disagree. NULL's handling in SQL *is* inconsistent. The SQL committee
has taken some actions to make things... less worse, in particular by adding
F571 ("is true", "is false", "is unknown"), but the castle still remains (and
will likely forever remain) a castle of sand.
> For those purposes, NULL means one of two things: "no value" or "value
> unknown”.
There is a third option: “no information” (value may exist and be unknown, or
it may not exist). And before someone asks: no, there is no need for going meta
(“don’t know whether there is no information…”).
> For instance, the sum of a result which includes a NULL value is NULL.
Note that this is incompatible with the “value unknown” semantics (noticed in
*1977* by Grant!). Trivial example:
select * from T where x = x;
If x contains NULLs, under the “value unknown” semantics the above query should
still output all the rows, because an existing value, albeit unknown, is
always, surely, equal to itself. Under the “inapplicable” semantics SQL's
result is correct, though, and under the “no information” semantics it may be
considered correct assuming that you want certain answers (i.e., only tuples
that satisfy the condition with certainty). You see that NULL conflate
different things and leaves the user the burden to deal with it.
> But the sum of a result with zero rows is not NULL, it's zero.
So, SQL is wrong there, because it returns NULL (my library returns 0). Note
that an empty table has no NULLs, so the result of any query or function on an
empty set has nothing to do with NULLs.
I am fond of NULLs (as much as I try to avoid them in my databases), so if you
feel like it is interesting discussing this any further (and deemed appropriate
for the list), we may do so in a separate thread.
> For the purposes of a decimal extension to SQLite, I would imitate what
> SQLite3 does with REAL values. If you have a question about how SQLite sees
> NULL it might be answered here:
>
>
>
> It's especially important that the test script at the end of that page,
> behaves the same if you run it as is, and if you substitute the 'int' type
> with your decimal type. If you have any questions, please don't hesitate to
> ask.§
I have tried the script, adapted for decimals, and it works the same as for
ints. The script is reported at the end of this message. Btw, talking about
consistency:
create table t(n int);
insert into t values (1), (null);
select distinct n from t; -- Result has two rows
select count(distinct n) from t; -- 1
:)
Thanks for the feedback!
Life.
-- Create a test table with data
create table t1(a blob, b blob, c blob);
insert into t1 values (dec(1), dec(0), dec(0));
insert into t1 values (dec(2), dec(0), dec(1));
insert into t1 values (dec(3), dec(1), dec(0));
insert into t1 values (dec(4), dec(1), dec(1));
insert into t1 values (dec(5), null, dec(0));
insert into t1 values (dec(6), null, dec(1));
insert into t1 values (dec(7), null, null);
select decStr(a), decStr(b), decstr(c) from t1;
-- Check to see what CASE does with NULLs in its test expressions
select decStr(a), case when b<>dec(0) then 1 else 0 end from t1;
select decStr(decAdd(a,10)), case when not b<>dec(0) then 1 else 0 end from t1;
select decStr(decAdd(a,20)), case when b<>dec(0) and c<>dec(0) then 1 else 0
end from t1;
select decStr(decAdd(a,30)), case when not (b<>dec(0) and c<>dec(0)) then 1
else 0 end from t1;
select decStr(decAdd(a,40)), case when b<>dec(0) or c<>dec(0) then 1 else 0 end
from t1;
select decStr(decAdd(a,50)), case when not (b<>dec(0) or c<>dec(0)) then 1 else
0 end from t1;
select decStr(decAdd(a,60)), case dec(b) when dec(c) then 1 else 0 end from t1;
select decStr(decAdd(a,70)), case dec(c) when dec(b) then 1 else 0 end from t1;
-- What happens when you multiple a NULL by zero?
select decStr(decAdd(a,80)), decStr(decMul(b,0)) from t1;
select decStr(decAdd(a,90)), decStr(decMul(b,c)) from t1;
-- What happens to NULL for other operators?
select decStr(decAdd(a,100)), decStr(decAdd(b,c)) from t1;
-- Test the treatment of aggregate operators
select count(*), count(dec(b)), decStr(decSum(b)), decStr(decAvg(b)),
decStr(decMin(b)), decStr(decMax(b)) from t1;
-- Check the behavior of NULLs in WHERE clauses
select decStr(decAdd(a,110)) from t1 where bdec(10);
select decStr(decAdd(a,130)) from t1 where bhttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
