Re: [sqlite] CVE-2019-19317

[Simon Slavin]

On 14 Dec 2019, at 11:16pm, Yongheng Chen  wrote:

> When we report the bugs, we said that they were from 3.31 version, but people 
> in mitre changed them to 3.30.1. We just reported what we found. And the 
> commit we reported in the bug report is referencing to the official GitHub 
> repo. 
> 
> Bugs are found in the latest version

I need to clarify this for you, because the difference is important.

The version of SQLite you tested was never released.  It was not version 
3.30.1.  It was not version 3.31.  It was no version at all.  The version you 
downloaded was a work in progress.  The programmers were still working on it.

There is no point in reporting bugs in such a version to CVE.  Becausee CVE 
requires a version number to report bugs and that version of SQLite does not 
have a version number.

However, your work is important.  If you would like the current version of 
SQLite to test, please download it from this page.  Do not depend on a version 
in GITHUB.  This may not be an official copy.



You will find two copies of SQLite there:

At the top you will find "Pre-release Snapshots".  These are not released.  
They do not have a version number.  Nobody should be using it.  It is there so 
that people can look at new features and recent bug-fixes.  If you find a bug 
in this version, please report it by posting here, including the name of the 
download (e.g. "sqlite-snapshot-201911192122.tar.gz").  The developers will 
thank you for your work and correct the bug before that version is released.

Lower down you will find "Source Code".  This version has been released.  It 
has a version number.  People might be using it.  If you find a bug in this 
version please report it here as above.  But you could also report it elsewhere 
if you want, since you will be able to report which version the bug affects.

Thank you for your help.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CVE-2019-19317

[Yongheng Chen]

When we report the bugs, we said that they were from 3.31 version, but people 
in mitre changed them to 3.30.1. We just reported what we found. And the commit 
we reported in the bug report is referencing to the official GitHub repo. 

Bugs are found in the latest version, because there are so many bugs in the 
release version that are already been fixed in the development code. So there’s 
no point finding bugs in release version, as we have to verify whether the 
latest code still has such bug anyway. Some bugs we found can reproduced in the 
release version, with slight change in the test case, but when we asked the 
developer to confirm them again. We didn’t get reply as they had been fixed in 
the developing version after we reported them. 


> On Dec 14, 2019, at 5:41 PM, Richard Hipp  wrote:
> 
> On 12/14/19, Raitses, Alex  wrote:
>> Hello,
>> CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was
>> submitted on SQLite.
>> As far as I can see the patch is already submitted. Can you confirm please?
>> Do you have estimation for the fixed version release?
> 
> 
> This CVE appears to reference a bug in an unreleased development
> version of SQLite only.  The bug has never appeared in any official
> release version of SQLite, as far as I can tell.  So there is nothing
> to fix.
> 
> The CVE is from a third-party, not one of the SQLite developers.
> There was no coordination between the CVE authors and the SQLite
> developers.
> 
> SQLite is open-source.  Anybody can download our latest development
> code and run fuzzers or other tests against it.  Sometimes those
> people find issues in unreleased code and write CVEs against them,
> even though the problem has never appeared in any release.
> 
> One clue that this is a third-party CVE that does not have the
> endorsement of the SQLite developers is that it references a GitHub
> mirror of the source-code repository, rather than the official Fossil
> source-code repository.  The developers would never do that.
> 
> -- 
> 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] CVE-2019-19317

[Richard Hipp]

On 12/14/19, Raitses, Alex  wrote:
> Hello,
> CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was
> submitted on SQLite.
> As far as I can see the patch is already submitted. Can you confirm please?
> Do you have estimation for the fixed version release?


This CVE appears to reference a bug in an unreleased development
version of SQLite only.  The bug has never appeared in any official
release version of SQLite, as far as I can tell.  So there is nothing
to fix.

The CVE is from a third-party, not one of the SQLite developers.
There was no coordination between the CVE authors and the SQLite
developers.

SQLite is open-source.  Anybody can download our latest development
code and run fuzzers or other tests against it.  Sometimes those
people find issues in unreleased code and write CVEs against them,
even though the problem has never appeared in any release.

One clue that this is a third-party CVE that does not have the
endorsement of the SQLite developers is that it references a GitHub
mirror of the source-code repository, rather than the official Fossil
source-code repository.  The developers would never do that.

-- 
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

Re: [sqlite] CVE-2019-19317

[Shawn Wagner]

Addendum: I suspect it's the one mentioned as being filed in this earlier
thread:
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg117794.html


On Sat, Dec 14, 2019, 2:12 PM Shawn Wagner  wrote:

> This appears to be a CVE pertaining to a feature that hasn't even been
> released yet (generated columns will be in the upcoming 3.31; they're
> certainly not in the referenced 3.30.1). Unless you're using the
> development snapshot from the download page or following trunk on fossil
> and haven't updated in a while, it sounds like a complete non-issue.
>
> On Sat, Dec 14, 2019, 1:36 PM Raitses, Alex 
> wrote:
>
>> Hello,
>> CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was
>> submitted on SQLite.
>> As far as I can see the patch is already submitted. Can you confirm
>> please?
>> Do you have estimation for the fixed version release?
>>
>>
>> Thanks in advance,
>> Regards,
>> Alex
>>
>> -
>> Intel Israel (74) Limited
>>
>> This e-mail and any attachments may contain confidential material for
>> the sole use of the intended recipient(s). Any review or distribution
>> by others is strictly prohibited. If you are not the intended
>> recipient, please contact the sender and delete all copies.
>> ___
>> 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] CVE-2019-19317

[Shawn Wagner]

This appears to be a CVE pertaining to a feature that hasn't even been
released yet (generated columns will be in the upcoming 3.31; they're
certainly not in the referenced 3.30.1). Unless you're using the
development snapshot from the download page or following trunk on fossil
and haven't updated in a while, it sounds like a complete non-issue.

On Sat, Dec 14, 2019, 1:36 PM Raitses, Alex  wrote:

> Hello,
> CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was
> submitted on SQLite.
> As far as I can see the patch is already submitted. Can you confirm please?
> Do you have estimation for the fixed version release?
>
>
> Thanks in advance,
> Regards,
> Alex
>
> -
> Intel Israel (74) Limited
>
> This e-mail and any attachments may contain confidential material for
> the sole use of the intended recipient(s). Any review or distribution
> by others is strictly prohibited. If you are not the intended
> recipient, please contact the sender and delete all copies.
> ___
> 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] CVE-2019-19317

[Raitses, Alex]

Hello,
CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was submitted 
on SQLite.
As far as I can see the patch is already submitted. Can you confirm please?
Do you have estimation for the fixed version release?


Thanks in advance,
Regards,
Alex

-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
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

[Simon Slavin]

On 14 Dec 2019, at 8:49pm, Donald Shepherd  wrote:

> Gut feel is that the divide will fall such that embedded use cases want the 
> memory and PC or equivalent use cases want the CPU.

I read here around 18 months ago that more than half of SQLite installations 
were on mobile phones (presumably including tablets which run iOS or Android).  
So a consideration would be which option was of best advantage on a mobile 
phone.

Unfortunately I don't know what the performance stats or speeds of mobile 
phones are, or which option would give the best payoff.  The only thing I've 
seen recently was that iPhone stats and capabilities are roughly equivalent to 
those of high-end Android phones, so an argument which applies to one should 
apply to the other.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine the column type? – virtual table?

[Richard Damon]

On 12/14/19 1:55 PM, František Kučera wrote:
> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
>> What he wants is different. He takes a basically arbitrary database
>> (user provided) and an arbitrary SQL statement (also user provided) and
>> he wants to determine what type a given column will present.
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the 
> user wants integer in e.g. "size" column, he must say it explicitly by: 
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to SQLite. 
> It could provide a virtual table that will parse the query and return 
> expected result set metadata. So before executing the query, I could do 
> SELECT order, column_name, column_type FROM result_set_metadata WHERE sql = 
> 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be used 
> for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared 
> types
>
>  - analyze the operations done in the SELECT clause (operators, function 
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer or 
> decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does not 
> make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another 
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it 
> might be beneficial for those who come from other relational databases and 
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming 
> languages.
>
> Franta

I think the biggest part of the issue is that you are thinking in
'Standard SQL' and then giving the user free reign in what SQL they are
going to use (so they COULD use some of the relaxation of limitations
provided by SQLite).

One big issue with your proposed module is that it (or at least programs
using it) are going to want to assume its assumptions, but there is not
an easy way to enforce them, as a program that doesn't use the module
could access the data base and break them.

-- 
Richard Damon

___
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

[Eric Grange]

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

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

[Donald Shepherd]

On Sun, 15 Dec 2019 at 1:40 am, Dominique Pellé 
wrote:

> Richard Hipp  wrote:
>
> > On 12/14/19, Dominique Pellé  wrote:
> > >
> > > I'm curious about what kind of change reduces memory per
> > > connection.
> > >
> >
> > The branch is here:
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
> >
> > I'll try to add more description later - busy with other things right
> > this moment.
>
> Ah, it about lookaside. I build with SQLITE_OMIT_LOOKASIDE
> so I suppose the mini-lookaside-take-2 branch will do nothing then
> in this case.
>
> Regards
> Dominique
> ___


Gut feel is that the divide will fall such that embedded use cases want the
memory and PC or equivalent use cases want the CPU.  I fall into the latter
so inevitably want the performance since 72k doesn't really move the needle
on our usage but we've found ourselves CPU bound a few times.

If that split on the usefulness is correct, is it reasonable to suggest
that since embedded cases will use switches to exclude everything they
don't need this should fall into that category?

I'm obviously just speculating on that front having not used it there
myself.

Regards,
Donald Shepherd.

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

Re: [sqlite] How to determine the column type? – virtual table?

[Simon Slavin]

On 14 Dec 2019, at 6:55pm, František Kučera  wrote:

> This is IMHO feasible, but would mean probably a lot of work. However it 
> might be beneficial for those who come from other relational databases and 
> prefer static typing rather than dynamic.

I don't think it's practical.  For instance, did you know that SQLite does not 
enforce string lengths ?  If you define a column as CHAR (100) SQLite 
completely ignores the length.  It'll store and return any string, no matter 
how long.

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

Re: [sqlite] How to determine the column type? – virtual table?

[J Decker]

I just don't see the trouble

https://github.com/d3x0r/SACK/blob/master/src/SQLlib/sqlstub.c#L3613-L3680
for each row for each column
= sqlite3_column_type( collection->stmt, idx - 1 )
and then get the data according to the type... sqlite3_column_double  (for
instance)
 It's not very expensive to get the data type; sqlite3 will have already
prepared its internal variant structure...
if you don't know the type, then you don't know what type go get (yes, you
can, get everything as a string, but then why do you care about the type
anyway? :)  )



On Sat, Dec 14, 2019 at 10:55 AM František Kučera 
wrote:

> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> > What he wants is different. He takes a basically arbitrary database
> > (user provided) and an arbitrary SQL statement (also user provided) and
> > he wants to determine what type a given column will present.
>
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the
> user wants integer in e.g. "size" column, he must say it explicitly by:
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to
> SQLite. It could provide a virtual table that will parse the query and
> return expected result set metadata. So before executing the query, I could
> do SELECT order, column_name, column_type FROM result_set_metadata WHERE
> sql = 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would
> return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be
> used for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared
> types
>
>  - analyze the operations done in the SELECT clause (operators, function
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer
> or decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does
> not make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it
> might be beneficial for those who come from other relational databases and
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming
> languages.
>
> Franta
>
> ___
> 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] How to determine the column type? – virtual table?

[František Kučera]

Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> What he wants is different. He takes a basically arbitrary database
> (user provided) and an arbitrary SQL statement (also user provided) and
> he wants to determine what type a given column will present.

Yes, I am looking for something that is usually called ResultSetMetaData.

Currently I have solved it by adding a new command line option, so if the user 
wants integer in e.g. "size" column, he must say it explicitly by: --type-cast 
"size" integer.

I can imagine a module, that will introduce optional static typing to SQLite. 
It could provide a virtual table that will parse the query and return expected 
result set metadata. So before executing the query, I could do SELECT order, 
column_name, column_type FROM result_set_metadata WHERE sql = 'SELECT 
mount_point, pass+1000 AS pass FROM fstab'; and it would return:

result_set_metadata:
 ╭─┬──┬──╮
 │ order (integer) │ column_name (string) │ column_type (string) │
 ├─┼──┼──┤
 │   1 │ mount_point  │ string   │
 │   2 │ pass │ integer  │
 ╰─┴──┴──╯
Record count: 2

to do this, it would have to:

 - parse the SQL (I am not sure whether internal SQLite parser could be used 
for it or if I had to do it myself)

 - check whether requested tables and columns exist and check the declared types

 - analyze the operations done in the SELECT clause (operators, function calls 
or CASE) and derive resulting type

 - return more generic type if types varies e.g. CASE that returns integer or 
decimal numbers will result in a decimal type

 - return an error if the CASE mixes e.g. integers and strings which does not 
make much sense and is probably a mistake

expectations and limitations:

 - the table contains only values of declared types

 - null is not perceived as a type itself but rather as a value of another type 
(missing integer, missing string etc.)

This is IMHO feasible, but would mean probably a lot of work. However it might 
be beneficial for those who come from other relational databases and prefer 
static typing rather than dynamic.

i.e. something like optional type hints and checks in dynamic programming 
languages.

Franta

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

Re: [sqlite] How to determine the column type? – explicit cast

[Richard Damon]

On 12/14/19 11:12 AM, Simon Slavin wrote:
> On 14 Dec 2019, at 10:46am, František Kučera  wrote:
>
>> SELECT cast(dump+100 AS integer) FROM fstab;
>>
>> the sqlite3_column_decltype() still does not return the integer type.
>>
>> Would it be possible to modify this function or add a new one, to tell the 
>> correct type at least if there is an explicit cast like this in given query?
> It works fine for me:
>
> SQLite version 3.28.0 2019-04-15 14:49:49
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
> sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
> sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER), 
> CAST (456.7 AS INTEGER));
> sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
> 123|text|123|integer|123.4|real
> 456|text|456|integer|456.0|real
> sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS 
> INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS 
> INTEGER)) FROM t;
> 123|integer|123|integer|123|integer
> 456|integer|456|integer|456|integer
>
> When you do your CAST when you store, and the column type is compatible with 
> the input value, the column type is what you declared the column type to be.
>
> But whether you do your cast() when you recall, the column type is always 
> INTEGER.  Which is what you want.

What he wants is different. He takes a basically arbitrary database
(user provided) and an arbitrary SQL statement (also user provided) and
he wants to determine what type a given column will present.

He is willing to assume that columns hold their declared data type
(otherwise the problem is truly impossible), and if the results goes
back to just a column, the answer is simple, the declared type of the
column (even though in many cases, the answer could also be NULL). The
problem is that there is no built in algebra to deduce what type an
expression will produce (assuming you know the types of the inputs), in
part because in SQLite you can't 'know' the type that the input would
be, and in part because sometimes result type will depend on the values
received.

The only answers I can think of are

1) retrieve all the results, taking whatever type SQLite says that value
is, and then process all the results for a give column to figure out
what (and if) that column produces (if because it might be inconsistent,
so you need to respond 'mixed' and maybe handle mixed INTEGER and REAL
some way).

2) Parse the expression yourself and determine the types (and know ahead
of time if there are problem columns). There might be ways to use some
internals of SQLite to help, but SQLite isn't going to do the job
itself, as it has no need for that answer (as it is based on assumptions
that SQLite doesn't make on the data).

-- 
Richard Damon

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

Re: [sqlite] How to determine the column type? – explicit cast

[Simon Slavin]

On 14 Dec 2019, at 10:46am, František Kučera  wrote:

> SELECT cast(dump+100 AS integer) FROM fstab;
> 
> the sqlite3_column_decltype() still does not return the integer type.
> 
> Would it be possible to modify this function or add a new one, to tell the 
> correct type at least if there is an explicit cast like this in given query?

It works fine for me:

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t (a TEXT, b INTEGER, c REAL);
sqlite> INSERT INTO t VALUES ('123', 123, 123.4);
sqlite> INSERT INTO t VALUES (CAST ('456' AS INTEGER), CAST (456 AS INTEGER), 
CAST (456.7 AS INTEGER));
sqlite> SELECT a,typeof(a),b,typeof(b),c,typeof(c) FROM t;
123|text|123|integer|123.4|real
456|text|456|integer|456.0|real
sqlite> SELECT cast(a AS INTEGER),typeof(cast(a AS INTEGER)),cast(b AS 
INTEGER),typeof(cast(b AS INTEGER)),cast(c AS INTEGER),typeof(cast(c AS 
INTEGER)) FROM t;
123|integer|123|integer|123|integer
456|integer|456|integer|456|integer

When you do your CAST when you store, and the column type is compatible with 
the input value, the column type is what you declared the column type to be.

But whether you do your cast() when you recall, the column type is always 
INTEGER.  Which is what you want.
___
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

[Dominique Pellé]

Richard Hipp  wrote:

> On 12/14/19, Dominique Pellé  wrote:
> >
> > I'm curious about what kind of change reduces memory per
> > connection.
> >
>
> The branch is here: 
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
>
> I'll try to add more description later - busy with other things right
> this moment.

Ah, it about lookaside. I build with SQLITE_OMIT_LOOKASIDE
so I suppose the mini-lookaside-take-2 branch will do nothing then
in this case.

Regards
Dominique
___
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

[Esdras Mayrink]

I think it should be turned on by default. Saving 72KB per connection is
worth the performance penalty.

On Sat, Dec 14, 2019 at 11:22 AM Dominique Pellé 
wrote:

> Richard Hipp  wrote:
>
> > 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.
>
> It depends of course, but for my project, saving 72KB of memory
> per connection is more important as many databases are opened,
> each consuming memory. It may be an unusual scenario though.
>
> Furthermore, since I set a heap soft limit for SQLite, saving
> memory per connection should leave more memory for page
> caching, hence more SQLite page cache hits, hence less I/Os.
>
> In any case, if user can choose at compile time or runtime,
> the default behavior does not matter for me.
>
> I'm curious about what kind of change reduces memory per
> connection.
>
> Regards
> Dominique
> ___
> 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] Performance vs. memory trade-off question

[Richard Hipp]

On 12/14/19, Richard Hipp  wrote:
> On 12/14/19, Dominique Pellé  wrote:
>>
>> I'm curious about what kind of change reduces memory per
>> connection.
>>
>
> The branch is here:
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
>
> I'll try to add more description later - busy with other things right
> this moment.

Background information on the lookaside memory allocator:
https://sqlite.org/malloc.html#_lookaside_memory_allocator


> --
> D. Richard Hipp
> d...@sqlite.org
>


-- 
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

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

[Richard Hipp]

On 12/14/19, Dominique Pellé  wrote:
>
> I'm curious about what kind of change reduces memory per
> connection.
>

The branch is here: https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2

I'll try to add more description later - busy with other things right
this moment.
-- 
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

Re: [sqlite] How to determine the column type?

[Richard Damon]

On 12/14/19 5:22 AM, František Kučera wrote:
> Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
>> With respect to SQLite, every column is the union of: Null, every Integer, 
>> every Float, every Text, every Blob.
> OK, we can close this thread with that it is currently impossible to track 
> the declared column types through relational operations.
>
It is currently impossible because it is actually impossible to know the
result type of some expressions except by knowing the values and
computing it. Some arithmetic operations have thier result type varied
based on the value of the result (overflow goes to REAL), a CASE
expression can return different types based on the value of an
expression, and then you have the fact that NULL has a unique type.

A common issue with computer systems is that when you have something
that looks easy to do for a lot of simple cases, but there actually are
some cases that are hard or impossible to determine, then those hard
cases make it hard to handle the general problem.

Thus even without using SQLite's flexibility it types stored in columns,
you can't predetermine the type of some expressions.

-- 

Richard Damon

___
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

[Dominique Pellé]

Richard Hipp  wrote:

> 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.

It depends of course, but for my project, saving 72KB of memory
per connection is more important as many databases are opened,
each consuming memory. It may be an unusual scenario though.

Furthermore, since I set a heap soft limit for SQLite, saving
memory per connection should leave more memory for page
caching, hence more SQLite page cache hits, hence less I/Os.

In any case, if user can choose at compile time or runtime,
the default behavior does not matter for me.

I'm curious about what kind of change reduces memory per
connection.

Regards
Dominique
___
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

[Gary R. Schmidt]

On 15/12/2019 00:27, Richard Hipp wrote:

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.


I'll vote for "default on"
- 72KB of heap is a fair old whack, particularly for those on bare 
silicon.
	- the half-a-step back in performance is a "meh," given that only those 
who manage to push the latest version to production quickly will 
probably even notice, that is, if they look for it.
	- 400-500 bytes is not a dramatic cost, but it still might be too much 
for someone's PROM.



Cheers,
GaryB-)
___
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

[Philip Bennefall]

Hi Richard,


I can see the advantage of both, but personally I prefer the greater 
speed with slightly more memory approach (e.g. what we have now).



Kind regards,


Philip Bennefall


On 12/14/2019 2:27 PM, Richard Hipp wrote:

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.



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

[sqlite] Performance vs. memory trade-off question

[Richard Hipp]

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

Re: [sqlite] How to determine the column type?

[Jean-Christophe Deschamps]

dump+100 (string), pass+1000 (string)

the type information disappears and former integers becomes mere strings


There must be something else going on here:

Z:> 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 (N integer);
sqlite> insert into T values (1), (2), (3);
sqlite> select N, N+100, typeof(N+100), N+1000, typeof(N+1000) from T;
1|101|integer|1001|integer
2|102|integer|1002|integer
3|103|integer|1003|integer
sqlite> .q

JcD 


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

Re: [sqlite] How to determine the column type? – explicit cast

[František Kučera]

P.S. Even if I do:

SELECT cast(dump+100 AS integer) FROM fstab;

the sqlite3_column_decltype() still does not return the integer type.

Would it be possible to modify this function or add a new one, to tell the 
correct type at least if there is an explicit cast like this in given query?

If I do: SELECT cast("xxx" AS integer); it returns 0. So I hope that it is not 
too immodest to expect that all values of such column will have the same type 
(or be null).

Franta


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

Re: [sqlite] How to determine the column type?

[František Kučera]

Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
> With respect to SQLite, every column is the union of: Null, every Integer, 
> every Float, every Text, every Blob.

OK, we can close this thread with that it is currently impossible to track the 
declared column types through relational operations.

Just to explain what my generic program does: it is typically used as a filter 
and works on the fly. The user feeds a stream of relations (tables) into it, 
the program does some transformations (execute SELECTs in this case) and sends 
a stream of another relations on the standard output.

The input relations have declared attribute types (currently boolean, integer 
and string) and it does not allow mixing various types in the same column.

For example the user can do this:

relpipe-in-fstab | relpipe-tr-sql --relation "x" "SELECT * FROM fstab WHERE 
type = ?" --parameter "ext4" | relpipe-out-tabular
x:
 
╭─┬──┬──┬───┬───┬┬╮
 │ scheme (string) │ device  (string) │ mount_point 
(string) │ type (string) │ options  (string) │ dump 
(integer) │ pass (integer) │
 
├─┼──┼──┼───┼───┼┼┤
 │ UUID    │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /   
 │ ext4  │ relatime,user_xattr,errors=remount-ro │  0 │ 
 1 │
 │ │ /dev/sde │ /mnt/data   
 │ ext4  │ relatime,user_xattr,errors=remount-ro │  0 │ 
 2 │
 
╰─┴──┴──┴───┴───┴┴╯
Record count: 2

If you look at the header, the integer types of the "dump" and "pass" columns 
are preserved (I get the type from the sqlite3_column_decltype() function).

But if the user just slightly modifies the query:

relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT mount_point, dump+100, 
pass+1000 FROM fstab WHERE type = ?" --parameter "ext4" | relpipe-out-tabular
x:
 ╭──┬───┬╮
 │ mount_point (string) │ dump+100 (string) │ pass+1000 (string) │
 ├──┼───┼┤
 │ /    │ 100   │ 1001   │
 │ /mnt/data    │ 100   │ 1002   │
 ╰──┴───┴╯
Record count: 2

the type information disappears and former integers becomes mere strings (a 
fallback generic type here). From the point of view of a developer who knows 
SQLite internals, this is obvious. But from the user's point of view, this is 
quite weird and unexpected behavior. Users expect that a mathematical operation 
will return the original (integer) type or at least some other numeric type – 
but not a text string. I would have to add something like --type-cast "dump" 
"integer" option to explicitly specify the types and convert the strings back 
to the integers. But I was wondering whether there is a more user-friendly 
automatic way.

The source codes and more information are available here: 
.

>
> With respect to a generic application programming language, every column is 
> of the most generic type, such as "Object" or "Any" for examples.
>
> Now, if you want to be more precise, you can say that the type of every 
> column is the union of all values currently in it.  This means that the type 
> of an empty column is the empty type consisting of zero values, which is a 
> subset of all other types just as the universal type is the superset of all 
> other types.
>
> Generally speaking, you want to support union types.

In my software, the most generic type is the text string. e.g. 123 will become 
"123", true will become "true" or byte array (when implemented) will become a 
hexadecimal text string. And it is currently also used as a fallback – if I can 
not get a better type from SQLite, the attribute is returned as a string. So 
the text string serves as that union type for me.

Franta

relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT * FROM fstab WHERE type 
= ?" --parameter "ext4" | relpipe-out-tabular 
x:
 
╭─┬──┬──┬───┬───┬┬╮
 │ scheme (string) │ device  (string) │ mount_point 
(string) │ type (string) │ options  (string) │ dump 
(integer) │ pass (integer) │
 

Re: [sqlite] How to determine the column type?

[Darren Duncan]

František,

Having done this myself, I will tell you that:

The only effective design for your "generic software that should work with any 
database model" is that every column is the universal type, the union of all 
other types; the type is the set of all representable values.


With respect to SQLite, every column is the union of: Null, every Integer, every 
Float, every Text, every Blob.


With respect to a generic application programming language, every column is of 
the most generic type, such as "Object" or "Any" for examples.


Now, if you want to be more precise, you can say that the type of every column 
is the union of all values currently in it.  This means that the type of an 
empty column is the empty type consisting of zero values, which is a subset of 
all other types just as the universal type is the superset of all other types.


Generally speaking, you want to support union types.

Do you have any questions to help you understand this?

-- Darren Duncan

On 2019-12-13 2:49 p.m., František Kučera wrote:

I know that SQLite uses dynamic types, so it is not easy… But what is the best 
way to determine the column type of a result set?

The sqlite3_column_decltype() works only if I select directly a column, but not 
when I do some other operations (call function, increment etc.).

The sqlite3_column_type() works for while iterating over particular rows. I can 
fetch the first row and get type here (expecting that all values in that column 
will have same type), but the problem is a) if the first value is NULL or b) if 
the result set is empty.

If I have e.g. "SELECT a + 1 AS x FROM t", is there a way to say that the x will be 
numeric? Or if I have "SELECT 1+1 AS x"?

I am writing a generic software that should work with any database model 
(tables are defined by the user) and I need to know the types, so I can 
properly present the results. Currently I use sqlite3_column_decltype() and 
will add options so the user could explicitly specify types of particular 
columns, but I am looking for a better way…


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