Re: [sqlite] REMOVE ME

[Jose Isaias Cabrera]

Click at the link at the bottom of the email...


From: sqlite-users  on behalf of 
Edmondo Borasio 
Sent: Saturday, March 14, 2020 03:34 AM
To: SQLite mailing list 
Subject: [sqlite] REMOVE ME


___
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] New SQLite Forum established - this mailing list is deprecated

[Jose Isaias Cabrera]

x, on Friday, March 13, 2020 09:01 AM, wrote...
>
> * I truly hope Keith you'll continue making your tremendous contributions
> to
> the SQLite community.
>
I would say to the SQL world.

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

Re: [sqlite] COALESCE() does not short-circuit sometimes

[Jose Isaias Cabrera]

Justin Ng, on Wednesday, March 11, 2020 07:03 AM, wrote...
>
> This happens in SQLite 3.28 and 3.31.
>
> Consider the following queries,
>
> -- Query 1
> SELECT
>   COALESCE(
> NULL,
> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT
> NULL))
>   );

On 3.24.0, this worked correctly:
 8:32:20.56>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT
   ...>   COALESCE(
   ...> NULL,
   ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT 
NULL))
   ...>   );
Error: integer overflow


> -- Query 2
> SELECT
>   COALESCE(
> (SELECT 'hello'),
> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT
> NULL))
>   );

Also, on 3.24.0. this worked correctly:
sqlite> SELECT
   ...>   COALESCE(
   ...> (SELECT 'hello'),
   ...> (SELECT SUM(9223372036854775807) FROM (SELECT NULL UNION ALL SELECT 
NULL))
   ...>   );
hello

> -- Query 3
> SELECT
>   COALESCE(
> (SELECT 'hello'),
> ABS(-9223372036854775808)
>   );

This one, above, was bad at 3.24.0 also.
sqlite> SELECT
   ...>   COALESCE(
   ...> (SELECT 'hello'),
   ...> ABS(-9223372036854775808)
   ...>   );
Error: integer overflow

> Query 1 should throw `integer overflow`.
> Query 2 should return 'hello'.
> Query 3 should return 'hello'.
>
> However, Query 3 throws `integer overflow`. It should short-circuit and
> not evaluate ABS()

Why do you think that that it should not evaluate ABS?  It is there and you 
asked for it.  I believe it's a good idea to say, "hey, the amount you placed 
here is out of boundary, think about what you are doing here." IMO, of course. 
Thanks.

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

Re: [sqlite] No feedback for executing a mathematical expression

[Jose Isaias Cabrera]

Simon Slavin, on Tuesday, March 10, 2020 09:23 AM, wrote...
>
> On 10 Mar 2020, at 12:40pm, Jose Isaias Cabrera 
> wrote:
>
> > Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote...
> >
> >> That's going in my list of annoying questions.  Thank you.
> >
> > Simon, with all due respect, and grateful for all the answers you have
> provided to me, this is not an annoying question. Not everyone in the world
> knows what you know.
>
> Jose, I must apologise for not explaining myself better.
>
> It's a delightful annoying question. Anyone who asks that question should
> be annoyed, at computers in general and the one they're swearing at in
> particular. I love those questions and I giggle over them when people tell
> me that programming must be easy because computers are simple and logical.

Apologies, Simon.  I knew there was something wrong, here: me.  Yes, I said to 
myself, "that is not like Simon to answer that harsh!" But you actually were 
talking about the SQL syntax itself.  It's hard with emails to see the faces, 
intentions, meanings, etc., behind the content. Thanks for explaining yourself. 
:-)  I knew I was wrong.

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

Re: [sqlite] No feedback for executing a mathematical expression

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, March 10, 2020 03:57 AM, wrote...
>
>
> On Tuesday, 10 March, 2020 01:22, Octopus ZHANG 
> wrote:
>
> >I try to run a simple math expression, but SQLite gives no feedback :
>
> >sqlite> select 99-(55/(30/57));
>
> >Should I expect it to return nothing?
>
> It is returning something.  It is returning NULL.
>
> sqlite> .nullvalue 
> sqlite> select 99-(55/(30/57));
> 
> sqlite>
>
> 99 - (55 / (30 / 57))
>
> 30 / 57 -> 0
>
> 55 / 0 -> NULL
>
> 99 - NULL -> NULL
>
> If you want the result of 30/57 to be a floating point number (ie, not
> zero), you need to have one of those numbers be floating point, after which
> each successive operation will be carried out in floating point rather than
> integer arithmetic.
>
> 30. / 57 == 30 / 57. == 30. / 57. -> 0.526315789473684
>
> 55 / 0.526315789473684 -> 104.5
>
> 99 - 104.5 -> -5.5
>
Thanks Keith for the lesson of the day. ;-)

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

Re: [sqlite] No feedback for executing a mathematical expression

[Jose Isaias Cabrera]

Simon Slavin, on Tuesday, March 10, 2020 03:25 AM, wrote...
>
> On 10 Mar 2020, at 7:21am, Octopus ZHANG  wrote:
>
> > sqlite> select 99-(55/(30/57));
> >
> > Should I expect it to return nothing?
>
> That's going in my list of annoying questions.  Thank you.

Simon, with all due respect, and grateful for all the answers you have provided 
to me, this is not an annoying question.  Not everyone in the world knows what 
you know.  Some people do answer questions because they don't know.  I actually 
was going to add other ones that I found trying to figure this one out.  But, 
Keith has provided the answer.  Otherwise, I would have added some more.  In 
this forum, email-list, most people ask questions because we just don't know, 
and we want to learn.  Thanks.

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

Re: [sqlite] sqlite3: .width counts bytes, not characters

[Jose Isaias Cabrera]

softw...@quantentunnel.de, on Monday, March 9, 2020 04:02 PM, wrote...
>
> Hi
>
> I use sqlite3 (sqlite3 --version = "3.11.0 2016-02-15 17:29:24
> 3d862f207e3adc00f78066799ac5a8c282430a5f" on Ubuntu 16.04.6 LTS) for
> formatted output. '.width' does not behave as I expected when non-ASCII
> Unicode characters are printed. It seems that .width counts bytes and not
> characters. See test case below (in case the email does not display
> non-ASCII characters properly: the second INSERT has 6 characters '3/4' as
> first field). Is there an option to format the output nicely in columns
> when non-ASCII characters may be included?
>
> Test case:
> echo "CREATE TEMPORARY TABLE width_test (data, description);" >
> width_test.sql
> echo "INSERT INTO width_test VALUES('aa', '6 ASCII letters');" >>
> width_test.sql
> echo "INSERT INTO width_test VALUES('¾¾', '6 non-AScII
> letters');" >> width_test.sql
> echo ".mode col" >> width_test.sql
> echo ".width 6 20" >> width_test.sql
> echo "SELECT * FROM width_test;" >> width_test.sql
> echo ".width 9 20" >> width_test.sql
> echo "SELECT * FROM width_test;" >> width_test.sql
> echo ".width 12 20" >> width_test.sql
> echo "SELECT * FROM width_test;" >> width_test.sql
> cat width_test.sql | sqlite3 "test.db"

On Windows is working as expected.  Or maybe, I don't understand the problem, 
which is something that could be very likely.
12:10:51.82>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TEMPORARY TABLE width_test (data, description);
sqlite> INSERT INTO width_test VALUES('aa', '6 ASCII letters');
sqlite> INSERT INTO width_test VALUES('¾¾', '6 non-AScII letters');
sqlite> .mode col
sqlite> .width 6 20
sqlite> SELECT * FROM width_test;
aa  6 ASCII letters
__  6 non-AScII letters
sqlite> .width 9 20
sqlite> SELECT * FROM width_test;
aa 6 ASCII letters
__ 6 non-AScII letters
sqlite> .width 12 20
sqlite> SELECT * FROM width_test;
aa6 ASCII letters
__6 non-AScII letters
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting all changes within a begin; end; transaction

[Jose Isaias Cabrera]

Simon Slavin, on Wednesday, March 4, 2020 10:47 AM, wrote...
>
> On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera  wrote:
>
> > The reason why I know is that if I have 238 INSERTS, but I have a
> constraint , there should be 238 INSERTs the first time I run a set of SQL,
> but if I run the same SQL again, there should not be any records INSERTED,
> and thus, the amount should be 0, correct?
>
> Can you try the same thing but instead of INSERT use INSERT OR IGNORE ?

Simon Slavin, you are a genius!  Well, you're pretty smart. ;-)  Yes, indeed, I 
was using
INSERT OR REPLACE, which will always work. ;-)  INSERT OR IGNORE is now 
providing
the result I am looking for.  As we say in Spanish, muchas gracias.

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

Re: [sqlite] Getting all changes within a begin; end; transaction

[Jose Isaias Cabrera]

Simon Slavin, on Wednesday, March 4, 2020 09:42 AM, wrote...
>
> On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera  wrote:
>
> > Is there a way to know all the changes that may have happened within
> the full BEGIN and END? Thanks.
>
> Use this function
>
> <https://sqlite.org/c3ref/total_changes.html>
>
> before and after your block, and subtract one from another.

Thanks, this is exactly what I needed.  However, I have found that it does not 
actually provide the **ACTUAL** changes, but a count of the possible changes.  
The reason why I know is that if I have 238 INSERTS, but I have a constraint , 
there should be 238 INSERTs the first time I run a set of SQL, but if I run the 
same SQL again, there should not be any records INSERTED, and thus, the amount 
should be 0, correct?  So, is there one that actually provides the actual 
number of changes?  Thanks.

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

[sqlite] Getting all changes within a begin; end; transaction

[Jose Isaias Cabrera]

Greetings.

Imagine this SQL,

BEGIN TRANSACTION;
...
changes to records
...
END;

When I execute "int result = sqlite3_changes(database);" after that SQL 
execution, I always get 1.  I think that it is because it is only providing the 
result of the last statement that was successful within the BEGIN-END 
transaction.  I know that in all cases there should be many more than 1. Is 
there a way to know all the changes that may have happened within the full 
BEGIN and END?  Thanks.

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

Re: [sqlite] Report bug found in SQLite version 3.31.1

[Jose Isaias Cabrera]

Right, Yinyue.  Apologies.  I actually thought I had built it.  Thanks.



From: sqlite-users  on behalf of 
Xinyue Chen 
Sent: Monday, March 2, 2020 06:40 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Report bug found in SQLite version 3.31.1

Hi josé,

This bug is found in 3.31.1 but you are running it in 3.30.1.

Best,
Xinyue Chen

On Mon, Mar 2, 2020 at 3:36 PM Jose Isaias Cabrera 
wrote:

> Xinyue Chen, on Monday, March 2, 2020 06:21 PM, wrote...
> >
> > Hi,
> >
> > I found a bug in the most recent SQLite release version 3.31.1
> 2020-01-27.
> > My initial test environment is macOS 10.14.6 (18G87) and I have tested in
> > https://sqliteonline.com/.
> >
> > CREATE TABLE t (
> >   textid TEXT
> > );
> > INSERT INTO t
> > VALUES ('12');
> > INSERT INTO t
> > VALUES ('34');
> > CREATE TABLE i (
> >   intid INTEGER PRIMARY KEY
> > );
> > INSERT INTO i
> > VALUES (12);
> > INSERT INTO i
> > VALUES (34);
> > CREATE TABLE e (
> >   x INTEGER PRIMARY KEY NOT NULL,
> >   y TEXTNOT NULL
> > );
> > -- Original query
> > select t1.textid a, i.intid b
> > from t t1,
> >  i i
> > where ((t1.textid = i.intid) and (t1.textid = 12));
> > -- Buggy query
> > select distinct t1.textid a, i.intid b
> > from t t1,
> >  i i,
> >  t vucjp
> > where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
> > vucjp.textid) and (t1.textid = vucjp.textid));
> >
> > The results for the two queries should be the same, but the result for
> the
> > first one is 12|12 and for the second one is 12|12, 34|12.
> I get 12|12 for both query.
>
> 18:00:22.46>sqlite3
> SQLite version 3.30.1 2019-10-10 20:19:45
> 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 (
>...>   textid TEXT
>...> );
> sqlite> INSERT INTO t
>...> VALUES ('12');
> sqlite> INSERT INTO t
>...> VALUES ('34');
> sqlite> CREATE TABLE i (
>...>   intid INTEGER PRIMARY KEY
>...> );
> sqlite> INSERT INTO i
>...> VALUES (12);
> sqlite> INSERT INTO i
>...> VALUES (34);
> sqlite> CREATE TABLE e (
>...>   x INTEGER PRIMARY KEY NOT NULL,
>...>   y TEXTNOT NULL
>...> );
> sqlite> -- Original query
> sqlite> select t1.textid a, i.intid b
>...> from t t1,
>...>  i i
>...> where ((t1.textid = i.intid) and (t1.textid = 12));
> 12|12
> sqlite> -- Buggy query
> sqlite> select distinct t1.textid a, i.intid b
>...> from t t1,
>...>  i i,
>...>  t vucjp
>...> where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
>...> vucjp.textid) and (t1.textid = vucjp.textid));
> 12|12
> sqlite>
>
> Maybe I am not following your steps, but as you can see above, I am
> getting the same for both.
>
> josé
> ___
> 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] Report bug found in SQLite version 3.31.1

[Jose Isaias Cabrera]

Xinyue Chen, on Monday, March 2, 2020 06:21 PM, wrote...
>
> Hi,
>
> I found a bug in the most recent SQLite release version 3.31.1 2020-01-27.
> My initial test environment is macOS 10.14.6 (18G87) and I have tested in
> https://sqliteonline.com/.
>
> CREATE TABLE t (
>   textid TEXT
> );
> INSERT INTO t
> VALUES ('12');
> INSERT INTO t
> VALUES ('34');
> CREATE TABLE i (
>   intid INTEGER PRIMARY KEY
> );
> INSERT INTO i
> VALUES (12);
> INSERT INTO i
> VALUES (34);
> CREATE TABLE e (
>   x INTEGER PRIMARY KEY NOT NULL,
>   y TEXTNOT NULL
> );
> -- Original query
> select t1.textid a, i.intid b
> from t t1,
>  i i
> where ((t1.textid = i.intid) and (t1.textid = 12));
> -- Buggy query
> select distinct t1.textid a, i.intid b
> from t t1,
>  i i,
>  t vucjp
> where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
> vucjp.textid) and (t1.textid = vucjp.textid));
>
> The results for the two queries should be the same, but the result for the
> first one is 12|12 and for the second one is 12|12, 34|12.
I get 12|12 for both query.

18:00:22.46>sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
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 (
   ...>   textid TEXT
   ...> );
sqlite> INSERT INTO t
   ...> VALUES ('12');
sqlite> INSERT INTO t
   ...> VALUES ('34');
sqlite> CREATE TABLE i (
   ...>   intid INTEGER PRIMARY KEY
   ...> );
sqlite> INSERT INTO i
   ...> VALUES (12);
sqlite> INSERT INTO i
   ...> VALUES (34);
sqlite> CREATE TABLE e (
   ...>   x INTEGER PRIMARY KEY NOT NULL,
   ...>   y TEXTNOT NULL
   ...> );
sqlite> -- Original query
sqlite> select t1.textid a, i.intid b
   ...> from t t1,
   ...>  i i
   ...> where ((t1.textid = i.intid) and (t1.textid = 12));
12|12
sqlite> -- Buggy query
sqlite> select distinct t1.textid a, i.intid b
   ...> from t t1,
   ...>  i i,
   ...>  t vucjp
   ...> where ((t1.textid = i.intid) and (t1.textid = 12) and (t1.textid =
   ...> vucjp.textid) and (t1.textid = vucjp.textid));
12|12
sqlite>

Maybe I am not following your steps, but as you can see above, I am getting the 
same for both.

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

Re: [sqlite] Fwd: inserting new data only

[Jose Isaias Cabrera]

James K. Lowden, on Wednesday, February 26, 2020 11:55 AM, wrote...
>
> On Tue, 25 Feb 2020 12:02:24 -0500
> Przemek Klosowski  wrote:
>
> > and I want to avoid storing repetitive data, so that the database
> > should contain
> > 10:32  12
> > 10:35  15
> > 10:39  13
> > 10:46  18
> > where only the earliest time with the unchanging value is stored.
>
> Be careful what you wish for.  Usually "avoid storing" is a proxy for
> some kind of intended use.  Unless it's infeasible, it's usually better
> to store everything, verbatim, as it arrives.  Then you can present it
> however you like, with nothing lost.

Fresh real-life-work experience...
I was tasked to take data from a system and report on it, so, as I looked
over the data I saw multiple entries, so I asked the client do you want to
know if a set of data is repeated? "NO," said the client, "I only care for
what new and fresh."  Are you sure, I can see that there are repeated
entries, and I can see different places where they are coming from, and
how often, and... "NO!,  I only care for what new and fresh."
When I was going to show the reporting tool, she said, "By the way, this
has how many times repeated records are coming in, and from where,
correct?" I knew this was going to happened, so I had left a way to
quickly make a few changes, re-imported the data, and was able to save
the world.  So, as James said, "I would keep everything and just show
what you need."  Maybe someday you want to know how many
repeated entries happen in an hour, day, etc.  Thanks.

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

Re: [sqlite] Trigger name missing

[Jose Isaias Cabrera]

SQLite is even better than I thought...


From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Wednesday, February 26, 2020 11:44 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Trigger name missing

On 2/26/20, Simon Slavin  wrote:
>
> Backward compatibility ?  Do you think anyone who used the word AFTER
> really wants a BEFORE trigger ?  More likely to be a bug they should know
> about.

We have seen triggers like this in the wild, that work as intended.
If we change it to throw an error, the applications that use these
kinds of triggers will suddenly start failing.  Some of them (no
doubt) are unmaintained.  The source code has gone missing for some of
them, perhaps.  That much breakage is not worth it.

The issue arises from the forgiving nature of the SQLite parser.  The
parser is designed so that we can add new keywords to the language
(for example:  "GENERATED" and "ALWAYS" in the most recent release, in
support of generated columns) without breaking legacy schemas that use
those keywords as table or column names.

Consider what happens in the example Dan provide:

  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
...
  END;

The parser is bebooping along, parsing tokens one by one.  The first
token is the keyword CREATE.  The second token is the keyword TRIGGER.
All good so far.  The third token is the keyword AFTER.  But the
grammar does not recognize the keyword AFTER in that context, and so
the parser converts it into an identifier with the value of "AFTER".
That does work, and so the parse continues, using "AFTER" as the name
of the trigger.  That example is a little confusing.  But what if,
instead, the trigger has been this:

  CREATE TRIGGER generated INSERT ON t1 BEGIN
...
  END;

With strict enforcement of keywords, this trigger would have worked
fine for all versions of SQLite through 3.30.1 and then started
failing in version 3.31.0, because it was in that release that
GENERATED became a keyword.  But with the "fallback" mechanism in
SQLite, the trigger continues to work as it always has.  That is why
the mechanism exists - to prevent unnecessary breakage when new
keywords are added to the language.

There are literally millions of applications that use SQLite.  Some
fraction of those are unmaintained.  And some additional fraction of
those will break, probably to never work again, whenever we add a
keyword, except for the keyword fallback mechanism.
--
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] [EXTERNAL] rtrim and round functions unexpected result

[Jose Isaias Cabrera]

Ah, so rtrim(X,Y) removes all characters in the Y slot; NOT the string Y.  
Apologies.  I thought that it was the string that it removed.  Ok, replace it 
is, then.



From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Thursday, February 20, 2020 11:09 AM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] rtrim and round functions unexpected result

Round(1299.6) returns the floating point number 1300.0,
passing 1300.0 to the rtrim function converts it tot he string '1300.0'
removing all '.' and '0' characters from '1300.0' yields 13
This is no suprise

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jose Isaias Cabrera
Gesendet: Donnerstag, 20. Februar 2020 17:03
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] rtrim and round functions unexpected result


Greetings.

Please take a look at the following:
sqlite> select rtrim(round(1235.6));
1236.0
This is expected.
sqlite> select rtrim(round(1235.6),'.0');
1236
Also expected.
sqlite> select rtrim(round(1299.6),'.0');
13
is not expected.  I was hoping for 1300.  Also, just rtrim,
sqlite> select rtrim('1000.0','.0');
1
sqlite> select rtrim('1000.0','0');
1000.

I know I can use replace for this,

sqlite> select replace('1.0','.0','');
1

but I wanted to see if there was an explanation for it. By the way, escaping 
the period (.) or dot also fails.

sqlite> select rtrim('1000.0','\.0');
1

Thanks.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] rtrim and round functions unexpected result

[Jose Isaias Cabrera]

Greetings.

Please take a look at the following:
sqlite> select rtrim(round(1235.6));
1236.0
This is expected.
sqlite> select rtrim(round(1235.6),'.0');
1236
Also expected.
sqlite> select rtrim(round(1299.6),'.0');
13
is not expected.  I was hoping for 1300.  Also, just rtrim,
sqlite> select rtrim('1000.0','.0');
1
sqlite> select rtrim('1000.0','0');
1000.

I know I can use replace for this,

sqlite> select replace('1.0','.0','');
1

but I wanted to see if there was an explanation for it. By the way, escaping 
the period (.) or dot also fails.

sqlite> select rtrim('1000.0','\.0');
1

Thanks.

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

Re: [sqlite] Why do I only get one record?

[Jose Isaias Cabrera]

Igor Korot, on Wednesday, February 19, 2020 04:30 PM, wrote...
>
> Hi,
>
> On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera 
> wrote:
> >
> >
> > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working
> now. Thanks.
> >
> >
> > 
> sqlite-users  on behalf of
> Keith Medcalf 
> > Sent: Wednesday, February 19, 2020 03:09 PM
> > To: SQLite mailing list 
> > >
> >
> > 1) In the first two query's, why do you have a condition on the LHS
> table in the LEFT JOIN conditions?
> > 2) In the last query, why do you have a condition on the RHS table of
> the LEFT JOIN in the WHERE clause?
> >
> > These would seem to indicate that you are using a LEFT JOIN when you
> really do not want a LEFT JOIN (maybe you are a LEFT lover?) ... it is a
> common ailment.
>
> I hate MS-ism. ;-)
> Why not just use a simple WHERE a.id = b.id?

That's what I was using originally, but I was only getting one record.  I was 
missing HAVING.

> Less typing anyway...
>
> Thank you.
>
> >
> > Also, you are only getting one row because you only asked for one row.
> If you request an aggregate and specify no GROUP BY then you can only ever
> get one row as a result -- the one aggregate row.
> >
> > If you want more than one row you need to specify by what you want the
> results grouped in the GROUP BY clause.
> >
> > --
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says a lot about anticipated traffic volume.
> >
> > >-Original Message-
> > >From: sqlite-users  On
> > >Behalf Of Jose Isaias Cabrera
> > >Sent: Wednesday, 19 February, 2020 12:46
> > >To: SQLite mailing list 
> > >Subject: [sqlite] Why do I only get one record?
> > >
> > >
> > >Greetings!
> > >
> > >Newbie here again... ;-)
> > >
> > >Please take a look at this query,
> > >
> > >sqlite> SELECT
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why do I only get one record?

[Jose Isaias Cabrera]

Thanks, Keith.  Darn it!  GROUP BY and ORDER BY!  Got it, it's working now.  
Thanks.



From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Wednesday, February 19, 2020 03:09 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Why do I only get one record?


1)  In the first two query's, why do you have a condition on the LHS table in 
the LEFT JOIN conditions?
2)  In the last query, why do you have a condition on the RHS table of the LEFT 
JOIN in the WHERE clause?

These would seem to indicate that you are using a LEFT JOIN when you really do 
not want a LEFT JOIN (maybe you are a LEFT lover?) ... it is a common ailment.

Also, you are only getting one row because you only asked for one row.  If you 
request an aggregate and specify no GROUP BY then you can only ever get one row 
as a result -- the one aggregate row.

If you want more than one row you need to specify by what you want the results 
grouped in the GROUP BY clause.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, 19 February, 2020 12:46
>To: SQLite mailing list 
>Subject: [sqlite] Why do I only get one record?
>
>
>Greetings!
>
>Newbie here again... ;-)
>
>Please take a look at this query,
>
>sqlite> SELECT
>   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
>   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
>   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
>   ...> FROM Project_List AS a
>   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
>   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget
>WHERE ProjID = b.ProjID)
>   ...> AND
>   ...> a.InsertDate =
>   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID =
>a.ProjID)
>   ...> WHERE a.ProjID IN
>   ...> (
>   ...> 'PR018284',
>   ...> 'PR015544'
>   ...> )
>   ...> ORDER BY a.ProjID;
>PR015544|2019-01-01|2020-01-01||
>sqlite>
>
>Why do I only get one row?  Because if I do this other query,
>
>sqlite> SELECT
>   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
>   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
>   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
>   ...> FROM Project_List AS a
>   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
>   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget
>WHERE ProjID = b.ProjID)
>   ...> AND
>   ...> a.InsertDate =
>   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID =
>a.ProjID)
>   ...> WHERE a.ProjID IN
>   ...> (
>   ...> 'PR018284'
>   ...> )
>   ...> ORDER BY a.ProjID;
>PR018284|2020-01-01|2020-03-01||
>sqlite>
>
>That project exists.  And if I do this other query,
>sqlite> SELECT
>   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
>   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
>   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
>   ...> FROM Project_List AS a
>   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
>   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget
>WHERE ProjID = b.ProjID)
>   ...> WHERE
>   ...> a.InsertDate =
>   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID =
>a.ProjID)
>   ...> AND a.ProjID = b.ProjID
>   ...> ;
>PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01-
>31|76605061.443927|76125541.48|149733051.57
>sqlite>
>
>I still only get one.  It looks like I am setting a limit, but that is
>not true.  Any help would be greatly appreciated.  Thanks.
>
>josé
>___
>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

[sqlite] Why do I only get one record?

[Jose Isaias Cabrera]

Greetings!

Newbie here again... ;-)

Please take a look at this query,

sqlite> SELECT
   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
   ...> FROM Project_List AS a
   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget WHERE 
ProjID = b.ProjID)
   ...> AND
   ...> a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...> WHERE a.ProjID IN
   ...> (
   ...> 'PR018284',
   ...> 'PR015544'
   ...> )
   ...> ORDER BY a.ProjID;
PR015544|2019-01-01|2020-01-01||
sqlite>

Why do I only get one row?  Because if I do this other query,

sqlite> SELECT
   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
   ...> FROM Project_List AS a
   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget WHERE 
ProjID = b.ProjID)
   ...> AND
   ...> a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...> WHERE a.ProjID IN
   ...> (
   ...> 'PR018284'
   ...> )
   ...> ORDER BY a.ProjID;
PR018284|2020-01-01|2020-03-01||
sqlite>

That project exists.  And if I do this other query,
sqlite> SELECT
   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
   ...> FROM Project_List AS a
   ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
   ...>   AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget WHERE 
ProjID = b.ProjID)
   ...> WHERE
   ...> a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...> AND a.ProjID = b.ProjID
   ...> ;
PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01-31|76605061.443927|76125541.48|149733051.57
sqlite>

I still only get one.  It looks like I am setting a limit, but that is not 
true.  Any help would be greatly appreciated.  Thanks.

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

Re: [sqlite] DRH interview on why/how SQLite succeeded

[Jose Isaias Cabrera]

Don V Nielsen, on Wednesday, February 19, 2020 01:43 PM, wrote...
>
> DRH sounds so much more human in the podcast than the DRH I have
> assembled
> in my head from reading this mailing list. :)
>
He is human like us, just smarter than most of us. :-)

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

Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

[Jose Isaias Cabrera]

Scott, on Thursday, February 13, 2020 09:01 AM, wrote...
>
> Can I search all tables and columns of SQLite database for a specific
> text string? I'm sure this question has been asked many times, but I'm
> having trouble finding a solid answer.
> My problem: My clients SQLite database has 11 tables and multiple columns
> (some designated and others not) and they want to be able to search the
> entire database for a specific text or phrase.
> What I have done: I've been searching a couple days and found the Full
> Text search on SQLite home based upon using a virtual table, but I don't
> think that will work. It appears that I may be able to search the
> sqlite_master but it seems it may only contain table and column information
> only minus the data.
> What I'm working in: This is an Android app written in Java using the
> SQLite
> What I hope to do: Find a simple query statement or combination of
> statements that may help to efficiently query for the string across tables
> and columns before I resort to multiple queries and methods for all 11
> tables.
> I'm looking for any experienced thoughts or suggestions anyone may have
> encountered resolving this kind of issue if available. I'm not expecting
> anyone to solve it for me -- just some guidance would be helpful.

This is a very wide open question.  It is a lot of work to create the query.
I actually have to do this for some tables and some fields, but I know these
tables and these fields. Here are some questions:

1. What are you going to do when you find a string match in a table field?

2. Do you need to know that table?  Do you need to know the field?

3. Do you need the whole content of that field if matched?

There are just too many questions to help, but it is possible if you know
what do you want to do. Here are some ideas:
a. The command prompt has a .table option that will provide all the tables
available on a DB
b. The .schema [tablename] will give you the table's fields

Imagine these three tables:
create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d);
insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby 2002', 
'2 plus 2 equals 4', 'I am going home soon');
create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12);
insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1 
equals 2');
create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d);
insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home', 
'2020-02-11','Once upon a time...');

SELECT
'field t0a on table0 has the string [plus]: ', t0a from table0
WHERE t0a LIKE '%plus%'
UNION
SELECT
'field t0b on table0 has the string [plus]: ', t0b from table0
WHERE t0b LIKE '%plus%'
UNION
SELECT
'field t0c on table0 has the string [plus]: ', t0c from table0
WHERE t0c LIKE '%plus%'
UNION
SELECT
'field t0d on table0 has the string [plus]: ', t0d from table0
WHERE t0d LIKE '%plus%'
UNION
SELECT
'field t10 on table1 has the string [plus]: ', t10 from table1
WHERE t10 LIKE '%plus%'
UNION
SELECT
'field t11 on table1 has the string [plus]: ', t11 from table1
WHERE t11 LIKE '%plus%'
UNION
SELECT
'field t12 on table1 has the string [plus]: ', t12 from table1
WHERE t12 LIKE '%plus%'
UNION
SELECT
'field t2a on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
UNION
SELECT
'field t2b on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
UNION
SELECT
'field t2a on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
;

For just to search on the string 'plus' you will have to do the
above query.

sqlite> SELECT
   ...> 'field t0a on table0 has the string [plus]: ', t0a from table0
   ...> WHERE t0a LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t0b on table0 has the string [plus]: ', t0b from table0
   ...> WHERE t0b LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t0c on table0 has the string [plus]: ', t0c from table0
   ...> WHERE t0c LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t0d on table0 has the string [plus]: ', t0d from table0
   ...> WHERE t0d LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t10 on table1 has the string [plus]: ', t10 from table1
   ...> WHERE t10 LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t11 on table1 has the string [plus]: ', t11 from table1
   ...> WHERE t11 LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t12 on table1 has the string [plus]: ', t12 from table1
   ...> WHERE t12 LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t2a on table2 has the string [plus]: ', t2a from table2
   ...> WHERE t2a LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t2b on table2 has the string [plus]: ', t2a from table2
   ...> WHERE t2a LIKE '%plus%'
   ...> UNION
   ...> SELECT
   ...> 'field t2a on table2 has the string [plus]: ', t2a from table2
   ...> WHERE t2a LIKE '%plus%'
   ...> ;
field t0c on table0 has the string [plus]: |2 plus 2 equals 4
field t12 on table1 has the 

[sqlite] .timer explanation anywhere

[Jose Isaias Cabrera]

Greetings.

I was searching on sqlite.org for [sqlite command line tool .timer explanation] 
and found nothing. I also searched on the internet and found an old thread[1] 
of when .timer had just two entries:

CPU Time: user 880.710398 sys 353.260288

And, although, there is some good information there, I would like for us to 
have a solid answer somewhere. :-)  Maybe even explain it on the site [2], or 
better yet, have an option on .timer (on|off|?) to explain each piece of the 
output. One-liners will suffice.

I also found this other interesting post [3], which I think is not totally 
correct, but I will let you guys explain why it is true.  Or, at least, if it 
has some truth in it.

Thanks for your support.

josé

[1] http://sqlite.1065341.n5.nabble.com/timer-on-in-the-shell-tool-td79626.html
[2] https://www.sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_
[3] 
https://stackoverflow.com/questions/40329106/how-to-measure-the-execution-time-of-each-sql-statement-query-in-sqlite
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug report (INSTR() ignores NOCASE on columns)

[Jose Isaias Cabrera]

Stephan Senzel, on Sunday, February 2, 2020 08:12 AM, wrote...
>
> INSTR() ignores NOCASE on columns
>
> ---
>
> example:
>
> SELECT * FROM table WHERE INSTR(column, ' castle ') > 0
>
> returns datasets with 'castle' only, without 'Castle', even if the
> column is set to NOCASE

True statement with v3.31.0:
12:25:41.10>sqlite3
SQLite version 3.31.0 2020-01-22 18:38:59
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0(a string collate nocase);
sqlite> insert into t0 values ('In my castle I have...');
sqlite> insert into t0 values ('In my castle I have had...');
sqlite> insert into t0 values ('In my castle I''ve never had...');
sqlite> insert into t0 values ('In my Castle I have...');
sqlite> select a from t0 where INSTR(a,' castle') > 0;
In my castle I have...
In my castle I have had...
In my castle I've never had...
sqlite>


> LIKE doesn't have this problem, works well
>
> SELECT * FROM table WHERE column LIKE '% castle %'
>
> returns 'castle' and 'Castle' when column is set to NOCASE

Also true with v3.31.0:
sqlite> select a from t0 where a LIKE '% castle%';
In my castle I have...
In my castle I have had...
In my castle I've never had...
In my Castle I have...
sqlite>

Just making sure... :-)

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

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

[Jose Isaias Cabrera]

Warren Young, on Tuesday, January 28, 2020 02:27 PM, wrote...
>
> On Jan 28, 2020, at 9:25 AM, Richard Hipp  wrote:
> >
> > On 1/28/20, Jan Danielsson  wrote:
> >> On 2020-01-28 00:19, Richard Hipp wrote:
> >>> daemon-less?
> >>
> >> This is my favorite, the only problem is that it is culturally more a
> >> Unix-y term.
> >
> > Since suggesting daemon-less, someone else (I'll have to research who,
> > exactly) suggested "server-free", which I think I like more.
>
> A, that’s too bad, because I just thought up a new tagline for you:
> “SQLite: exorcise your database daemons!”

Or

SQLite: No daemons in this database. :-)


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

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

[Jose Isaias Cabrera]

R Smith, on Tuesday, January 28, 2020 06:39 AM, wrote...
>
> I do not have a great suggestion to add, but to observe that the best
> suggestions I think are: NOT changing, (or if we have to) "Server-Free"
> or "Localized".

I agree with these, but localize is another buzz word for translation.

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

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

[Jose Isaias Cabrera]

Warren Young, on Monday, January 27, 2020 07:36 PM, wrote...
>
> On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
> >
> > "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me.”
>
> “Serverless” it a screwy buzzword anyway, because of course there’s
> still a server under its new meaning.
>
> My vote?  Keep using the term.  We were here first.

I agree.  It's an SQL engine without a server need, so it's SQL serverless.  Or 
"server needn't". or

Server? No.
SQL? Yes
Easy? Yes
Fast? Yes
Multi-platform? Yes
Choose any five.

> This is an ancient problem. It is why is any serious dictionary the count
> of definitions considerably exceeds the count of headwords. These new kids?
> “serverless, sense 2.”\

I actually don't believe is ancient.  My believe is that this probably started 
back in late 80's or early 90's with all of these words revision and forcing 
the meaning. Let's agree to disagree idealogy.

josé

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

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

[Jose Isaias Cabrera]

Richard Hipp, on Monday, January 27, 2020 05:18 PM, wrote...
>
> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".

It's kinda funny.  Back in 2006 I needed to create an app with SQL but on a 
local machine.  MySQL was too big for the simple app, so, I wanted something 
without a server.  So, I actually searched on "serverless SQL engine", and 
BOOOM!, sqlite.org came up.  Now you want to take that away from me. :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

David Raymond, on Monday, January 27, 2020 10:32 AM, wrote...

[clip]
> (c.WYear = 2020) is a perfectly valid expression... that's returning a
> boolean (well, int)
> So you're comparing c.WYear (from the subquery) against a boolean.

Yep, this little bit I knew. :-)

> (Others have replied with improved versions of the query, but for people
> following at home I figured I'd try to point out why the original version
> parsed ok and ran, just wasn't what you intended)

Fair enough...
The original email had inserts that would suffice the table that would create 
the 'YES' or the 'NO'. For example...

create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8, 
'2019-02-13');

create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j,  idate) values ('p004', 4, 5, 'y', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8, 
'2019-02-13');

create table t2 (pid, WYear);
insert into t2 values ('p001', 2019);
insert into t2 values ('p003', 2019);
insert into t2 values ('p004', 2019);
insert into t2 values ('p002', 2020);
insert into t2 values ('p003', 2020);
insert into t2 values ('p005', 2020);

As you can see, t2 contains data matches well with t0 and t1 regarding a, f and 
pid.  However, when I ran this on the real data, I found out that that there 
was data missing, ie.

insert into t0 (a, b, c, d, e, idate) values ('p006', 5, 2020, 'y', 8, 
'2019-03-13');
insert into t0 (a, b, c, d, e, idate) values ('p007', 5, 2020, 'n', 8, 
'2019-03-13');
insert into t0 (a, b, c, d, e, idate) values ('p008', 5, 2020, 'n', 8, 
'2019-03-13');

and

insert into t1 (f, g, h, i, j, idate) values ('p006', 6, 7, 'n', 8, 
'2019-03-13');
insert into t1 (f, g, h, i, j, idate) values ('p007', 6, 7, 'n', 8, 
'2019-03-13');
insert into t1 (f, g, h, i, j, idate) values ('p008', 6, 9, 'y', 8, 
'2019-03-13');

So, when I ran the original query,

SELECT a.a,
 a.c,
 a.e,
 b.g,
 b.h,
 b.i,
 coalesce((
   SELECT 'YES'
 FROM t2
WHERE wYear == a.c
  AND pid == a.a
  ),  'NO') AS digital
FROM t0 as a, t1 as b
   WHERE a.a == b.f
 AND a.idate == (SELECT MAX(idate) from t0 where a = a.a)
 AND b.idate == (SELECT MAX(idate) from t1 where f = a.a)
 AND a.a IN (SELECT pid FROM t2)
 AND a.c == 2020
;

it would only give me the records that were part of t2:

p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES

But, I also needed to display p006, p007, p008.  So, by taking 

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Jose Isaias Cabrera, on Monday, January 27, 2020 08:42 AM, wrote...
>
>
> Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote...

This is actually what I need:

SELECT a.a,
 a.c,
 a.e,
 b.g,
 b.h,
 b.i,
 coalesce((
   SELECT 'YES'
 FROM t2
WHERE wYear == a.c
  AND pid == a.a
  ),  'NO') AS digital
FROM t0 as a, t1 as b
   WHERE a.a == b.f
 AND a.idate == (SELECT MAX(idate) from t0 where a = a.a)
 AND b.idate == (SELECT MAX(idate) from t1 where f = a.a)
 AND a.c == 2020
;

Because there are other records that are in the other databases, but not in t2, 
which still need to be part of the result.  Thanks for everything Igor, Keith, 
Simon, and everyone who thought about it. ;-)

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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote...
>
>
> This version generates the most efficient query plan in 3.31.0 when you
> have indexes on the necessary columns:
>
> CREATE INDEX t0_1 on t0 (a, idate, c); -- c does not have to be in the
> index
> CREATE INDEX t1_1 on t1 (f, idate);
> CREATE INDEX t2_1 on t2 (pid, wyear); -- this could be a without rowid
> table with both columns in the primary key

I think I already have these INDEXes, but I will make sure.  Thanks for this 
one also.  I love having different ways to write the code.

> with keys (pid, idate0, idate1)
>   as (
>   select distinct pid,
>  (
>   select max(idate)
> from t0
>where a == pid
>  ),
>  (
>   select max(idate)
> from t1
>where f == pid
>  )
> from (
>   select distinct pid
> from t2
>  )
>  )
>   SELECT a.a,
>  a.c,
>  a.e,
>  b.g,
>  b.h,
>  b.i,
>  coalesce((
>SELECT 'YES'
>  FROM t2
> WHERE wYear == a.c
>   AND pid == a.a
>   ),  'NO') AS digital
> FROM t0 as a, t1 as b, keys
>WHERE a.a == keys.pid
>  AND b.f == keys.pid
>  AND a.idate == keys.idate0
>  AND b.idate == keys.idate1
>  AND a.c == 2020
> ;
>
> without help the query planner does not seem to generate a very good plan
> but maybe that is because the sample data is so small ... or maybe it does
> and I cannot tell with such small data ... but this forces the query to
> execute in the manner I think it should. If you take the "distinct" from
> the keys select it frees up the query planner to perhaps find a better plan
> -- you need the "select distinct pid from t2" to prevent duplicate rows.

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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Keith Medcalf, on Monday, January 27, 2020 02:28 AM, wrote...
>
>
> Do you perhaps mean:
>
>   SELECT a.a,
>  a.c,
>  a.e,
>  b.g,
>  b.h,
>  b.i,
>  coalesce((
>SELECT 'YES'
>  FROM t2
> WHERE wYear == a.c
>   AND pid == a.a
>   ),  'NO') AS digital
> FROM t0 as a, t1 as b
>WHERE a.a == b.f
>  AND a.idate == (SELECT MAX(idate) from t0 where a = a.a)
>  AND b.idate == (SELECT MAX(idate) from t1 where f = a.a)
>  AND a.a IN (SELECT pid FROM t2)
>  AND a.c == 2020
> ;
>

Yep, this one works also.  Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Keith Medcalf, on Sunday, January 26, 2020 11:19 PM, wrote...
>
>
> I get nothing at all except a complaint that the syntax is invalid. In
> particular
>
> (
> CASE
> (
>   SELECT WYear FROM t2 WHERE pid = a.a
> )
> WHEN c.WYear = 2020 THEN “YES”
> ELSE “NO” END
> ) AS DIGITAL
>
> Is not a valid scalar expression. Parsing fails at "WHEN".

This one does not give a syntax error and provides a result:

SELECT a.a, a.c, a.e, b.g, b.h, b.i,
(
CASE
(SELECT c.WYear FROM t2 WHERE pid = a.a)
WHEN c.WYear = 2020 THEN “YES” ELSE “NO” END
) AS DIGITAL
FROM t0 as a, t1 as b, t2 as c
WHERE a.a = b.f and a.a = c.pid
AND a.c = 2020
AND a.idate = (SELECT MAX(idate) from t0 where a = a.a)
AND b.idate = (SELECT MAX(idate) from t1 where f = a.a)
GROUP BY a.a
;

But, it gives me the wrong result.  I must have placed one of my many 
variations in the email with a syntax error.

> What exactly
> do you intend this scalar expression to do?

The idea is that if the pid is found in t2 with the same WYear (2020) I wanted 
to add YES to DIGITAL. Igor provided the solution.  For some reason I thought 
that I needed to give the full select to the CASE function.


> (and if the syntax was not invalid, the result would always be NO since
> it is impossible for the result of the subselect (which will always be a 4
> digit number because that is all that is in t2, or null, if not found (and
> a random year at that since there can be multiple rows with the same pid
> and you did not specify which one you want)) to be equal to the 0 or 1
> (true or false) boolean expression after the WHEN.

I must have given the wrong
> The fact that there's a Highway to Hell but only a Stairway to Heaven
> says a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users  On
> >Behalf Of Jose Isaias Cabrera
> >Sent: Sunday, 26 January, 2020 19:44
> >To: SQLite mailing list 
> >Subject: [sqlite] SQL CASE WHEN THEN ELSE END
> >
> >
> >Greetings!
> >
> >I am getting the wrong output, and I don't know how to get it to work.
> >Please take a look at the following (Pardon the lengthy data):
> >create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> >insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4,
> >'2019-02-11');
> >insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
> >'2019-02-11');
> >insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4,
> >'2019-02-11');
> >insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
> >'2019-02-11');
> >insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
> >'2019-02-11');
> >insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
> >'2019-02-12');
> >insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
> >'2019-02-12');
> >insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4,
> >'2019-02-12');
> >insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
> >'2019-02-12');
> >insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
> >'2019-02-12');
> >insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
> >'2019-02-13');
> >insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
> >'2019-02-13');
> >insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4,
> >'2019-02-13');
> >insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4,
> >'2019-02-13');
> >insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8,
> >'2019-02-13');
> >
> >create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> >insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4,
> >'2019-02-11');
> >insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4,
> >'2019-02-11');
> >insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4,
> >'2019-02-11');
> >insert into t1 (f, g, h, i, j,  idate) values ('p004', 4, 5, 'y', 4,
> >'2019-02-11');
> >insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4,
> >'2019-02-11');
> >insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4,
> >'2019-02-12');
> >insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4,
> >'2019-02-12');
> >insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4,
> >'2019-02-12');
> >insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4,
> >'2019-02-12');
> >insert into t1 (f, g, h, i, j, idate) values ('p005', 5,

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Simon Slavin, on Sunday, January 26, 2020 09:59 PM, wrote...
>
> On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera 
> wrote:
>
> > CASE
> >(
> >  SELECT WYear FROM t2 WHERE pid = a.a
> >)
> >WHEN c.WYear = 2020 THEN “YES”
> >ELSE “NO” END
>
> That's not the structure of a CASE statement.
>
> After CASE comes an expression.
> After WHEN comes another expression.
> If they equal one another, then the the bit after the THEN is returned.
>
> You want something more like
>
> SELECT
> (CASE WYear WHEN 2020 THEN 'YES' ELSE 'NO' END) AS DIGITAL
> FROM t2 WHERE pid = a.a
>
> but you'll have to fit this in with how your overall SELECT works.
>
> Sse "The CASE expression" on this page for more details:
>
> <https://sqlite.org/lang_expr.html>

Thanks.

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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Igor Tandetnik, on Sunday, January 26, 2020 09:57 PM, wrote...
>
> On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:
> >  CASE
> >  (
> >SELECT WYear FROM t2 WHERE pid = a.a
> >  )
> >  WHEN c.WYear = 2020 THEN “YES”
> >  ELSE “NO” END
> > ) AS DIGITAL
>
> This should probably be simply
>
> case c.WYear when 2020 then 'YES' else 'NO' end
>
> or equivalently
>
> case when c.WYear=2020 then 'YES' else 'NO' end

Thanks, Igor.

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

[sqlite] SQL CASE WHEN THEN ELSE END

[Jose Isaias Cabrera]

Greetings!

I am getting the wrong output, and I don't know how to get it to work.  Please 
take a look at the following (Pardon the lengthy data):
create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, 
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, 
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4, 
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8, 
'2019-02-13');

create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j,  idate) values ('p004', 4, 5, 'y', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4, 
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4, 
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8, 
'2019-02-13');

create table t2 (pid, WYear);
insert into t2 values ('p001', 2019);
insert into t2 values ('p003', 2019);
insert into t2 values ('p004', 2019);
insert into t2 values ('p002', 2020);
insert into t2 values ('p003', 2020);
insert into t2 values ('p005', 2020);

When I run this SELECT,

SELECT a.a, a.c, a.e, b.g, b.h, b.i,
(
CASE
(
  SELECT WYear FROM t2 WHERE pid = a.a
)
WHEN c.WYear = 2020 THEN “YES”
ELSE “NO” END
) AS DIGITAL
FROM t0 as a, t1 as b, t2 as c
WHERE a.a = b.f and a.a = c.pid
AND a.c = 2020
AND a.idate = (SELECT MAX(idate) from t0 where a = a.a)
AND b.idate = (SELECT MAX(idate) from t1 where f = a.a)
GROUP BY a.a
;

I get,

p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|NO
p005|2020|8|5|3|y|NO

I expected this,

p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES

I've tried a few changes, but nothing seems to work.  Thoughts please?  thanks.

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

Re: [sqlite] To edit in sqlite3 CLI multiline SQL statements?

[Jose Isaias Cabrera]

Simon Slavin, on Saturday, January 18, 2020 04:58 PM, wrote...
>
> On 18 Jan 2020, at 9:30pm, Csanyi Pal, on
>
> > can one edit a multiline SQL statement in the sqlite3 CLI?
>
> No.
>
> But if you make a multiline SQL statement in a text file you can paste it 
> into the CLI all in one operation.

Well, you could, but you need to hit the up arrow for every new line in that 
multiline, and edit the line. But, I agree with Simon: the best way to do it, 
is to use some text editor, and make the changes in the text editor and copy 
and paste to the CLI. Thanks.

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

Re: [sqlite] SQLite 3.31.0 enters pre-release testing

[Jose Isaias Cabrera]

Richard Hipp, on Friday, January 17, 2020 07:56 AM, wrote...

> A terse summary of changes can be seen at
> https://www.sqlite.org/draft/releaselog/current.html
No gain in speed? :-)  I love it when I see, "2% faster..."  Thanks.

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

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, January 14, 2020 11:49 AM, wrote...
>
>
> On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera
>  wrote:
>
> >That is an idea I have not thought about, Neal.  Thanks.  The boss comes
> >up with lots of requests, and these have to be done yesterday. I will
> >have to look into triggers. Have not used them yet. :-(
>
> Here is some sample triggers that records the changes as they happen ...
> create table t
> (
> n integer primary key,
> a not null,
> b,
> c,
> d,
> e,
> idate not null,
> unique (a, idate)
> );
> create table audit_t
> (
> a not null,
> changed not null,
> idate not null,
> variable not null,
> oldv,
> newv
> );
>
> create trigger t_upd_1 after insert on t begin
> insert into audit_t
> select new.a,
>julianday(),
>new.idate,
>'b',
> (select b from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>new.b as newv
>  where newv <> oldv;
> insert into audit_t
> select new.a,
>julianday(),
>new.idate,
>'c',
> (select c from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>new.c as newv
>  where newv <> oldv;
> insert into audit_t
> select new.a,
>julianday(),
>new.idate,
>'d',
> (select d from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>new.d as newv
>  where newv <> oldv;
> insert into audit_t
> select new.a,
>julianday(),
>new.idate,
>'e',
> (select e from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>new.e as newv
>  where newv <> oldv;
> end;
>
> create trigger t_upd_nocando before update of a, idate on t begin
>   select raise(ABORT, 'You cannot update columns a or idate');
> end;
>
> create trigger t_upd_b after update of b on t begin
>   insert into audit_t
>   select old.a,
>  julianday(),
>  old.idate,
>  'b',
>  old.b,
>  new.b;
> end;
> create trigger t_upd_c after update of c on t begin
>   insert into audit_t
>   select old.a,
>  julianday(),
>  old.idate,
>  'c',
>  old.c,
>  new.c;
> end;
> create trigger t_upd_d after update of d on t begin
>   insert into audit_t
>   select old.a,
>  julianday(),
>  old.idate,
>  'd',
>  old.d,
>  new.d;
> end;
> create trigger t_upd_e after update of e on t begin
>   insert into audit_t
>   select old.a,
>  julianday(),
>  old.idate,
>  'e',
>  old.e,
>  new.e;
> end;
>
>
> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
> '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4,
> '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8,
> '2019-02-13');
>
> select * from audit_t;
> a   changed   idate   variableoldvnewv
> --  -- -- -- --
> p0012458863.19728274  2019-02-12  b   1   10
> p0022458863.19728274  2019-02-12  c   2   4
> p0032458863.19728275  2019-02-12  d   n   y
> p00424

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

sub sk79, on Tuesday, January 14, 2020 10:54 AM, wrote...
>
> > Yes, a dumb of a system is provided daily
>
>
> There are some great solutions already here for offline processing as
> stated in your question. However, something you might also want to
> consider
> (if you have requisite access to the system) is to use ‘after insert’
> trigger(s) to online capture the change log  into a table and dump that
> table along with other tables. This would be easiest and fastest solution
> while not needing any indexes to be added or queries to be optimized.
That is an idea I have not thought about, Neal.  Thanks.  The boss comes up 
with lots of requests, and these have to be done yesterday. I will have to look 
into triggers. Have not used them yet. :-(

josé

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

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, January 14, 2020 09:04 AM, wrote...
>
>
> Assuming (a, idate) is indexed and unique, then give the following a
> whirl on your larger data. It does the same thing but does not use window
> functions to find the prior value -- it does a correlated subquery instead.
> I would expect that it is slower with real data than the window function
> version because it pushes the conditional into each union leg so that it
> does two correlated subqueries (one for the select and one for the where),
> but it might be interesting to see if it is actually slower. The window
> function version generates a huge internal VDBE program, but all the "bits"
> are co-routines.
>
>   select a,
>  idate,
>  variable,
>  oldv,
>  newv
> from (
> select a,
>idate,
>'b' as variable,
>(select b
>   from t
>  where a == o.a
>and idate < o.idate
>   order by idate desc
>  limit 1) as oldv,
>b as newv
>   from t as o
>  union
> select a,
>idate,
>'c' as variable,
>(select c
>   from t
>  where a == o.a
>and idate < o.idate
>   order by idate desc
>  limit 1) as oldv,
>c as newv
>   from t as o
>  union
> select a,
>idate,
>'d' as variable,
> (select d
>from t
>   where a == o.a
> and idate < o.idate
>order by idate desc
>   limit 1) as oldv,
> d as newv
>from t as o
>  union
>  select a,
> idate,
> 'e' as variable,
> (select e
>from t
>   where a == o.a
> and idate < o.idate
>order by idate desc
>   limit 1) as oldv,
> e as newv
>from t as o
>  )
>where oldv <> newv;

Thanks, Keith.  Will do.

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

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

Jean-Luc Hainaut, on Tuesday, January 14, 2020 07:25 AM, wrote...
>
>
> Another version that doesn't use CTE nor window functions:
>
> select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> andt2.idate = date(t1.idate,'+1 day')
> andt1.b <> t2.b
> union all
> select t1.a as Proj, t2.idate as "On", 'c' as Var, t1.c as oldVal, t2.c
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> andt2.idate = date(t1.idate,'+1 day')
> andt1.c <> t2.c
> union all
> select t1.a as Proj, t2.idate as "On", 'd' as Var, t1.d as oldVal, t2.d
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> andt2.idate = date(t1.idate,'+1 day')
> andt1.d <> t2.d
> union all
> select t1.a as Proj, t2.idate as "On", 'e' as Var, t1.e as oldVal, t2.e
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> andt2.idate = date(t1.idate,'+1 day')
> andt1.e <> t2.e
> order by Proj,"On";
>
> Valid if there is one state for each project on each date.

Thanks, Jean-Luc.  This one is also pretty fast. Interesting.

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

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

Keith Medcalf, on Monday, January 13, 2020 08:03 PM, wrote...
>
>
> And this version is several times faster since only the changes are
> union'd which minimizes the total number of records processed.
> The index should be "create index i on t (a, idate);" Because of the way
> indexes work, entries on the same a, idate will be ordered by n. (though
> really idate should be unique within a, so the index should be a unique
> index)
>
>   select a, idate, variable, oldv, newv
> from (
>   select a,
>  idate,
>  'b' as variable,
>  lag(b, 1) over (partition by a order by idate) as oldv,
>  b as newv
> from t
> order by a, idate
>  )
>where oldv <> newv
> union all
>   select a, idate, variable, oldv, newv
> from (
>   select a,
>  idate,
>  'c' as variable,
>  lag(c, 1) over (partition by a order by idate) as oldv,
>  c as newv
> from t
> order by a, idate
>  )
>where oldv <> newv
> union all
>   select a, idate, variable, oldv, newv
> from (
>   select a,
>  idate,
>  'd' as variable,
>  lag(d, 1) over (partition by a order by idate) as oldv,
>  d as newv
> from t
> order by a, idate
>  )
>where oldv <> newv
> union all
>   select a, idate, variable, oldv, newv
> from (
>   select a,
>  idate,
>  'e' as variable,
>  lag(e, 1) over (partition by a order by idate) as oldv,
>  e as newv
> from t
> order by a, idate
>  )
>where oldv <> newv
> order by a, idate, variable;
>

Wow!  Thanks, Keith.  Yes, this one is much faster.  As Ryan suggested, I will 
use this one. Thank so much. I really appreciate the help.  I already have an 
index on (a,idate).  I hope that I will be like you guys someday. ;-)  Thanks 
again.

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

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

R Smith, on Monday, January 13, 2020 06:49 PM, wrote...
>
>
> On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote:
> > R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​
> >> 
> > ​
> > Wow! Thanks for this. I had not thought about your questions. My boss
> said, I need to know all the changes per project whenever it happened.
> So,... I will have to revise my thinking, but I have enough with your help
> to continue. I am going to have to reload SQLitespeed, and try it again.
> :-) Thanks.​
>
> A - Es un placer,
Wow!  Spanish speaker also. :-)

> B - It's important to really understand how they want to see changes.
> Also I'm simply assuming (thanks to your example) that changes do not
> happen more frequently than once a day, and that the time of it is not
> important. If it is, the query will need to be adjusted.
Yes, a dumb of a system is provided daily and even if it happens more than once 
a day, the date contains hour also, which will also work with your example.  
Thanks.

> C - I know you probably know this, but just in case it isn't 100% clear:
> there is nothing about the SQL I posted that requires SQLitespeed. It is
> simply the easiest for me to use and it outputs SQL+Results the way I
> like it (so feel free), but that query will work in any SQLite platform
> for any version of SQLite - after 3.8 that is (or 3.7... or whatever
> version introduced CTE's, my memory is suddenly failing).
Yes.  I know.  But I like those stats. :-)  Thanks.

josé

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

Re: [sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​
> On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:​
> > Greetings!​
> >​
> > Please observe the following,​
> >​
> >​
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);​
> >​
> > insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
> > '2019-02-11');​
> >​
> > ...​
> >​
> > p001|1|10|column b changed on 2019-02-12​
> > p002|2|4|column c changed on 2019-02-12​
> > p003|n|y|column d changed on 2019-02-12​
> > sqlite>​
> >​
> > What I would like is to cover all of the dates in the same command, so that 
> > the output is this,​
> >​
> > p001|1|10|column b changed on 2019-02-12​
> > p002|2|4|column c changed on 2019-02-12​
> > p003|n|y|column d changed on 2019-02-12​
> > p004|4|5|column b changed on 2019-02-13​
> > p004|2|3|column c changed on 2019-02-13​
> > p004|y|n|column d changed on 2019-02-13​
> > p005|2|3|column c changed on 2019-02-13​
> > p005|4|8|column e changed on 2019-02-13​
> >​
> > Yes, I know I can get all the dates and build the SQL as I did above, but 
> > is there a way to do this in one call?  Thanks for the help.​
> ​
> ​
> Of course there is :)​
> ​
> Many questions are not clear though.​
> I assumed you are only interested in day-on-day changes, because in many ​
> days there can be many different values, if you measure them all against ​
> the last day, it will look like many changes between day X and the last ​
> day, when really you are probably interested only in the change from one ​
> day to the next day.​
> ​
> I also assumed that the days you will give as the input, let's call that ​
> min date and max date of the report, will refer to the days on which ​
> changed happened - which seems obvious, but the query must change if it ​
> is not the case.​
> ​
> Anyway, here is the SQL to achieve it. I made 2 queries, one in which it ​
> just gets the min and max from your data, and the other in which you can ​
> specify the min and max date. I guess the second one will be more what ​
> you want - the nice t hing is your calling program only needs to adjust ​
> those two values once, nowhere else:​
> ​
> (PS: I left the whole generated output in so you can see at the bottom, ​
> those errors are not errors, but just informing us that SQLite is ​
> creating indexes for those CTE tables, which is great and means this ​
> should be relatively fast  on large datasets even.)​
> ​
> ​
>-- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on SQLitespeed ​
> version 2.1.3.11.​
> ​
>-- Script Items: 4  Parameter Count: 0​
>-- 2020-01-14 00:08:17.875  |  [Info]   Script Initialized, ​
> Started executing...​
>-- ​
> ​
> ​
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);​
> ​
> insert into t (a, b, c, d, e, idate) values​
>   ('p001', 1, 2, 'n', 4, '2019-02-11')​
> ,('p002', 2, 2, 'n', 4, '2019-02-11')​
> ,('p003', 3, 2, 'n', 4, '2019-02-11')​
> ,('p004', 4, 2, 'y', 4, '2019-02-11')​
> ,('p005', 5, 2, 'y', 4, '2019-02-11')​
> ,('p001',10, 2, 'n', 4, '2019-02-12')​
> ,('p002', 2, 4, 'n', 4, '2019-02-12')​
> ,('p003', 3, 2, 'y', 4, '2019-02-12')​
> ,('p004', 4, 2, 'y', 4, '2019-02-12')​
> ,('p005', 5, 2, 'y', 4, '2019-02-12')​
> ,('p001',10, 2, 'n', 4, '2019-02-13')​
> ,('p002', 2, 4, 'n', 4, '2019-02-13')​
> ,('p003', 3, 2, 'y', 4, '2019-02-13')​
> ,('p004', 5, 3, 'n', 4, '2019-02-13')​
> ,('p005', 5, 3, 'y', 8, '2019-02-13')​
> ;​
> ​
> -- This version guesses the min and max dates...​
> WITH DSpan(minDate, maxDate) AS (​
>SELECT MIN(idate), MAX(idate) FROM t GROUP BY idate​
> ), DDays(dayOldDate, dayNewDate) AS (​
>SELECT minDate, date(minDate,'+1 day') FROM DSpan​
>UNION ALL​
>SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE ​
> DDays.dayNewDate, on ​
> ), Chg(a, idate, col, oldVal, newVal) AS (​
>SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b​
>  FROM DDays​
>  JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>  JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
> WHERE tNew.a = tOld.a AND tNew.b != tOld.b​
> UNION ALL​
>SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c​
>  FROM DDays​
>  JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>  JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
> WHERE tNew.a = tOld.a AND tNew.c != tOld.c​
> UNION ALL​
>SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d​
>  FROM DDays​
>  JOIN t AS tOld ON 

[sqlite] Capturing the changes in columns in a table

[Jose Isaias Cabrera]

Greetings!

Please observe the following,


create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);

insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');

insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');

insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');

insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');

insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');

insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-12');

insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, 
'2019-02-12');

insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, 
'2019-02-12');

insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-12');

insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-12');

insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4, 
'2019-02-13');

insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4, 
'2019-02-13');

insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4, 
'2019-02-13');

insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4, 
'2019-02-13');

insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8, 
'2019-02-13');


The SQL below provide info only for two dates (2019-02-11 and 2019-02-12):


select new.a,old.b,new.b, 'column b changed on ' || new.idate as info from t as 
new

  LEFT JOIN t as old ON

new.idate = '2019-02-12'

AND old.idate = '2019-02-11'

AND new.a = old.a

WHERE

  new.b != old.b

UNION ALL

select new.a,old.c,new.c, 'column c changed on ' || new.idate as info from t as 
new

  LEFT JOIN t as old ON

new.idate = '2019-02-12'

AND old.idate = '2019-02-11'

AND new.a = old.a

WHERE

  new.c != old.c

UNION ALL

select new.a,old.d,new.d, 'column d changed on ' || new.idate from t as new

  LEFT JOIN t as old ON

new.idate = '2019-02-12'

AND old.idate = '2019-02-11'

AND new.a = old.a

WHERE

  new.d != old.d

UNION ALL

select new.a,old.e,new.e, 'column e changed on ' || new.idate from t as new

  LEFT JOIN t as old ON

new.idate = '2019-02-12'

AND old.idate = '2019-02-11'

AND new.a = old.a

WHERE

  new.e != old.e

ORDER by new.a;


p001|1|10|column b changed on 2019-02-12
p002|2|4|column c changed on 2019-02-12
p003|n|y|column d changed on 2019-02-12
sqlite>

What I would like is to cover all of the dates in the same command, so that the 
output is this,

p001|1|10|column b changed on 2019-02-12
p002|2|4|column c changed on 2019-02-12
p003|n|y|column d changed on 2019-02-12
p004|4|5|column b changed on 2019-02-13
p004|2|3|column c changed on 2019-02-13
p004|y|n|column d changed on 2019-02-13
p005|2|3|column c changed on 2019-02-13
p005|4|8|column e changed on 2019-02-13

Yes, I know I can get all the dates and build the SQL as I did above, but is 
there a way to do this in one call?  Thanks for the help.

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

Re: [sqlite] A hang in Sqlite

[Jose Isaias Cabrera]

Dominique Devienne, on Monday, January 6, 2020 09:14 AM, wrote...
>
> On Mon, Jan 6, 2020 at 2:36 PM Jose Isaias Cabrera 
> wrote:
>
> > Dominique Devienne, on Monday, January 6, 2020 07:51 AM, wrote...
> > [...] it's "polluting" a bit this ML.
> >
> > This is why we are all so different.  I, actually enjoy the "pollution"
> > because I try to look at the code and, some times, learn from it. But,
> yes,
> > I am learning, so this is new to me. Perhaps, if I were to have more
> SQL
> > knowledge, perhaps I would think the same.
>
>
> Sure. I get that. But learning from fuzzer SQL is probably not the best
> way
> to go about it, IMHO :)
> They go into dark corners and even nonsensical SQL to find bugs, so
> hardly
> newbie material.

I agree with this.  Some of those codes were really obscure, and WY 
over my head. ;-)

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

Re: [sqlite] A hang in Sqlite

[Jose Isaias Cabrera]

Dominique Devienne, on Monday, January 6, 2020 07:51 AM, wrote...
>
> On Mon, Jan 6, 2020 at 8:30 AM Dan Kennedy  wrote:
>
> > On 6/1/63 13:44, Yongheng Chen wrote:
> > > We found a test case that hangs Sqlite:
> >
> > Thanks for all the work you've been doing on SQLite!
> >
>
> Indeed. But...
>
> The frequency of all these fuzzer related emails has reached a point IMHO
> that it's "polluting" a bit this ML.

This is why we are all so different.  I, actually enjoy the "pollution" because 
I try to look at the code and, some times, learn from it. But, yes, I am 
learning, so this is new to me.  Perhaps, if I were to have more SQL knowledge, 
perhaps I would think the same.

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

Re: [sqlite] A hang in Sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Monday, January 6, 2020 01:44 AM, wrote...
>
> Hi,
>
> We found a test case that hangs Sqlite:
> —
> CREATE TEMPORARY TABLE v0 ( v1 INT UNIQUE ) ;
> WITH RECURSIVE v0 ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM v0 )
> SELECT 'x' from v0;
> —
>
> This seems triggering a dead loop. However, since v0 is empty, it might
> not enter a dead loop I think ? We are not sure whether this is a bug.

Yep, infinite loop with 3.30.0.  Continues to display x and new line.  Thanks.

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

[sqlite] Happy New 2020 to everyone on the list...

[Jose Isaias Cabrera]

Dr. Hipp, and the SQLite team, the sqlite-users group, happy new year!  I wish 
the best for everyone here, but better yet, that you find TRUE not be FALSE.

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

Re: [sqlite] Causal profiling

[Jose Isaias Cabrera]

D Burgess, on Wednesday, January 1, 2020 08:19 PM, wrote...
>
> > I’ve spent too much time lately trying to figure out or debug
> hellacious C spaghetti code
>
> And I’ve spent too much time lately trying to figure out or debug
> hellacious C++ spaghetti code
>
> Someone who writes bad C,   will write even worse C++

Very true.  And for that matter, any other language too.  Except assembler. ;-)

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

Re: [sqlite] sqlite3VdbeMemAboutToChange(Vdbe *, Mem *): Assertion `(mFlags_Str)==0 || (pMem->n==pX->n && pMem->z==pX->z)' failed.

[Jose Isaias Cabrera]

Richard Hipp, on Tuesday, December 31, 2019 08:33 AM, wrote...
>
> On 12/31/19, Jose Isaias Cabrera  wrote:
> >
> > I don't know what "This bug affects debug builds...",
>
> This is yet another fault not in SQLite itself, but in the auxiliary
> VM register tracking logic that we use during testing and debugging.
> Hence, it only comes up if you compile with -DSQLITE_DEBUG.  An
> ordinary build works find and gives a correct answer.
>
> The VM register tracking logic does for the VM (approximately) what
> ASAN does for machine code - it tries to identify cases where the byte
> code is violating assumptions about the operation of the VM.
>
> In this particular instance, the issue raised by the VM register
> tracking logic was a false-positive.  The VM register tracking logic
> has been enhanced on trunk to avoid this particular false-positive, so
> the problem should no longer occur.

Thank you, Dr. Hipp.

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

Re: [sqlite] 18 minutes 41 seconds

[Jose Isaias Cabrera]

Indeed!  Thanks Dr. Hipp and the rest of the team for such a wonderful, and 
easy, and light, and robust, and... product.

josé


From: sqlite-users  on behalf of 
Manuel Rigger 
Sent: Tuesday, December 31, 2019 05:15 AM
To: SQLite mailing list 
Subject: Re: [sqlite] 18 minutes 41 seconds

Thanks for all your great work, Richard and Dan! Among all DBMS that we
have been testing, we have put most of our effort and energy into testing
SQLite. The reason for that is that you were by far the most responsive to
our bug reports, and typically address bugs immediately after we find them!
It's great that you take all bug reports seriously. In other widely-used
DBMS that we have been testing, bugs take weeks, months, or longer until
getting fixed.

Looking forward to another fruitful year of cooperating in making SQLite
even more robust!

Best,
Manuel

On Tue, Dec 31, 2019 at 7:26 AM Michael Falconer <
michael.j.falco...@gmail.com> wrote:

> >
> > There is no "year 0" between 1 BC and 1 AD.  This is perhaps the most
> > common fencepost problem in existance.  The "great renaming" of AD to CE
> > and doing away with BC by replacing them with "off by one" numbers less
> > than 1 does not change the fact that there was, in fact, no year 0.
>
> Obviously the character(s) responsible  for dates etc were NOT C
> programmers!
>
> On Tue, 31 Dec 2019 at 14:45, Richard Damon 
> wrote:
>
> > On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote:
> > > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf 
> > wrote:
> > >>
> > >> On Monday, 30 December, 2019 19:29, Michael Falconer <
> > michael.j.falco...@gmail.com> wrote:
> > >>
> > >>> As we approach the end of yet another year ( and indeed decade ).
> > >> Technically, every year is the end of a decade, if one means the
> > immediately preceding ten years.
> > >>
> > >> However, if you mean the end of the second decade of the 21st century,
> > you will have to wait another year for that.  January 1st, 0001 AD was
> the
> > first day of the year 1.  The first decade ended at the end of December
> > 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic
> > Gregorian calendar).
> > > Languages don't work like this.
> > >
> > > https://www.collinsdictionary.com/dictionary/english/decade
> > >
> > > Cheers
> >
> > Its a difference between ordinals and numerals. The 20th century was
> > from the beginning of 1901 to the end of 2000. We also have the century
> > called the 1900's which went from 1900 to the end of 1999.
> >
> > Decade would work the same way, the 202st decade goes from 2011 to end
> > of 2020, but the 2010s go from 2010 to end of 2019.
> >
> > --
> > Richard Damon
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Regards,
>  Michael.j.Falconer.
> ___
> 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] sqlite3VdbeMemAboutToChange(Vdbe *, Mem *): Assertion `(mFlags_Str)==0 || (pMem->n==pX->n && pMem->z==pX->z)' failed.

[Jose Isaias Cabrera]

Yongheng Chen, on Tuesday, December 31, 2019 03:21 AM, wrote...
>
> Hi,
>
> We found a debug assertion bug in sqlite. Here’s the PoC:
> —
> CREATE TABLE v0 ( v1 , v2 FLOAT ) ;
> CREATE TRIGGER x AFTER INSERT ON v0
> BEGIN
> INSERT INTO v0 SELECT DISTINCT v2 / 10 , v2 / 1 FROM v0 ;
> END;
> INSERT INTO v0 ( v1 , v1 ) VALUES ( '' , 10 ) ,( '' , 0 ) ,( 'AIR' , 10
> );
> UPDATE v0 SET v2 = randomblob ( v2 ) ;
> SELECT coalesce ( max ( quote ( v1 ) ) , 10 ) FROM v0 GROUP BY v1 ;
> —
>
> This bug affects debug builds based on trunk and the latest release
> version.
>
> Special thanks to Manuel Rigger for all his help. And thanks every one in
> the sqlite team for your great work.

I don't know what "This bug affects debug builds...", this worked on 3.30.0 
released version.

 8:10:31.22>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 , v2 FLOAT ) ;
sqlite> CREATE TRIGGER x AFTER INSERT ON v0
   ...> BEGIN
   ...> INSERT INTO v0 SELECT DISTINCT v2 / 10 , v2 / 1 FROM v0 ;
   ...> END;
sqlite> INSERT INTO v0 ( v1 , v1 ) VALUES ( '' , 10 ) ,( '' , 0 ) ,( 'AIR' , 10 
);
sqlite> UPDATE v0 SET v2 = randomblob ( v2 ) ;
sqlite> SELECT coalesce ( max ( quote ( v1 ) ) , 10 ) FROM v0 GROUP BY v1 ;
NULL
''
'AIR'
sqlite>

But, I am not running any debugger. ;-)

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

Re: [sqlite] SQL help

[Jose Isaias Cabrera]

True that. ;-)


From: sqlite-users  on behalf of 
x 
Sent: Tuesday, December 31, 2019 03:15 AM
To: SQLite mailing list 
Subject: Re: [sqlite] SQL help

LOL. English isnae oor furst language either. Ye ken whit a mean. 




From: sqlite-users  on behalf of 
Jose Isaias Cabrera 
Sent: Monday, December 30, 2019 9:50:22 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] SQL help



x, on Saturday, December 28, 2019 10:46 AM, wrote...

> Apologies if that’s not worded correctly. Scottish education could be
> done gooder 

Wrong English! You should have said, "Scottish education could have been done 
gooder."  Com'on! This is not even my first language! :-)

josé
___
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] SQL help

[Jose Isaias Cabrera]

x, on Saturday, December 28, 2019 10:46 AM, wrote...

> Apologies if that’s not worded correctly. Scottish education could be
> done gooder 

Wrong English! You should have said, "Scottish education could have been done 
gooder."  Com'on! This is not even my first language! :-)

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

Re: [sqlite] Bug Report

[Jose Isaias Cabrera]

Bigthing Do, on Friday, December 27, 2019 01:56 PM, wrote...
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1
> ) SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY
> col1 DESC ) FROM table1 ;
>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10
> 20:19:45`

Also with 3.30.0...

16:41:27.70>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM 
table1 ) SELECT col2 FROM table1 ORDER BY 1 ;
sqlite> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER 
BY col1 DESC ) FROM table1 ;

16:42:07.53>

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

Re: [sqlite] Sanitising user input for FTS5 MATCH parameter

[Jose Isaias Cabrera]

test user, on Friday, December 20, 2019 10:03 AM, wrote...
>
> Hello,
>
> I have a search box on a website that uses FTS5/MATCH.
>
> MATCH seems to take its own custom language for matching.
>
> 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind
> FFI?
>
> - This would give them full access to the FTS5 matching language.
>
> 2. If not, how should I be sanitising user input?
>
> - E.g. How can I transform a string of words and text into a query? What
> characters should I be removing or escaping? How can I prevent them using
> the FTS5 keywords "AND" "OR" etc?

Have you taken a look at the FTS5 site[1]?  It has lots of information there 
that may be helpful.

josé

[1] https://www.sqlite.org/fts5.html

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

Re: [sqlite] Crash Bug in Sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Thursday, December 19, 2019 04:29 PM, wrote...
>
> Hi,
>
> We found another crash in Sqlite. Here’s the POC:
> —
> CREATE TABLE v0 ( v1 , v2 ) ;
> SELECT 10 , 1 UNION SELECT v2 , dense_rank () OVER( ORDER BY - 10 ) FROM
> v0 ;
> —
>
> This bug exists in both the latest development code and the release code.

Indeed, sqlite v3.30.0 for Windows crashes with this set of instructions...

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

Re: [sqlite] Crash bug in Sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Thursday, December 19, 2019 01:21 PM, wrote...
>
> Hi,
>
> We found another crash in Sqlite. Here’s the POC:
> —
> CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ;
> CREATE VIEW v2 ( v3 ) AS SELECT DISTINCT ( SELECT DISTINCT v1 , v1 , v1 ,
> v3 , v1 , v3 , v1 , 10.10 ) ;
> CREATE TABLE v4 ( v5 INTEGER PRIMARY KEY , v6 INT );
> DELETE FROM v0 WHERE NULL BETWEEN ( SELECT v1 FROM v0 AS x GROUP BY v1
> ORDER BY 10 + sum ( v1 ) OVER( ORDER BY - 10 ) DESC ) AND 10 ;

Entering each of those lines above, one at a time, when I hit enter after the 
last line above,
sqlite3 crashes right after.  So, I don't even need the line below.  This is 
using sqlite3
v3.30.0 in Windows.  Just an FYI.

> SELECT * FROM v4 , v2 WHERE v3 = v3 AND v3 = 10;
> —
>
> This bug exists in both the latest development code and the release code.
> (And thanks josé for the confirmation every time we report a bug).

You're welcome.  I have SQlite3 tool opened, so it's a "nice copy and paste" 
and see your
replication text/steps go to work. ;-)

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

Re: [sqlite] Crash bug in Sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Thursday, December 19, 2019 09:54 AM, wrote...
>
> Hi,
>
> We found another crash in Sqlite. Here’s the POC:
> —
> CREATE TABLE v0 ( v7 FLOAT , v3 DOUBLE , v6 TEXT , v1 INTEGER UNIQUE , v5
> DOUBLE , v2 VARCHAR(20) UNIQUE , v4 ) ;
> REPLACE INTO v0 ( v6 , v3 , v2 ) VALUES ( 10 , 10 , 10 );
> CREATE VIRTUAL TABLE v8 USING zipfile ( v9 DOUBLE ) ;
> REPLACE INTO v8 SELECT * FROM v0;
> —
>
> This bug exists in both the latest development code and the release code.

Crash confirmed in Windows Sqlite3 v3.30.0. Just fyi...

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

Re: [sqlite] Crash bug in Sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Wednesday, December 18, 2019 10:45 AM, wrote...
>
> Hi,
>
> We found another crash in Sqlite. Here’s the POC:
>
> —
> CREATE TABLE v0 ( v2 INTEGER UNIQUE ON CONFLICT IGNORE , v1 TEXT PRIMARY
> KEY ) ;
> CREATE VIEW v3 ( v4 ) AS SELECT v2 IN ( 9223372036854775808 , ( printf ()
> IN ( 0 , 0 ) ) , 10 , 10 , 10 ) AS AVG_YEARLY FROM v0 ;
> CREATE TABLE v5 ( v6 , v7 ) ; INSERT INTO v0 VALUES ( 1.10 , 'y' ) ;
> INSERT INTO v5 VALUES ( 10 , 10 ) ;
> INSERT INTO v0 VALUES ( 10 , 10 ) ;
> SELECT DISTINCT v4 FROM v0 LEFT JOIN v3 ON v4 = 10 OR v4 = v3 . v4 ORDER
> BY v4 ;
> —
>
> This bug exists in both the latest development code and the release code.

Yep, took down sqlite3.exe with 3.30.0.  Just fyi.

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

Re: [sqlite] Crash Bug in Sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Tuesday, December 17, 2019 04:21 PM, wrote...
>
> Hi,
>
> We found a bug that crashes Sqlite. Here’s the test case:
>
> ——
> CREATE TABLE v0 ( v1 UNIQUE , v2 VARCHAR(80) NULL PRIMARY KEY ) ;
> CREATE VIEW v3 ( v4 ) AS SELECT max ( ( SELECT count ( v1 ) OVER( ORDER
> BY 10 ASC ) ) ) FROM v0 ;
> SELECT * FROM v3 WHERE - 'b' >= v4 AND v4 > 10 OR ( v4 BETWEEN 10 AND 10
> );
> ——
>
> This bug exists in both the development code and the latest release code.

Yep, 3.30.0 has the problem.

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

Re: [sqlite] Performance vs. memory trade-off question

[Jose Isaias Cabrera]

Give me speed anytime and twice on Sundays...


From: sqlite-users  on behalf of 
Eric Grange 
Sent: Saturday, December 14, 2019 04:11 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Performance vs. memory trade-off question

While I am using SQLIite mostly in x86-64 with plentiful RAM, I suspect the
reduced lookaside size will help CPU L1/L2 caches, which is definitely
never plentiful. I would not be surprised if it resulted in a speedup in
practical high-load multi-threaded scenarios.

Also future SQLite code changes may be able to further build upon a new
lookaside configuration by tweaking the structure sizes to more tightly
match with the slot sizes f.i., and reduce the performance hit even in
single-threaded cases.

So "on by default" for me.

Eric

Le sam. 14 déc. 2019 à 14:27, Richard Hipp  a écrit :

> A new feature on a branch has the following disadvantages:
>
> (1)  It uses about 0.25% more CPU cycles.  (Each release of SQLite is
> normally about 0.5% faster, so enabling this feature is sort of like
> going back by one-half of a release cycle.)
>
> (2)  The code space (the size of the library) is between 400 and 500
> bytes larger (depending on compiler and optimization settings).
>
> The this one advantage:
>
> (3)  Each database connection uses about 72 KB less heap space.
>
> QUESTION:  Should this feature be default-on or default-off?
>
> What's more important to you?  0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
> The feature can be activated or deactivated at start-time, but you
> take the disadvantages (the performance hit and slightly larger
> library size) regardless, unless you disable the feature at
> compile-time.  If the feature is compile-time disabled, then the
> corresponding code is omitted and and it cannot be turned on at
> start-time.
>
> If you have opinions, you can reply to this mailing 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between hex notation and string notation

[Jose Isaias Cabrera]

Yeah, mine was vanilla compiled,

sqlite> pragma compile_options;
COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
sqlite>



From: sqlite-users  on behalf of 
David Raymond 
Sent: Friday, December 13, 2019 03:14 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Difference between hex notation and string notation

Run...
pragma compile_options;
...and see if LIKE_DOESNT_MATCH_BLOBS is in the list that comes up.

If it is NOT in the list then both rows will show up. This is the case for the 
precompiled Windows cli for example.

If it IS in the list, then only the second one that was inserted as text will 
show up, and the blob will not be selected.

https://www.sqlite.org/compile.html#like_doesnt_match_blobs

SQLITE_LIKE_DOESNT_MATCH_BLOBS

This compile-time option causes the LIKE operator to always return False if 
either operand is a BLOB. The default behavior of LIKE is that BLOB operands 
are cast to TEXT before the comparison is done.

This compile-time option makes SQLite run more efficiently when processing 
queries that use the LIKE operator, at the expense of breaking backwards 
compatibility. However, the backwards compatibility break may be only a 
technicality. There was a long-standing bug in the LIKE processing logic (see 
https://www.sqlite.org/src/info/05f43be8fdda9f) that caused it to misbehavior 
for BLOB operands and nobody observed that bug in nearly 10 years of active 
use. So for more users, it is probably safe to enable this compile-time option 
and thereby save a little CPU time on LIKE queries.

This compile-time option affects the SQL LIKE operator only and has no 
impact on the sqlite3_strlike() C-language interface.


Since I just pasted it there's typo in there. "caused it to misbehavior" should 
be "caused it to misbehave"


-Original Message-
From: sqlite-users  On Behalf Of 
Jose Isaias Cabrera
Sent: Friday, December 13, 2019 2:58 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Difference between hex notation and string notation


Sascha Ziemann, on Friday, December 13, 2019 04:16 AM, wrote...
>
> I have a problem to find rows in a database when I write in hex notation:
>
> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
> INSERT INTO LOG VALUES
>
> (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
> down');
> SELECT ROWID,MSG FROM LOG; --
> returns both rows
> SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
> returns just the second
> SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; --
> returns both rows
>
> This looks like a bug to me.

Update to 3.30.0 or higher.  It works fine for me...
14:55:34.46>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
sqlite> INSERT INTO LOG VALUES
   ...> 
(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
sqlite> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
   ...> down');
sqlite> SELECT ROWID,MSG FROM LOG;
1|facility=daemon;component=named;text=shutting down
2|facility=daemon;component=named;text=shutting
down
sqlite>
___
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] Difference between hex notation and string notation

[Jose Isaias Cabrera]

Sascha Ziemann, on Friday, December 13, 2019 04:16 AM, wrote...
>
> I have a problem to find rows in a database when I write in hex notation:
>
> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
> INSERT INTO LOG VALUES
>
> (X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
> down');
> SELECT ROWID,MSG FROM LOG; --
> returns both rows
> SELECT ROWID,MSG FROM LOG WHERE MSG LIKE '%down';  --
> returns just the second
> SELECT ROWID,MSG FROM LOG WHERE CAST(MSG AS VARCHAR) LIKE '%down'; --
> returns both rows
>
> This looks like a bug to me.

Update to 3.30.0 or higher.  It works fine for me...
14:55:34.46>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE LOG (MSG VARCHAR(6291456) NOT NULL);
sqlite> INSERT INTO LOG VALUES
   ...> 
(X'666163696c6974793d6461656d6f6e3b636f6d706f6e656e743d6e616d65643b746578743d7368757474696e6720646f776e');
sqlite> INSERT INTO LOG VALUES ('facility=daemon;component=named;text=shutting
   ...> down');
sqlite> SELECT ROWID,MSG FROM LOG;
1|facility=daemon;component=named;text=shutting down
2|facility=daemon;component=named;text=shutting
down
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A crash bug in sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Monday, December 9, 2019 11:14 AM, wrote...
>
> So should we just report the bugs after another release version? We think the 
> sooner
> the bugs get fixed, the better in terms of security, as this approach can 
> minimize
> the number of bugs in future release.

Yongheng, ignore my comments.  I was trying to be funny.  Continue on as Dr. 
Hipp, or whomever has instructed.

Solomon, a proverbist once said, "In the multitude of words there wants not 
sin: but he that refrains his lips is wise." It should probably say, "he that 
refrains his typing, is wise."

> > On Dec 9, 2019, at 10:56 AM, Jose Isaias Cabrera, on
> >
> >
> > Since no one explains... ;-)
> >
> > Richard Hipp, on Monday, December 9, 2019 10:53 AM, wrote...
> >>
> >> On 12/9/19, Jose Isaias Cabrera, on
> >>> Error: near "AS": syntax error
> >>>
> >>> So, I can't replicate your problem.  thanks.
> >>
> >> You have to run off of the latest trunk version, as they are fuzzing
> >> for features that are unreleased.

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

Re: [sqlite] A crash bug in sqlite

[Jose Isaias Cabrera]

Since no one explains... ;-)

Richard Hipp, on Monday, December 9, 2019 10:53 AM, wrote...
>
> On 12/9/19, Jose Isaias Cabrera, on
> > Error: near "AS": syntax error
> >
> > So, I can't replicate your problem.  thanks.
>
> You have to run off of the latest trunk version, as they are fuzzing
> for features that are unreleased.

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

Re: [sqlite] A crash bug in sqlite

[Jose Isaias Cabrera]

Yongheng Chen, on Monday, December 9, 2019 10:16 AM, wrote...
>
> Hi,
>
> We found a crash bug in sqlite of master branch. Here’s the POC
> —
> CREATE TABLE v0 ( v2 DOUBLE CHECK( ( v2 IN ( v2 , v1) ) ) , v1 UNIQUE AS( v2 
> > v2 ) ) ;
> INSERT INTO v0 VALUES ( 10 );
> SELECT v0 . v1 , v0 . v1 FROM v0 JOIN v0 USING ( v1 , v1) ;
> —
> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and "SQLite 
> version 3.31.0
> 2019-12-09 08:13:43”. We haven’t tested other versions yet. Thanks

I get "Error: near "AS": syntax error" after the first line:

sqlite> CREATE TABLE v0 ( v2 DOUBLE CHECK( ( v2 IN ( v2 , v1) ) ) , v1 UNIQUE 
AS( v2 > v2 ) ) ;
Error: near "AS": syntax error

So, I can't replicate your problem.  thanks.

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

Re: [sqlite] Crash Bug Report

[Jose Isaias Cabrera]

Yongheng Chen, on Sunday, December 8, 2019 02:51 PM, wrote...
>
> Hi,
>
> We found one crash bug in sqlite, which causes a dead loop and then OOM.
> 
> CREATE TABLE v0 ( v1 ) ;
> CREATE TABLE v2 ( v3 ) ;
> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 AS x 
> FROM v2 ;
> DROP TRIGGER IF EXISTS x ;
> ALTER TABLE v2 RENAME TO t3;
> 
> The bug exists in "SQLite version 3.31.0 2019-12-08 00:06:39” and before.
>
> We reported this bug to one of the developers but didn’t get response. And we 
> reported
> several bugs to the same person one-to-one before (which was required by him) 
> and the
> bugs got fixed but we never got any credits for them. We hope somebody else 
> can help
> us. Thanks.

This definitely crashes in Windows 10:

17:21:58.01>sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE v0 ( v1 ) ;
sqlite> CREATE TABLE v2 ( v3 ) ;
sqlite> CREATE VIEW v4 AS WITH x AS ( SELECT x () OVER( ) FROM v4 ) SELECT v3 
AS x FROM v2 ;
sqlite> DROP TRIGGER IF EXISTS x ;
sqlite> ALTER TABLE v2 RENAME TO t3;

17:23:08.57>

Just fyi. thanks.

josé

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

Re: [sqlite] .expert disables loaded extensions

[Jose Isaias Cabrera]

Thanks, Keith.


From: Keith Medcalf 
Sent: Friday, December 6, 2019 05:34 PM
To: SQLite mailing list 
Cc: Jose Isaias Cabrera 
Subject: RE: .expert disables loaded extensions


On Friday, 6 December, 2019 07:49. Jose Isaias Cabrera  
wrote:

>please observe the following:
> 9:45:49.39>sqlite3
>SQLite version 3.30.0 2019-10-04 15:03:17
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
>sqlite> select decStr('1.7654');
>1.7654
>sqlite> .expert
>sqlite> select decStr('1.7654');
>Error: no such function: decStr
>sqlite>
>
>This is kind of a drag because I use expert a lot for setting indexes
>when searches are slow.  Any thoughts?  Thanks.

Actually, .expert opens another connection, and that connection does not have 
any non-autoinit extensions loaded.  You can "fix" this by applying the 
following patch to decimal.c which adds a static to keep track of the fact that 
the module has been loaded, and then adds it to the autoinit list so that it is 
activated on all subsequent connections automatically.  This means that with 
this patch you only need to load the module once and it will automatically be 
active on all subsequently created connections ... you do not need to load it 
each time, only once per process.

Index: ext/private/decimal/decimal.c
==
--- ext/private/decimal/decimal.c
+++ ext/private/decimal/decimal.c
@@ -712,17 +712,18 @@

 #endif /* SQLITE_OMIT_VIRTUALTABLE */

 #pragma mark Public interface

+static int autoinit = 1;
+
 /**
  ** \brief Entry point of the SQLite3 Decimal extension.
  **/
 #ifdef _WIN32
 __declspec(dllexport)
 #endif
-
 int sqlite3_decimal_init(sqlite3* db, char** pzErrMsg, sqlite3_api_routines 
const* pApi) {

   (void)pzErrMsg;

   int rc = SQLITE_OK;
@@ -846,9 +847,10 @@
   if (rc == SQLITE_OK) {
 rc = sqlite3_create_module_v2(db, SQLITE_DECIMAL_PREFIX "Context",
   , decimalSharedContext, 
decimalContextDestroy);
   }
 #endif
-
-  return rc;
+  if (autoinit) sqlite3_auto_extension((void*)sqlite3_decimal_init);
+  autoinit = 0;
+  return rc == SQLITE_OK ? SQLITE_OK_LOAD_PERMANENTLY : rc;
 }

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

[sqlite] .expert disables loaded extensions

[Jose Isaias Cabrera]

Greetings.

please observe the following:
 9:45:49.39>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
sqlite> select decStr('1.7654');
1.7654
sqlite> .expert
sqlite> select decStr('1.7654');
Error: no such function: decStr
sqlite>

This is kind of a drag because I use expert a lot for setting indexes when 
searches are slow.  Any thoughts?  Thanks.

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

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Jose Isaias Cabrera]

Octopus ZHANG, on Friday, December 6, 2019 06:18 AM, wrote...
>
> Hi all,
>
> I'm trying to fuzz sqlite, and I found the following query was executed for
> more than one minute. (./sqlite3, on
>
> >> SELECT
> printf('%*.*c',9||00600&66,1003)""WHERE""/"";
>
> I also turned on the timer, but no time was printed. So I used `time` to
> record:
> +--+---+
> | real | 1m38.036s |
> | user | 1m36.086s |
> | sys  |  0m1.948s |
> +--+---+

> Here is how to reproduce:

> OS: Linux 18.04.3 LTS, 4.15.0-65-generic
> SQLite version 3.30.1 2019-10-10 20:19:45 (used default command to build)

I actually ran out of memory...

 8:18:59.35>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite>  SELECT
   ...> 
printf('%*.*c',9||00600&66,1003)""WHERE""/"";
Run Time: real 12.191 user 11.296875 sys 0.796875
Error: out of memory
sqlite>

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

Re: [sqlite] Enable Or Disable Extension Loading

[Jose Isaias Cabrera]

Keith Medcalf, on Thursday, December 5, 2019 02:24 PM, wrote...
> On Thursday, 5 December, 2019 11:39, Jose Isaias Cabrera, on
> >Just to be sure...
> >
> >The function,
> >
> >int sqlite3_enable_load_extension(sqlite3 *db, int onoff);
> >
> >enables or disables a database to allow or disallow the loading of
> >extensions[1].  Once it's set, will it stay on?  Or does one need to be
> >turn it on every time one connects to the database?  It appears that the
> >latter is the correct behavior, but I just want to make sure.  Thanks.
>
> Each time a connection is opened the default setting of the load_extension 
> flag is
> set according the SQLITE_ENABLE_LOAD_EXTENSION compile time define, which
> defaults to 0.
>
> Using either the sqlite3_enable_load_extension or sqlite3_db_config makes a 
> change
> to the setting for that connection only which remains in effect until the 
> connection
> is closed or the configuration of the connection is changed again (using the 
> same API
> calls).  Each connection needs to enable the loading of extensions separately 
> and the
> change only applies to that connection.  (It applies to the connection, not 
> the database)
>
> See also:  https://sqlite.org/c3ref/load_extension.html

Thanks.  Yes, I saw the load extension text, but somehow it left me thinking 
that it was a setting per DB and not per connection.  All clear now. Thanks.

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

[sqlite] Enable Or Disable Extension Loading

[Jose Isaias Cabrera]

Greetings!

Just to be sure...

The function,

int sqlite3_enable_load_extension(sqlite3 *db, int onoff);

enables or disables a database to allow or disallow the loading of 
extensions[1].  Once it's set, will it stay on?  Or does one need to be turn it 
on every time one connects to the database?  It appears that the latter is the 
correct behavior, but I just want to make sure.  Thanks.

josé

[1] https://www.sqlite.org/c3ref/enable_load_extension.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA for .load

[Jose Isaias Cabrera]

David Raymond, on Tuesday, December 3, 2019 03:43 PM, wrote...
>
> I _think_ the load_extension() function is what you'll be looking for, though 
> I
> could be wrong.
>
> https://www.sqlite.org/lang_corefunc.html#load_extension

[clip]

> I was looking into the pragmas page, and I don't see one that would do the 
> function
> to load other libraries, such as the .load function of the CLI.  Is there one?
> Thanks.

Yep, that's it.  Thanks. (For some reason, I thought that there was a PRAGMA 
version of it) Thanks.

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

[sqlite] PRAGMA for .load

[Jose Isaias Cabrera]

Greetings.  Quick question...

I was looking into the pragmas page, and I don't see one that would do the 
function to load other libraries, such as the .load function of the CLI.  Is 
there one?  Thanks.

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Ah-hah!

Keith Medcalf, on Tuesday, November 26, 2019 03:58 PM, wrote...
>
>
> Ah, you have missing dependancies.  You need to make sure that the
> dependencies can be loaded.  When Windows attempts to load the module
> all the dependencies must be loaded as well, otherwise the loader
> cannot return a handle to the loaded module.  If no module handle is
> returned then the message "Module not found" is returned because
> sqlite3_load_extension does not actually check whether the module
> exists, but only whether or not it was loaded.


> So if the module exists and you get the message that it was not found,
> that is because it could not be loaded ...
>
> use depends.exe to see what other dll's are required and either put
> them on the path or in the same directory ...


> >
> >Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
> >>
> >>
> >> What is the canonical name of the file you are trying to load?
> >
> >c:\PMOProjects\libsqlite3decimal.dll
> >
> >As shown by the dir command,
> >
> >15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
> > Volume in drive C is Windows
> > Volume Serial Number is 40AA-E472
> >
> > Directory of c:\PMOProjects
> >
> >11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
> >   1 File(s)225,376 bytes
> >   0 Dir(s)  431,761,633,280 bytes free
> >
> >
> >> It seems that you are being told that
> >"c:\PMOProjects\libsqlite3decimal.dll" does not exist.
> >
> >But it is there,
> >
> >15:40:48.95>dir c:\PMOProjects
> > Volume in drive C is Windows
> > Volume Serial Number is 40AA-E472
> >
> > Directory of c:\PMOProjects
> >
> >11/26/2019  03:40 PM   , on
> >11/26/2019  03:40 PM   , on
> >11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
> >11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
> >11/22/2019  02:17 PM   926,748 PMOUpdater.exe
> >10/04/2019  11:30 AM   932,223 sqlite3.dll
> >   4 File(s)  5,530,647 bytes
> >   2 Dir(s)  431,760,363,520 bytes free
> >
> >15:43:18.90>
> >
> >
> >> Are you sure that the file exists and that you have permission to
> >read/execute it?
> >
> >Aaaah, permissions... H... I created this in cygwin, and copied it to
> >that directory.  Let me try something...

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Jose Isaias Cabrera, on Tuesday, November 26, 2019 03:44 PM, wrote...

> Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
> > What is the canonical name of the file you are trying to load?
>
> c:\PMOProjects\libsqlite3decimal.dll
>
> As shown by the dir command,
>
> 15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
>  Volume in drive C is Windows
>  Volume Serial Number is 40AA-E472
>
>  Directory of c:\PMOProjects
>
> 11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
>1 File(s)225,376 bytes
>0 Dir(s)  431,761,633,280 bytes free
>
>
> > It seems that you are being told that 
> > "c:\PMOProjects\libsqlite3decimal.dll" does not exist.
>
> But it is there,
>
> 15:40:48.95>dir c:\PMOProjects
>  Volume in drive C is Windows
>  Volume Serial Number is 40AA-E472
>
>  Directory of c:\PMOProjects
>
> 11/26/2019  03:40 PM   , on
> 11/26/2019  03:40 PM   , on
> 11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
> 11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
> 11/22/2019  02:17 PM   926,748 PMOUpdater.exe
> 10/04/2019  11:30 AM   932,223 sqlite3.dll
>4 File(s)  5,530,647 bytes
>2 Dir(s)  431,760,363,520 bytes free
>
> 15:43:18.90>
>
>
> > Are you sure that the file exists and that you have permission to 
> > read/execute it?
>
> Aaaah, permissions... H... I created this in cygwin, and copied it to 
> that directory.  Let me try something...

Yes, I am the owner,
15:46:48.04>dir /Q C:\PMOProjects
 Volume in drive C is Windows
 Volume Serial Number is 40AA-E472

 Directory of C:\PMOProjects

11/26/2019  03:40 PM  BUILTIN\Administrators .
11/26/2019  03:40 PM  NT SERVICE\TrustedInsta..
11/26/2019  02:19 PM   225,376 AMER\e608313   
libsqlite3decimal.dll
11/25/2019  09:21 AM 3,446,300 AMER\e608313   PMOProjs.exe
11/22/2019  02:17 PM   926,748 AMER\e608313   PMOUpdater.exe
10/04/2019  11:30 AM   932,223 AMER\e608313   sqlite3.dll
   4 File(s)  5,530,647 bytes
   2 Dir(s)  431,782,502,400 bytes free


I think there is a bug in the Windows tool.  I will keep trying until I get to 
the bottom of it.  Thanks.

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

>
>

Keith Medcalf, on Tuesday, November 26, 2019 03:38 PM, wrote...
>
>
> What is the canonical name of the file you are trying to load?

c:\PMOProjects\libsqlite3decimal.dll

As shown by the dir command,

15:40:36.57>dir c:\PMOProjects\libsqlite3decimal.dll
 Volume in drive C is Windows
 Volume Serial Number is 40AA-E472

 Directory of c:\PMOProjects

11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
   1 File(s)225,376 bytes
   0 Dir(s)  431,761,633,280 bytes free


> It seems that you are being told that "c:\PMOProjects\libsqlite3decimal.dll" 
> does not exist.

But it is there,

15:40:48.95>dir c:\PMOProjects
 Volume in drive C is Windows
 Volume Serial Number is 40AA-E472

 Directory of c:\PMOProjects

11/26/2019  03:40 PM  .
11/26/2019  03:40 PM  ..
11/26/2019  02:19 PM   225,376 libsqlite3decimal.dll
11/25/2019  09:21 AM 3,446,300 PMOProjs.exe
11/22/2019  02:17 PM   926,748 PMOUpdater.exe
10/04/2019  11:30 AM   932,223 sqlite3.dll
   4 File(s)  5,530,647 bytes
   2 Dir(s)  431,760,363,520 bytes free

15:43:18.90>


> Are you sure that the file exists and that you have permission to 
> read/execute it?

Aaaah, permissions... H... I created this in cygwin, and copied it to that 
directory.  Let me try something...

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

Re: [sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, November 26, 2019 02:57 PM, wrote...
>
>
> Escape the reverse solstice with a duplicate reverse solstice (\ -> \\) or 
> replace them
> with normal solstice (\ -> /) since Windows recognizes either as the path 
> separator.
> The CLI, like most things, parses escape sequences on input.
>
> Same applies to the C API function.  You specify the full path and name of 
> the file.
> Since the compiler probably also parses escape sequences in its input, you 
> probably
> need to escape your reverse solstice there as well.

Thanks for the support, Keith.  And yes, that is why I wrote.  I tried all of 
those.  Here are some examples:

14:22:23.19>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load
Usage: .load FILE ?ENTRYPOINT?
sqlite> .load c:/PMOProjects/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:\PMOProjects\libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .help load
.load FILE ?ENTRY?   Load an extension library
sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:/PMOProjects/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c://PMOProjects//libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load /PMOProjects/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:\/PMOProjects\/libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load c:\\PMOProjects\\libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite>
sqlite> .cd c:\PMOProjects
Cannot change to directory "c:PMOProjects"
sqlite> .cd c:\\PMOProjects
sqlite> .load ./libsqlite3decimal sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .load ./libsqlite3decimal.dll sqlite3_decimal_init
Error: The specified module could not be found.

sqlite> .cd
Usage: .cd DIRECTORY
sqlite> .cd c:\\PMOProjects
sqlite> .cd c:\PMOProjects
Cannot change to directory "c:PMOProjects"
sqlite> .q


15:08:12.49>sqlite3 --cmd '.load c:\\PMOProjects\\libsqlite3decimal 
sqlite3_decimal_init'
Error: unrecognized token: "'.load"
Error: near "sqlite3_decimal_init": syntax error

15:09:37.68>sqlite3 --cmd ".load c:\\PMOProjects\\libsqlite3decimal 
sqlite3_decimal_init"
Error: The specified module could not be found.

SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
sqlite>



> >I am trying to load a DLL to the DOS tool, but it's failing.  I am
> >trying,
> >
> >sqlite> .load c:\PMOProjects\sqlite3libIN sqlite3_decimal_init
> >Error: The specified module could not be found.
> >
> >Any help would be greatly appreciated.  Also, if I would like to use the
> >call,
> >
> >int sqlite3_load_extension(
> >  sqlite3 *db,  /* Load the extension into this database
> >connection */
> >  const char *zFile,/* Name of the shared library containing
> >extension */
> >  const char *zProc,/* Entry point.  Derived from zFile if 0 */
> >  char **pzErrMsg   /* Put error message here if not 0 */
> >);
> >
> >What would be the path to use in Windows?  Thanks so much.

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

[sqlite] Passing a path to sqlite3.exe to load a dll

[Jose Isaias Cabrera]

Greetings!

I am trying to load a DLL to the DOS tool, but it's failing.  I am trying,

sqlite> .load c:\PMOProjects\sqlite3libIN sqlite3_decimal_init
Error: The specified module could not be found.

Any help would be greatly appreciated.  Also, if I would like to use the call,

int sqlite3_load_extension(
  sqlite3 *db,  /* Load the extension into this database connection */
  const char *zFile,/* Name of the shared library containing extension */
  const char *zProc,/* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg   /* Put error message here if not 0 */
);

What would be the path to use in Windows?  Thanks so much.

josé

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

Re: [sqlite] wrong timestamp using strftime('%s')

[Jose Isaias Cabrera]

Dominik Ohnezeit, on Sunday, November 24, 2019 03:21 PM, wrote...

> strftime('%s', '1970-01-01 00:00:00.000')

[clip]

> the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
> 22:29:11.000
>
> Does anyone know why?

Here are some samples run...

sqlite> select strftime('%s', '1970-01-01 00:00:00.000');
0

sqlite> select strftime('%s', '1969-12-31 23:59:59');
-1

sqlite> select datetime(0, 'unixepoch');
1970-01-01 00:00:00

sqlite> select datetime(-1, 'unixepoch');
1969-12-31 23:59:59

You are probably subtracting 1 from 0 which is sending a -1 to datetime.  Just 
a thought...  Thanks.

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

Re: [sqlite] Bug report

[Jose Isaias Cabrera]

Kees Nuyt, on Thursday, November 21, 2019 03:48 PM, wrote...
>
>
> Thanks, Jose.
>
> I see no CVE entered by the OP, but maybe I missed something.

Yes, you are right.  After pasting it, I went through the top 5 and none of 
these aren't/weren't the one. Apologies.  I thought that by searching on sqlite 
the top 5 or so would be the one that was just opened, but for some reason, it 
was not.  Sorry about that.  Fast fingers Jose.

josé

> A quick look to your list :
>
> > NameDescription
> > CVE-2019-9937, on
> > In SQLite 3.27.2, interleaving reads and writes in a single transaction with
> > an fts5 virtual table will lead to a NULL Pointer Dereference in
> > fts5ChunkIterate in sqlite3.c. This is related to ext/fts5/fts5_hash.c and
> > ext/fts5/fts5_index.c.
>
> Resolved 2019-03-18
>
>
> > CVE-2019-9936, on
> > In SQLite 3.27.2, running fts5 prefix queries inside a transaction could
> > trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, 
> > which
> > may lead to an information leak. This is related to ext/fts5/fts5_hash.c.
>
> Resolved 2019-03-18
>
>
> > CVE-2019-5827, on
> > Integer overflow in SQLite via WebSQL in Google Chrome prior to 
> > 74.0.3729.131
> > allowed a remote attacker to potentially exploit heap corruption via a 
> > crafted
> > HTML page.
>
> Resolved 2019-04-13
>
>
> > CVE-2019-3784, on
> > Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session
> > that can be spoofed. When deployed on cloud foundry with multiple instances
> > using the default embedded SQLite database, a remote authenticated malicious
> > user can switch sessions to another user with the same session id.
>
> Application error
>
>
> > CVE-2019-1616 
> > 8
> > In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a
> > browser or other application because of missing validation of a sqlite_stat1
> > sz field, aka a "severe division by zero in the query planner."
>
> Resolved 2019-08-15
>
>
> > CVE-2019-1075 
> > 2
> > Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to
> > SQL Injection due to sequelize.json() helper function not escaping values
> > properly when formatting sub paths for JSON queries for MySQL, MariaDB and
> > SQLite.
>
> Application error
>
>
> > CVE-2018-8740, on
> > In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE
> > TABLE AS statement could cause a NULL pointer dereference, related to 
> > build.c
> > and prepare.c.
>
> Resolved 2018-03-16
>
>
> > CVE-2018-7774, on
> > The vulnerability exists within processing of localize.php in Schneider
> > Electric U.motion Builder software versions prior to v1.3.4. The underlying
> > SQLite database query is subject to SQL injection on the username input
> > parameter.
>
> Application error
>
>
> --
> Regards,
> Kees Nuyt
> ___
> 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] Bug report

[Jose Isaias Cabrera]

NameDescription
CVE-2019-9937  
In SQLite 3.27.2, interleaving reads and writes in a single transaction with an 
fts5 virtual table will lead to a NULL Pointer Dereference in fts5ChunkIterate 
in sqlite3.c. This is related to ext/fts5/fts5_hash.c and ext/fts5/fts5_index.c.
CVE-2019-9936  
In SQLite 3.27.2, running fts5 prefix queries inside a transaction could 
trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, which 
may lead to an information leak. This is related to ext/fts5/fts5_hash.c.
CVE-2019-5827  
Integer overflow in SQLite via WebSQL in Google Chrome prior to 74.0.3729.131 
allowed a remote attacker to potentially exploit heap corruption via a crafted 
HTML page.
CVE-2019-3784  
Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session 
that can be spoofed. When deployed on cloud foundry with multiple instances 
using the default embedded SQLite database, a remote authenticated malicious 
user can switch sessions to another user with the same session id.
CVE-2019-16168
In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a 
browser or other application because of missing validation of a sqlite_stat1 sz 
field, aka a "severe division by zero in the query planner."
CVE-2019-10752
Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to 
SQL Injection due to sequelize.json() helper function not escaping values 
properly when formatting sub paths for JSON queries for MySQL, MariaDB and 
SQLite.
CVE-2018-8740  
In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE 
TABLE AS statement could cause a NULL pointer dereference, related to build.c 
and prepare.c.
CVE-2018-7774  
The vulnerability exists within processing of localize.php in Schneider 
Electric U.motion Builder software versions prior to v1.3.4. The underlying 
SQLite database query is subject to SQL injection on the username input 
parameter.



From: sqlite-users  on behalf of 
Kees Nuyt 
Sent: Thursday, November 21, 2019 09:51 AM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Bug report

On Tue, 19 Nov 2019 00:19:13 -0500, you wrote:

> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU.
> We found 7 crashes for sqlite of  the newest commit
> 3842e8f166e23a1ed6e6094105e7a23502d414da.
> We have attached the samples that crash sqlite in the email.

The mailing list strips attachemnts. Please insert them in the body text of your
message, or mail them to Richard Hipp.

> FYI, we have also reported the bugs for CVE
> at cve.mitre.org .

Can you tell us the CVE nunber?


--
Regards,

Kees Nuyt

___
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] What is the C language standard to which sqlite conforms ?

[Jose Isaias Cabrera]

Jens Alfke, on Tuesday, November 19, 2019 03:11 PM, wrote...
>
>
>
> > On Nov 19, 2019, at 5:29 AM, Dennis Clarke, on
> >
> > Yes I have tried gcc 9.2.0 and the whole process fails in the tests
> > and no it will not compile as C90 code.
>
> Have you tried just not forcing strict compliance? Which is the way people 
> normally build SQLite?

Using cygwin, I run,

$ i686-w64-mingw32-gcc -shared -static-libgcc sqlite3.c -o sqlite3.dll​

and that works for me.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Tuesday, November 19, 2019 10:47 AM, wrote...
>
> Jose, at least two things bothers me about part of your query:
> IfNull('p006', Max(idate))
> The first is that 'p006' is never null so the second part of the ifnull() 
> will never be used.

True.

> The second thing is that the result of this ifnull() is to set the value of 
> the "a" field.
> However, the domain of "a" is 'p001', 'p002',... It is not a date which would 
> be returned by
> Max(idate).

> I know you are trying to use side effects, but I don't understand ???

Hi Doug.  This is more or less "a hack" to make the INSERT work when the id 
does not exists in the table. Please take a look a both Keith's email regarding 
this subject, and he has done a wonderful job explaining what is happening.  I 
would probably damage something trying to explain it. ;-) What I can tell you 
is that I need this INSERT to always INSERT something.  Either a new record 
based on an already existing id ('p001') in the table, or a new record based on 
a non-existing id ('p006') in the table.  The IfNull works beautifully to allow 
for this.  Why it works with Max(idate) on the first select, I don't know, but, 
if I take it out, it does not. Thanks.


> Doug
> > -----Original Message-
> > From: sqlite-users, on

Jose Isaias Cabrera
> > Sent: Monday, November 18, 2019 12:11 PM
>
> > Subject: Re: [sqlite] Question about: Adding a record to a table
> > with select failure
> >
> >
> > Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> > Jose Isaias Cabrera
> > [clip]
> > > > >
> > > > > INSERT INTO t (a, b, c, d, e, idate)​
> > > > > SELECT IfNull('p006', Max(idate)),​
> > > > >IfNull(b, 1),​
> > > > >IfNull(c, 2),​
> > > > >'y',​
> > > > >IfNull(e, 4),​
> > > > >'2019-20-12'​
> > > > >   FROM t​
> > > > >  WHERE a = 'p006';​
> > >
> > > I think that you will never insert the first record with a query
> > like this, since
> > > the select returns 0 records of there are none in the database
> > yet.
> >
> > Well, it does...
> > sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> > idate));
> > sqlite> INSERT INTO t
> >...> SELECT IfNull('p001', Max(idate)),
> >...>IfNull(b, 1),
> >...>IfNull(c, 2),
> >...>IfNull(d,'n'),
> >...>IfNull(e, 4),
> >...>'2019-20-11'
> >...>  FROM t
> >...>  WHERE a = 'p001';
> > sqlite> select * from t;
> > p001|1|2|n|4|2019-20-11
> > sqlite>
> >
> > And, since I put an uniqueness on a and idate, now these can not
> > be repeated, so if I run the same command again,
> > sqlite> INSERT INTO t
> >...> SELECT IfNull('p001', Max(idate)),
> >...>IfNull(b, 1),
> >...>IfNull(c, 2),
> >...>IfNull(d,'n'),
> >...>IfNull(e, 4),
> >...>'2019-02-11'
> >...>  FROM t
> >...>  WHERE a = 'p001';
> > Error: UNIQUE constraint failed: t.a, t.idate
> > sqlite>
> >
> > I do not get a repeated record for 'p001' and 2019-02-11; But if
> > they are different,
> > sqlite> INSERT INTO t
> >...> SELECT IfNull('p002', Max(idate)),
> >...>IfNull(b, 1),
> >...>IfNull(c, 2),
> >...>IfNull(d,'n'),
> >...>IfNull(e, 4),
> >...>'2019-02-11'
> >...>  FROM t
> >...>  WHERE a = 'p002';
> > sqlite> select * from t;
> > p001|1|2|n|4|2019-02-11
> > p002|1|2|n|4|2019-02-11
> >
> > It'll work. Thanks.
> >
> > josé

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Peter da Silva, on Monday, November 18, 2019 08:07 PM, wrote...
>
> Assuming I'm understanding what the original message was about.
>
> Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for?

The original message was about adding a new record using old values from an 
existing id, let's say 'p001'.  Then, I found out that I needed to add that 
record if there was no 'p001' id.  And yes, we were using INSERT to do this. 
UPDATE wouldn't work because it can not add a new record.  So...

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Thanks, Keith.

Keith Medcalf, on Monday, November 18, 2019 07:25 PM, wrote...
>
>
> On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera, on
>
> >Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
> >>
> >> This relies on two implementation details particular to SQLite3 which
> >> hold at present, but may of course change at any time:
> >> (1) that selecting a non-aggregate scalar column will return a value
> >> from (one of) the row(s) matching the value of the aggregate (most
> >> RDBMS used to do this, most now throw an error at this construct); and,
> >> (2) that the optimizer will not optimize "IfNull('p006', max(idate))"
> >> into 'p006' since the result must always be 'p006' which would of
> >> course render the select to be a simple select and not an aggregate
> >> causing "all hell to break loose".
>
> >Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't
> >know much to argue, but is working. If I take out the first IfNull, and
> >there is not, at least one instance of 'p006' in the table, the INSERT
> >never works. I was thinking of using COALESCE, but that would also mean
> >that one or the other would have to be not null. Any suggestion would be
> >appreciated.
>
> No, what I am saying is that this particular insert is working with the
> current version of SQLite3 and is unlikely to work with any other RDBMS
> that uses SQL as this is using an "implementation detail" specific to
> SQLite3 that does not exist elsewhere; and, secondly that the particular
> construction used putting the aggregate function in the second argument
> of a coalesce where the first argument is a constant is dependent on the
> query optimizer not "optimizing way" the entire expression based on the
> fact that the first argument is a not-null constant (though if this
> optimized away the aggregate-ness of the query this would be a bug in the
> optimizer, since the optimization process should not cause different
> results to occur).
>
> What I am saying is that you need to be aware of this and if you change
> versions of SQLite3 then you need to make sure that this query still
> operates as you intend it to operate.
>
> So far as I know the first constraint (the scalar use of columns that are
> not part of the group by clause in a query) is unlikely to change because
> this is a significant change to backwards compatibility and Richard is
> unlikely to make such a change without warnings in second coming type.
>
> The second is unlikely with SQLite3 because the optimizer is not based on
> a query re-write which would be more prone to this sort of issue.
>
> The second issue can be bypassed entirely by using bound parameters (since
> there is no way except by examining the bound parameter at execution time
> to know that a bound parameter is not NULL) thus precluding the
> possibility of optimizing away the aggregate function.  As in:
>
> select ifnull(:a, max(idate)),
>ifnull(b, 1),
>ifnull(c, 2),
>:y,
>ifnull(e, 4),
>:idate
>   from t
>  where a == :a
>
> and binding values for :a, :y and :idate.  There is no way to know at prepare
> time that parameter :a cannot be null so therefore the ifnull(:a... and the
> evaluation of the aggregate cannot be optimized away.
>
> >[clip]
> >
> >> >SELECT IfNull('p006', Max(idate)),
> >> >   IfNull(b, 1),
> >> >   IfNull(c, 2),
> >> >   'y',
> >> >   IfNull(e, 4),
> >> >   '2019-20-12'
> >> >  FROM t
> >> >  WHERE a = 'p006';
> >> >

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Simon Slavin, on Monday, November 18, 2019 05:14 PM, wrote...

> Being completely serious, whenever I see "undocumented" or "implementation 
> dependent" or
> "optimization side-effect", or a SQL statement I can't parse in my head, I 
> usually decide
> to do it in my programming language instead.  This simplifies testing and 
> debugging, and
> makes things easier for the poor engineer who has to understand my code.

Thanks for this.  Yes, I have lots of those.  Some of these, I can probably ask 
the GURUs in this list, and they would come up with some beautiful, SQL, but 
then, I wouldn't understand how to support it.  So, I do follow the statement 
above, as much as possible.

> You can do clever things in a language like SQL which allows recursive 
> construction
> clauses.  But what strikes me as ingenious when I'm writing it can look 
> bizarre and
> impenetrable to me, or someone else, a year later.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>
> This relies on two implementation details particular to SQLite3 which hold> 
> at present,
> but may of course change at any time:
> (1) that selecting a non-aggregate scalar column will return a value from 
> (one of) the
> row(s) matching the value of the aggregate (most RDBMS used to do this, most 
> now throw
> an error at this construct); and,
> (2) that the optimizer will not optimize "IfNull('p006', max(idate))" into 
> 'p006' since
> the result must always be 'p006' which would of course render the select to 
> be a simple
> select and not an aggregate causing "all hell to break loose".

Thanks Keith.  So, you are saying that this is a bad INSERT, and I don't know 
much to argue, but is working. If I take out the first IfNull, and there is 
not, at least one instance of 'p006' in the table, the INSERT never works. I 
was thinking of using COALESCE, but that would also mean that one or the other 
would have to be not null. Any suggestion would be appreciated.

[clip]

> >SELECT IfNull('p006', Max(idate)),
> >   IfNull(b, 1),
> >   IfNull(c, 2),
> >   'y',
> >   IfNull(e, 4),
> >   '2019-20-12'
> >  FROM t
> >  WHERE a = 'p006';
> >
> >versus this:
> >SELECT (a,b,c,d,e,idate) from t where a = "p006"
> >
> >Doesn't the where clause that cannot be satisfied in both cases guarantee
> >that no rows will be selected, when there are no records in the database?
> >Doug
> >
> >> -Original Message-
> >> From: sqlite-users, on

Jose Isaias Cabrera
> >> Sent: Monday, November 18, 2019 12:11 PM
> >
> >> Subject: Re: [sqlite] Question about: Adding a record to a table
> >> with select failure
> >>
> >>
> >> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> >> Jose Isaias Cabrera
> >> [clip]
> >> > > >
> >> > > > INSERT INTO t (a, b, c, d, e, idate)​
> >> > > > SELECT IfNull('p006', Max(idate)),​
> >> > > >IfNull(b, 1),​
> >> > > >IfNull(c, 2),​
> >> > > >'y',​
> >> > > >IfNull(e, 4),​
> >> > > >'2019-20-12'​
> >> > > >   FROM t​
> >> > > >  WHERE a = 'p006';​
> >> >
> >> > I think that you will never insert the first record with a query
> >> like this, since
> >> > the select returns 0 records of there are none in the database
> >> yet.
> >>
> >> Well, it does...
> >> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> >> idate));
> >> sqlite> INSERT INTO t
> >>...> SELECT IfNull('p001', Max(idate)),
> >>...>IfNull(b, 1),
> >>...>IfNull(c, 2),
> >>...>IfNull(d,'n'),
> >>...>IfNull(e, 4),
> >>...>'2019-20-11'
> >>...>  FROM t
> >>...>  WHERE a = 'p001';
> >> sqlite> select * from t;
> >> p001|1|2|n|4|2019-20-11
> >> sqlite>
> >>
> >> And, since I put an uniqueness on a and idate, now these can not
> >> be repeated, so if I run the same command again,
> >> sqlite> INSERT INTO t
> >>...> SELECT IfNull('p001', Max(idate)),
> >>...>IfNull(b, 1),
> >>...>IfNull(c, 2),
> >>...>IfNull(d,'n'),
> >>...>IfNull(e, 4),
> >>...>'2019-02-11'
> >>...>  FROM t
> >>...>  WHERE a = 'p001';
> >> Error: UNIQUE constraint failed: t.a, t.idate
> >> sqlite>
> >>
> >> I do not get a repeated record for 'p001' and 2019-02-11; But if
> >> they are different,
> >> sqlite> INSERT INTO t
> >>...> SELECT IfNull('p002', Max(idate)),
> >>...>IfNull(b, 1),
> >>...>IfNull(c, 2),
> >>...>IfNull(d,'n'),
> >>...>IfNull(e, 4),
> >>...>'2019-02-11'
> >>...>  FROM t
> >>...>  WHERE a = 'p002';
> >> sqlite> select * from t;
> >> p001|1|2|n|4|2019-02-11
> >> p002|1|2|n|4|2019-02-11
> >>
> >> It'll work. Thanks.
> >>
> >> josé

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Monday, November 18, 2019 02:48 PM, wrote...
>
> I'm really confused now. I don't understand the semantics of:
> SELECT IfNull('p006', Max(idate)),
>IfNull(b, 1),
>IfNull(c, 2),
>'y',
>IfNull(e, 4),
>'2019-20-12'
>   FROM t
>   WHERE a = 'p006';
>
> versus this:
> SELECT (a,b,c,d,e,idate) from t where a = "p006"
>
> Doesn't the where clause that cannot be satisfied in both cases guarantee 
> that no rows will
> be selected, when there are no records in the database?

Imagine this select:

SELECT 'p006',1, 2, 'y', 4, '2019-02-11';

The IfNull is bringing these values back if the 'p006' does not exists.  Try 
the above select in SQLite3 tool.  You will get a record.  Even without table.  
Since the original question was how can I INSERT... this will check the table 
for 'p006', if it exists, it will bring some of the data from that existing 
record and insert a new one. Ihth.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Monday, November 18, 2019 12:31 PM, wrote...
Jose Isaias Cabrera
[clip]
> > >
> > > INSERT INTO t (a, b, c, d, e, idate)​
> > > SELECT IfNull('p006', Max(idate)),​
> > >IfNull(b, 1),​
> > >IfNull(c, 2),​
> > >'y',​
> > >IfNull(e, 4),​
> > >'2019-20-12'​
> > >   FROM t​
> > >  WHERE a = 'p006';​
>
> I think that you will never insert the first record with a query like this, 
> since
> the select returns 0 records of there are none in the database yet.

Well, it does...
sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a, idate));
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-20-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
sqlite> select * from t;
p001|1|2|n|4|2019-20-11
sqlite>

And, since I put an uniqueness on a and idate, now these can not be repeated, 
so if I run the same command again,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p001', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p001';
Error: UNIQUE constraint failed: t.a, t.idate
sqlite>

I do not get a repeated record for 'p001' and 2019-02-11; But if they are 
different,
sqlite> INSERT INTO t
   ...> SELECT IfNull('p002', Max(idate)),
   ...>IfNull(b, 1),
   ...>IfNull(c, 2),
   ...>IfNull(d,'n'),
   ...>IfNull(e, 4),
   ...>'2019-02-11'
   ...>  FROM t
   ...>  WHERE a = 'p002';
sqlite> select * from t;
p001|1|2|n|4|2019-02-11
p002|1|2|n|4|2019-02-11

It'll work. Thanks.

josé


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

Re: [sqlite] Relax "DISTINCT aggregates" error

[Jose Isaias Cabrera]

Dominique Devienne, on Monday, November 18, 2019 04:33 AM, wrote...
>
> On Fri, Nov 15, 2019 at 4:22 PM Jose Isaias Cabrera, on
> wrote:
>
> > Dominique Devienne, on Friday, November 15, 2019 09:02 AM, wrote...
> >
> > Have you tried this,
> > sqlite> select group_concat(distinct id || ', ') from t;
> > 1, ,2, ,4, ,7,
> >
> > The only problem is that when the list has a non-distinct.  H.
> >
>
> That's obviously not the same result (trailing text), and a bit hackish.
> Clever, but a no-go IMHO. I'm hoping for a "real" fix taking into account
> the constant-ness of trailing arguments, having only the first arg being
> row-dependent. --DD

Yes, understood.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...​
>  ​
> One approach might be something like this:​
> ​
> INSERT INTO t (a, b, c, d, e, idate)​
> SELECT 'p006',​
>Coalesce(b, 1),​
>Coalesce(c, 2),​
>'y',​
>Coalesce(e, 4),​
>'2019-20-12'​
>   FROM (SELECT 1)​
>   LEFT JOIN​
>(SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC LIMIT 
> 1);​
> ​
> A slightly more succinct (but not universal) way:​
> Note: see point 1 of​
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause​
> ​
> INSERT INTO t (a, b, c, d, e, idate)​
> SELECT IfNull('p006', Max(idate)),​
>IfNull(b, 1),​
>IfNull(c, 2),​
>'y',​
>IfNull(e, 4),​
>'2019-20-12'​
>   FROM t​
>  WHERE a = 'p006';​
​
Thanks, Jake.  I like this last one.  I appreciate it.  Thanks.​
​
josé​
​
> On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera, on ​
> >​
> >​
> > Doug, on Friday, November 15, 2019 11:42 AM, wrote...​
> > >​
> > > WRT Jose's original context, and just for my enlightment, what happens 
> > > with the following:​
> > >​
> > > insert into t (a, b, c, d, e, idate)​
> > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';​
> > >​
> > > where p999 does not define a record? Is a new record inserted with values 
> > > of a,b,c, and e null?​
> >​
> > Ok, I promise that this will be the last email on this for me:​
> >​
> > I just came to my senses, and sometimes, I need to insert when the 'a' 
> > value does not exists, as Doug just brought to my attention.  So, I am 
> > trying to insert a record with two new values using the last existing 'a'.  
> > If a does not exists, then I need to add that record with the two values.  
> > I have been trying a few INSERT with CASEs, but nothing is working.  I know 
> > one of you will make it look easy, but this is what I have done as of now:​
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);​
> > insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
> > '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
> > '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
> > '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
> > '2019-02-11');​
> > insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
> > '2019-02-11');​
> > select * from t;​
> > 1|p001|1|2|n|4|2019-02-11​
> > 2|p002|2|2|n|4|2019-02-11​
> > 3|p003|3|2|n|4|2019-02-11​
> > 4|p004|4|2|y|4|2019-02-11​
> > 5|p005|5|2|y|4|2019-02-11​
> >​
> > I have tried various combination of the following,​
> >​
> > insert into t (a, b, c, d, e, idate) VALUES​
> > (​
> > CASE​
> >SELECT a from t WHERE a = 'p006' idate desc limit 1​
> > WHEN a = NULL​
> > THEN 'p006',1,2,'y',4,'2019-02-12'​
> > ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate 
> > desc limit 1​
> > END​
> > );​
> > Error: near "SELECT": syntax error​
> > sqlite>​
> >​
> > But, different syntax error have popped.  Any help would be greatly 
> > appreciated.  Thanks.​
> >​
> > josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite import Latin1 data?

[Jose Isaias Cabrera]

Shawn Wagner, on Friday, November 15, 2019 04:01 PM, wrote...
>
> If you're on Windows, which cp1252 suggests, just make sure that you don't
> end up with a BOM at the start of the file when you convert it. Windows
> tools that output utf-8 are sometimes prone to add one even though it's
> pointless to have.

Why do you think it's pointless?

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

Re: [sqlite] Adding a record to a table with one value change

[Jose Isaias Cabrera]

Keith Medcalf, on Friday, November 15, 2019 03:50 PM, wrote...
>
>
> How you would use bound parameters depends on what you are using to interface 
> with the sqlite3 database.
>
> https://www.sqlite.org/c3ref/bind_blob.html for the C interfaces.
>
> In something like python you would pass the bindings as a tuple to the 
> execute method of the cursor:
>
> cr.execute(sql, ('p0001', 5, '2014-02-23'))
>
> Basically it allows you to substitute values provided by your program into 
> the sql statement rather than
> compose the sql statement dynamically possibly leading to injection problems.

Thanks. I am going to read about.  The D libraries that I am using has it.  I 
just never knew about it.  Thanks.


> https://xkcd.com/327/

Pretty funny stuff... thanks.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Friday, November 15, 2019 11:42 AM, wrote...
>
> WRT Jose's original context, and just for my enlightment, what happens with 
> the following:
>
> insert into t (a, b, c, d, e, idate)
> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
>
> where p999 does not define a record? Is a new record inserted with values of 
> a,b,c, and e null?

Ok, I promise that this will be the last email on this for me:

I just came to my senses, and sometimes, I need to insert when the 'a' value 
does not exists, as Doug just brought to my attention.  So, I am trying to 
insert a record with two new values using the last existing 'a'.  If a does not 
exists, then I need to add that record with the two values.  I have been trying 
a few INSERT with CASEs, but nothing is working.  I know one of you will make 
it look easy, but this is what I have done as of now:
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');
select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11

I have tried various combination of the following,

insert into t (a, b, c, d, e, idate) VALUES
(
CASE
   SELECT a from t WHERE a = 'p006' idate desc limit 1
WHEN a = NULL
THEN 'p006',1,2,'y',4,'2019-02-12'
ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate 
desc limit 1
END
);
Error: near "SELECT": syntax error
sqlite>

But, different syntax error have popped.  Any help would be greatly 
appreciated.  Thanks.

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

Re: [sqlite] Can SQLite import Latin1 data?

[Jose Isaias Cabrera]

Winfried, on Friday, November 15, 2019 03:13 PM, wrote...
>
> Hello,
>
> I have a big CSV file that's encoded in Latin1 (cp1252), while SQLite stores
> strings as Unicode.
>
> Neither Google nor ".help" helped to find if SQLite offers a switch to
> convert Latin1 to UTF-8 on the fly before running the ".import" command.
>
> Should I first convert the file into UTF-8 before importing it into SQLite?

I would just take that file and using your favorite Text Editor (EmEditor is a 
good one), and save it as UFT-8.  SQLite will handle it beautifully. Thanks.

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Simon Slavin, on Friday, November 15, 2019 11:58 AM, wrote...
>
> On 15 Nov 2019, at 4:48pm, Jose Isaias Cabrera, on
>
> > It does not get inserted.
>
> The SELECT returns zero lines.  Therefore zero lines get inserted.  You might 
> like to try
> one where the SELECT returns more than one line.

Yes, I expected this.  I was just trying it as a show-result type of deal. :-)

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

Re: [sqlite] Question about: Adding a record to a table with select failure

[Jose Isaias Cabrera]

Doug, on Friday, November 15, 2019 11:42 AM, wrote...
>
> WRT Jose's original context, and just for my enlightment, what happens with 
> the following:
>
> insert into t (a, b, c, d, e, idate)
> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
>
> where p999 does not define a record? Is a new record inserted with values of 
> a,b,c, and e null?

It does not get inserted.

sqlite> drop table t;
sqlite> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
sqlite> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
'2019-02-11');
sqlite> select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11
sqlite> insert into t (a, b, c, d, e, idate)
   ...> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
sqlite> select * from t;
1|p001|1|2|n|4|2019-02-11
2|p002|2|2|n|4|2019-02-11
3|p003|3|2|n|4|2019-02-11
4|p004|4|2|y|4|2019-02-11
5|p005|5|2|y|4|2019-02-11
sqlite>

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