Re: [sqlite] SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

[Keith Medcalf]

On Wednesday, 30 October, 2019 16:33, mailing lists  
wrote:

>I face the following issue:

>1) SQLite has been compiled with SQLITE_THREADSAFE=1 and
>SQLITE_DEFAULT_SYNCHRONOUS=3
>2) I am opening in a thread a new database (standard journal mode) and
>creating some tables, indices etc. (explicit transaction)
>3) while creating the database a new database connection (read only) is
>established in another thread that tries to get some data by a prepared
>statement.

>In this second thread (step (3)) I get an SQLITE_ERROR in
>sqlite3_prepare. I actually expected an SQLITE_BUSY error. Remark: as
>step (2) is a transaction no tables exist when step (3) starts execution.

That is because your SQL statement has an error.  By the sounds of it the 
table(s) you are trying to query do not exist because you have not yet 
committed the transaction which is creating them.  

Key hints "new database" (contains nothing), creating tables etc in an explicit 
transaction, and while creating the database ... preparing a statement that 
reads the database fails with an error.

>Is my understanding correct that I only get an SQLITE_BUSY error when
>actually trying to run a query? In all other cases I should get different
>error codes, or?

Yes.

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

Re: [sqlite] SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

[Simon Slavin]

On 30 Oct 2019, at 10:33pm, mailing lists  wrote:

> In this second thread (step (3)) I get an SQLITE_ERROR in sqlite3_prepare. I 
> actually expected an SQLITE_BUSY error. Remark: as step (2) is a transaction 
> no tables exist when step (3) starts execution.
> 
> Is my understanding correct that I only get an SQLITE_BUSY error when 
> actually trying to run a query? In all other cases I should get different 
> error codes, or?

I cannot immediately solve your problem, but here are some things you didn't 
mention which might help.

Did you test it to see that if the other program (the one which writes) isn't 
running you don't get the error ?

SQLite has two locking errors: SQLITE_BUSY and SQLITE_LOCKED.

In order to prepare a statement, SQLite has to read the database.  It does this 
because preparing a statement requires it to know the layout of the table and 
which indexes are available.  If another connection has the database locked, 
SQLite cannot do this.

When you tell SQLite to open a database it does not do it.  It reads in the 
schema (structures of tables and indexes) only when you do the first thing that 
needs them.  So if you're writing a small test program, it might not be doing 
things when you expect it to.

If you are doing this as a test, that's fine.  If you are writing software for 
production use, then both programs should be setting a timeout of at least a 
few seconds:


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

[sqlite] SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

[mailing lists]

Hi,

I face the following issue:

1) SQLite has been compiled with SQLITE_THREADSAFE=1 and 
SQLITE_DEFAULT_SYNCHRONOUS=3
2) I am opening in a thread a new database (standard journal mode) and creating 
some tables, indices etc. (explicit transaction)
3) while creating the database a new database connection (read only) is 
established in another thread that tries to get some data by a prepared 
statement.

In this second thread (step (3)) I get an SQLITE_ERROR in sqlite3_prepare. I 
actually expected an SQLITE_BUSY error. Remark: as step (2) is a transaction no 
tables exist when step (3) starts execution.

Is my understanding correct that I only get an SQLITE_BUSY error when actually 
trying to run a query? In all other cases I should get different error codes, 
or?

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

Re: [sqlite] Generated columns in primary keys, indexes, and constraints

[Darren Duncan]

On 2019-10-30 12:52 p.m., Keith Medcalf wrote:


On Wednesday, 30 October, 2019 13:23, Darren Duncan  
wrote:


On 2019-10-30 12:02 p.m., Simon Slavin wrote:

On 30 Oct 2019, at 6:56pm, Darren Duncan  wrote:



"Generated columns may not be used as part of the PRIMARY KEY. (Future
versions of SQLite might relax this constraint for STORED columns.)"

Replace with this:

"VIRTUAL generated columns may not be used as part of the PRIMARY
KEY."



What happens if the generated column is based on a calculation
including rowid ?  At the time the values are calculated, rowid might not
yet be known.



I suggest one option is adding a restriction that rowid may not be used
directly or indirectly in a STORED generated column and only in a VIRTUAL one.


The rowid value is always known so this is not an issue.


One way or another I believe this is surmountable and stored generated
columns in a primary key is worth it.


In a rowid table, the primary key is the implicit or explicit rowid.  "primary key(...)" in such tables is merely and 
alternate spelling of unique(...), and you can specify an index on a generated column computed from the (explicit) rowid.  But 
since the rowid is already the primary key you cannot specify another one and if you do then your spelling "primary 
key" is automatically translated into the spelling "unique".  However, you must actually spell "primary 
key" as "unique" to generate a unique index on a generated column in a rowid table even if you have not explicitly 
declared the rowid column.

The restriction is that you cannot use a generated column as the primary key of 
a WITHOUT ROWID table, however, for those tables there is no rowid so something 
has to be the actual primary key since it cannot be the rowid.  However, in 
order to use a generated column as a primary key in a WITHOUT ROWID table, the 
value would have to be stored.  You can still create other indexes using the 
generated columns (whether virtual or stored) but you still need a stored 
primary key.  Lifting the restriction on the use of a stored generated column 
as the primary key for a without rowid table is the only restriction that could 
be lifted.


Okay, I think we're on the same page, so to be clear the scenario I want to have 
supported is that a STORED generated column can be used in the PRIMARY KEY of a 
WITHOUT ROWID table.


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

Re: [sqlite] Generated columns in primary keys, indexes, and constraints

[Keith Medcalf]

On Wednesday, 30 October, 2019 13:23, Darren Duncan  
wrote:

>On 2019-10-30 12:02 p.m., Simon Slavin wrote:
>> On 30 Oct 2019, at 6:56pm, Darren Duncan  wrote:

>>> "Generated columns may not be used as part of the PRIMARY KEY. (Future
>>> versions of SQLite might relax this constraint for STORED columns.)"
>>>
>>> Replace with this:
>>>
>>> "VIRTUAL generated columns may not be used as part of the PRIMARY
>>> KEY."

>> What happens if the generated column is based on a calculation
>> including rowid ?  At the time the values are calculated, rowid might not
>> yet be known.

> I suggest one option is adding a restriction that rowid may not be used
> directly or indirectly in a STORED generated column and only in a VIRTUAL one.

The rowid value is always known so this is not an issue.

> One way or another I believe this is surmountable and stored generated
> columns in a primary key is worth it.

In a rowid table, the primary key is the implicit or explicit rowid.  "primary 
key(...)" in such tables is merely and alternate spelling of unique(...), and 
you can specify an index on a generated column computed from the (explicit) 
rowid.  But since the rowid is already the primary key you cannot specify 
another one and if you do then your spelling "primary key" is automatically 
translated into the spelling "unique".  However, you must actually spell 
"primary key" as "unique" to generate a unique index on a generated column in a 
rowid table even if you have not explicitly declared the rowid column.

The restriction is that you cannot use a generated column as the primary key of 
a WITHOUT ROWID table, however, for those tables there is no rowid so something 
has to be the actual primary key since it cannot be the rowid.  However, in 
order to use a generated column as a primary key in a WITHOUT ROWID table, the 
value would have to be stored.  You can still create other indexes using the 
generated columns (whether virtual or stored) but you still need a stored 
primary key.  Lifting the restriction on the use of a stored generated column 
as the primary key for a without rowid table is the only restriction that could 
be lifted.

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

Re: [sqlite] Generated columns in primary keys, indexes, and constraints

[Darren Duncan]

On 2019-10-30 12:02 p.m., Simon Slavin wrote:

On 30 Oct 2019, at 6:56pm, Darren Duncan  wrote:


"Generated columns may not be used as part of the PRIMARY KEY. (Future versions of 
SQLite might relax this constraint for STORED columns.)"

Replace with this:

"VIRTUAL generated columns may not be used as part of the PRIMARY KEY."


What happens if the generated column is based on a calculation including rowid 
?  At the time the values are calculated, rowid might not yet be known.


I suggest one option is adding a restriction that rowid may not be used directly 
or indirectly in a STORED generated column and only in a VIRTUAL one.


Alternately, a longer term solution would be generate rowid as an independent 
prior step.  Like explicitly calling a sequence generator in one step and then 
using that as a then-constant input in composing the row.


While I can think of numerous uses of a stored generated column based on 
user-supplied values, I can't think of any use case for a generated column based 
on a rowid.


One way or another I believe this is surmountable and stored generated columns 
in a primary key is worth it.


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

[sqlite] PureFunc Detection Fix

[Keith Medcalf]

The "not working" PureFunc detection is because the sqlite3NotPureFunc needs to 
be checked in isDate if it is called with 0 arguments (in which a single 
parameter of 'now' is assumed).  This should fix the purefunc detection for 
CHECK constraints and Indexes.

I still think that OP_Function should be used for generated always as ... 
stored and generated always as ... virtual columns rather than OP_PureFunc, and 
that the all the datetime functions should have the same 
SQLITE_SLOCHNG|SQLITE_FUNC_CONSTANT flags on them.

Creating an index on a generated always as ... stored should not be 
problematic, however allowing an index on a generated always as ... virtual 
that contains an impure function is indeed a problem -- I do not know how you 
would detect that.

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

Re: [sqlite] Generated columns in primary keys, indexes, and constraints

[Simon Slavin]

On 30 Oct 2019, at 6:56pm, Darren Duncan  wrote:

> "Generated columns may not be used as part of the PRIMARY KEY. (Future 
> versions of SQLite might relax this constraint for STORED columns.)"
> 
> Replace with this:
> 
> "VIRTUAL generated columns may not be used as part of the PRIMARY KEY."

What happens if the generated column is based on a calculation including rowid 
?  At the time the values are calculated, rowid might not yet be known.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generated columns in primary keys, indexes, and constraints

[Darren Duncan]

On 2019-10-30 3:12 a.m., Richard Hipp wrote:

On 10/30/19, Darren Duncan  wrote:


Ideally a PRIMARY KEY would have no restrictions that a UNIQUE constraint
lacks;
they are conceptually the same thing, a subset of the columns of the row
that
uniquely identifies the row in the table, and designating one as PRIMARY is
completely arbitrary in that sense.



For rowid-tables in SQLite, the PRIMARY KEY is really just a UNIQUE
constraint.  But that is kind of a bug that is retained for historical
compatibility - not something to be proud of.  Let's ignore that case
for the moment.

In a WITHOUT ROWID table, the PRIMARY KEY really is the key used by
the B-Tree algorithm to locate records on disk.  Hence, the PRIMARY
KEY really does need to be stored on disk.  Theoretically, one could
have the B-Tree algorithm itself compute the values of keys
on-the-fly.  But that is not possible in SQLite without significantly
rearchitecting the system.  Expressions are evaluated using multiple
opcodes in the bytecode engine, but B-Tree search is a single opcode.
It is unclear how the B-Tree engine could be convinced to run multiple
byte-code operations in response to each key comparison.

All that said, it wouldn't really be a problem use a STORED generated
column as a PRIMARY KEY, since the value of the STORED column is
sitting there on disk and is hence readily available to b-tree.  In
fact, that might just work if I merely remove the restriction.  But
using a VIRTUAL generated column as a PRIMARY KEY would be an issue.


Thank you.

If I wasn't clear, it was specifically the STORED variant I was advocating 
supporting with PRIMARY KEY, that provides all the benefits I looked for, which 
I felt in theory be as easy to implement as a normal column PRIMARY KEY because 
the value would be calculated before insertion.


So I request that the draft document be amended like as follows.

Take this line:

"Generated columns may not be used as part of the PRIMARY KEY. (Future versions 
of SQLite might relax this constraint for STORED columns.)"


Replace with this:

"VIRTUAL generated columns may not be used as part of the PRIMARY KEY."

... with optional comment about possibly being relaxed in the future.

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

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Keith Medcalf]

On Wednesday, 30 October, 2019 06:41, Dominique Devienne  
wrote:

>On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp  wrote:

>> On 10/29/19, Keith Medcalf  wrote:

>> > Before you change anything, I think that is incorrect for the various
>> > datetime functions.  I think they SHOULD be permitted in CHECK
>> > constraints and in generated always columns, whether those are 
>> > stored or virtual, whether or not parameters are given since they 
>> > are constant.  They should not be permitted in indexes however unless 
>> > they are pure (deterministic).

>> i believe the purpose of a CHECK constraint is declare an eternal
>> truth about the database content, not merely something that was true
>> at the point in time when the content was first inserted.  Am I wrong?

> The kind of CHECK constraint Keith mentioned would run afoul of
> https://www.sqlite.org/pragma.html#pragma_integrity_check so you're
> probably right that allowing these non-deterministic function is not 
> a good idea (on 2nd thought...) --DD

Hmmm.  Yes, I believe that is correct that check constraint on a table is a 
truth about the table and should always evaluate to True (or NULL), and should 
not become False due to the passage of time (or anything else).  A tuple that 
passes a CHECK constraint on INSERT (or UPDATE) should eternally meet that 
constraint, so allowing a non-deterministic reference in a CHECK constraint 
could violate this.

I do not think that such a restriction would apply to the value of a generated 
column though UNLESS that column is used in a CHECK constraint or an index, and 
even then only for a virtual generated column (a stored generated column does 
not have this problem, however, and does not need to be restricted, since the 
value can only change when the tuple is inserted or updated).

I can see this getting very complicated very quickly.

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

Re: [sqlite] Multiple files for a single SQLite database

[J. King]

On October 30, 2019 9:58:20 a.m. EDT, Simon Slavin  wrote:

>There are things you cannot split across different database files.  For
>instance, you cannot declare a FOREIGN KEY that refers to a table in a
>different schema.  Because SQLite can't be sure you'll have that schema
>available.

It's also worth noting that in WAL mode transactions are not atomic across 
database files (they are atomic in the default rollback-journal mode, however).

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

Re: [sqlite] Multiple files for a single SQLite database

[David Raymond]

"There's a limit, somewhere around ten, I think.  Though you can increase it by 
explicitly messing with limits and compilation options."

Default is 10, can be raised to 125.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple files for a single SQLite database

[Simon Slavin]

On 30 Oct 2019, at 1:41pm, Aydin Ozgur Yagmur  wrote:

> SQLite stores a single database in a single file.
> 
> According to the specific project requirements and due to the performance 
> concerns;
> 
> When working with large databases, is it possible to explicitly tell SQLite 
> not to store the whole DB in a single file and store different tables in 
> different files instead?

SQLite can open with several different files on the same connection using the 
ATTACH DATABASE command:

<https://www.sqlite.org/lang_attach.html>

Open one database in the normal way.  This could be an empty database with no 
tables in.  This database automatically gets the schema name 'main'.  But you 
should supply schema names for attached databases:
ATTACH DATABASE 'second.sqlite' AS suppliers
ATTACH DATABASE 'third.sqlite' AS customers
etc..

There's a limit, somewhere around ten, I think.  Though you can increase it by 
explicitly messing with limits and compilation options.

In your SQL commands you must refer to the schema as part of the table name.  
Otherwise SQLite assumes that the table is part of the 'main' database.  So for 
a table "invoices" you might use

SELECT customer_num, pre_tax_total, post_tax_total
FROM customers.invoices
WHERE invoice_date = "20191030"

There are things you cannot split across different database files.  For 
instance, you cannot declare a FOREIGN KEY that refers to a table in a 
different schema.  Because SQLite can't be sure you'll have that schema 
available.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple files for a single SQLite database

[Aydin Ozgur Yagmur]

SQLite stores a single database in a single file.

According to the specific project requirements and due to the performance
concerns;

When working with large databases, is it possible to explicitly tell SQLite
not to store the whole DB in a single file and store different tables in
different files instead?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Dominique Devienne]

On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp  wrote:

> On 10/29/19, Keith Medcalf  wrote:
> >
> > Before you change anything, I think that is incorrect for the various
> > datetime functions.  I think they SHOULD be permitted in CHECK
> constraints
> > and in generated always columns, whether those are stored or virtual,
> > whether or not parameters are given since they are constant.  They should
> > not be permitted in indexes however unless they are pure (deterministic).
>
> i believe the purpose of a CHECK constraint is declare an eternal
> truth about the database content, not merely something that was true
> at the point in time when the content was first inserted.  Am I wrong?
>

The kind of CHECK constraint Keith mentioned would run afoul of
https://www.sqlite.org/pragma.html#pragma_integrity_check so you're
probably right
that allowing these non-deterministic function is not a good idea (on 2nd
thought...) --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Dominique Devienne]

On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp  wrote:

> On 10/29/19, Keith Medcalf  wrote:
> >
> > Before you change anything, I think that is incorrect for the various
> > datetime functions.  I think they SHOULD be permitted in CHECK
> constraints
> > and in generated always columns, whether those are stored or virtual,
> > whether or not parameters are given since they are constant.  They should
> > not be permitted in indexes however unless they are pure (deterministic).
>
> i believe the purpose of a CHECK constraint is declare an eternal
> truth about the database content, not merely something that was true
> at the point in time when the content was first inserted.  Am I wrong?
>

That makes sense too, indeed. Basically an "invariant" as we often say.

Yet I see Keith's use-cases as compelling enough to leave that decision to
the user,
rather than the implementation making it for us. Or perhaps you think
triggers should
be used instead, for those use cases? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Richard Hipp]

On 10/29/19, Keith Medcalf  wrote:
>
> Before you change anything, I think that is incorrect for the various
> datetime functions.  I think they SHOULD be permitted in CHECK constraints
> and in generated always columns, whether those are stored or virtual,
> whether or not parameters are given since they are constant.  They should
> not be permitted in indexes however unless they are pure (deterministic).

i believe the purpose of a CHECK constraint is declare an eternal
truth about the database content, not merely something that was true
at the point in time when the content was first inserted.  Am I wrong?

-- 
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] Generated columns in primary keys, indexes, and constraints

[Richard Hipp]

On 10/30/19, Darren Duncan  wrote:
>
> Ideally a PRIMARY KEY would have no restrictions that a UNIQUE constraint
> lacks;
> they are conceptually the same thing, a subset of the columns of the row
> that
> uniquely identifies the row in the table, and designating one as PRIMARY is
> completely arbitrary in that sense.
>

For rowid-tables in SQLite, the PRIMARY KEY is really just a UNIQUE
constraint.  But that is kind of a bug that is retained for historical
compatibility - not something to be proud of.  Let's ignore that case
for the moment.

In a WITHOUT ROWID table, the PRIMARY KEY really is the key used by
the B-Tree algorithm to locate records on disk.  Hence, the PRIMARY
KEY really does need to be stored on disk.  Theoretically, one could
have the B-Tree algorithm itself compute the values of keys
on-the-fly.  But that is not possible in SQLite without significantly
rearchitecting the system.  Expressions are evaluated using multiple
opcodes in the bytecode engine, but B-Tree search is a single opcode.
It is unclear how the B-Tree engine could be convinced to run multiple
byte-code operations in response to each key comparison.

All that said, it wouldn't really be a problem use a STORED generated
column as a PRIMARY KEY, since the value of the STORED column is
sitting there on disk and is hence readily available to b-tree.  In
fact, that might just work if I merely remove the restriction.  But
using a VIRTUAL generated column as a PRIMARY KEY would be an issue.

-- 
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] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[x]

Would any kind soul be willing to explain the deterministic problem to me? I 
can see that having now() in a virtual column would be a waste of time as the 
virtual column value would change every time it was read but if it was being 
stored then why not?



Also, I take it deterministic udf’s can be used in virtual columns. If that’s 
the case what’s to stop you from declaring a udf as deterministic and calling 
now() inside that?



I’m obviously missing something. I’m guessing there must be code in sqlite.c 
that needs the same value returned at different times.








From: sqlite-users  on behalf of 
Dominique Devienne 
Sent: Wednesday, October 30, 2019 9:21:12 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have 
SQLITE_FUNC_CONSTANT ?

On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf  wrote:

> On Tuesday, 29 October, 2019 23:05, Simon Slavin 
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf  wrote:
>
> >> Before you change anything, I think that is incorrect for the various
> >> datetime functions.  I think they SHOULD be permitted in CHECK
> >> constraints and in generated always columns, whether those are stored or
> >> virtual, whether or not parameters are given since they are constant.
> >> They should not be permitted in indexes however unless they are pure
> >> (deterministic).
>
> > But can SQLite tell the difference at that stage ?  For instance,
>
> Personally, I do not see a problem with permitting them to be used
> anywhere EXCEPT in an index expression.
>

+1. I completely agree with Keith, and the use-cases he outlined are great
examples of enforcing business logic using CHECK constraints.

I especially like the fact one cannot alter these stored generated values,
again adding a level of integrity to the DB. --DD
___
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] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Dominique Devienne]

On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf  wrote:

> On Tuesday, 29 October, 2019 23:05, Simon Slavin 
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf  wrote:
>
> >> Before you change anything, I think that is incorrect for the various
> >> datetime functions.  I think they SHOULD be permitted in CHECK
> >> constraints and in generated always columns, whether those are stored or
> >> virtual, whether or not parameters are given since they are constant.
> >> They should not be permitted in indexes however unless they are pure
> >> (deterministic).
>
> > But can SQLite tell the difference at that stage ?  For instance,
>
> Personally, I do not see a problem with permitting them to be used
> anywhere EXCEPT in an index expression.
>

+1. I completely agree with Keith, and the use-cases he outlined are great
examples of enforcing business logic using CHECK constraints.

I especially like the fact one cannot alter these stored generated values,
again adding a level of integrity to the DB. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[x]

I don’t think I did say that Marc. All I was saying was the ON didn’t have to 
refer to the closest join.



select * from

tblA inner join tblB ON tblA.key = tblC key – no mention of tblB in ON

inner join tblC ON tblA.key = tblB.key; – no mention of tblC in ON



Normally that would be written as



select * from

tblA inner join tblB ON tblA.key = tblB key

inner join tblC ON tblA.key = tblC.key;



I was just pointing out that they’re the same thing as the ONs are moved to the 
where



select * from tblA inner join tblB inner join tblC

where tblA.key = tblB.key AND tblA.key=tblC.key



which I thought illustrated Keith’s point.








From: sqlite-users  on behalf of 
Allen, Marc 
Sent: Monday, October 28, 2019 5:27:57 PM
To: SQLite mailing list 
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE

Sorry for top posting.. I'm way too lazy to figure out how to make Outlook play 
nicely.

You say:

FROM R LEFT OUTER JOIN S
 ON R.key = S.key AND R.col = 'foo'

Here,

 AND R.col = 'foo'

 while valid syntax (unfortunately), has no effect.

However, this isn't true.  While it doesn't constrain R, it does constrain the 
join.  This will still return all rows of R, but all S columns will be NULL for 
any row where R.col is not 'foo'.

This is actually useful, especially for efficiency.  This allows the database 
to not even bother doing the lookup on S if R.col != 'foo' and still return 
that R record.

Marc


Marc L. Allen | Software Engineer IV, PDI Marketing Cloud Solutions
2551 Eltham Ave. Suite N, Norfolk, VA 23513
Office  757.853.3000 x6015
Direct  757.605.6015
mlal...@pdisoftware.com
www.pdisoftware.com 


On 10/28/19, 1:20 PM, "sqlite-users on behalf of James K. Lowden" 
 wrote:

On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher  wrote:

> My colleagues and I are trying to understand the role of ON
> constraints vs WHERE clauses in JOINs.

ON applies before JOIN.  WHERE applies after.

That's a loose interpretation, but IMO it's nevertheless a useful way
to think about it.

It *mostly* doesn't matter.  It was invented for outer joins.

In SQL-89, there was only the inner join. There was no JOIN keyword.
You listed all the tables in FROM:

 FROM R, S
 WHERE R.key = S.key

and all was good with the world.

To support outer joins, vendors invented various syntaxes.  To unifiy
them, SQL-92 defined JOIN and ON:

 FROM R JOIN S
 ON R.key = S.key

with that syntax, JOIN could be modifed to be LEFT or RIGHT outer, and
the inner table could be further restricted:

 FROM R LEFT OUTER JOIN S
 ON R.key = S.key AND S.col = 'foo'

That means: all rows from R joined to rows in S for which col = 'foo'.
If no such S rows exist, every result row will be NULL for the S
columns.  Compare with

 FROM R LEFT OUTER JOIN S
 ON R.key = S.key
 WHERE S.col = 'foo'

Here, R and S are joined, and the WHERE test is applied to the joined
result.  If no such S rows exist, there will be no result rows at all.

When wrestling with this topic, it's useful to remember that ON doesn't
constrain the outer table:

 FROM R LEFT OUTER JOIN S
 ON R.key = S.key AND R.col = 'foo'

Here,

 AND R.col = 'foo'

while valid syntax (unfortunately), has no effect.  R is the outer
table, and all rows of the outer table are always produced, no matter
what ON says about it.  Perhaps the above should mean:

 FROM (select * from R WHERE R.col = 'foo') as R
 LEFT OUTER JOIN S
 ON R.key = S.key

but it does not.  Perfection remains, as ever, elusive.

--jkl


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


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

Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

[Keith Medcalf]

On Tuesday, 29 October, 2019 23:05, Simon Slavin  wrote:

>On 30 Oct 2019, at 3:58am, Keith Medcalf  wrote:

>> Before you change anything, I think that is incorrect for the various
>> datetime functions.  I think they SHOULD be permitted in CHECK
>> constraints and in generated always columns, whether those are stored or
>> virtual, whether or not parameters are given since they are constant.
>> They should not be permitted in indexes however unless they are pure
>> (deterministic).

> But can SQLite tell the difference at that stage ?  For instance,

>julianday('2019-10-30') is deterministic
>   julianday('now') is non-deteerministic

>Using 'now' as an argument gives different results for the same parameter
>values.  Worse still, the 'now' may not be explicit, you may have a table
>column with '2019-10-30' in one row and 'now' in another.

This applies to all the datetime functions, of course, not just the julianday 
function.

Within the context of a given statement, the value of julianday('now') or 
julianday() is a constant and is deterministic (and it remains thus no matter 
the additional parameters).  However, two statements executed at a different 
time will return different values each corresponding to the constant and fully 
deterministic value that was in effect at that particular 'now' when the 
statement was executed.  

>SQLite cannot make the distinction.  SQLITE_DETERMINISTIC applies to all
>values of the parameters of a function.  And since at least one value can
>yield different results, julianday() has to be marked as non-
>deterministic.

SQLITE_DETERMINISTIC means that the return value is dependent on the value of 
its arguments, and only the value of its arguments, and nothing but the value 
of its arguments, forever and ever, yesterday, today, and tomorrow.  None of 
the datetime functions which use a 'now', 'utc', or 'localtime' argument meet 
that requirement (or have no arguments).

SQLITE_SLOCHNG|SQLITE_FUNC_CONSTANT means that the return value is as above BUT 
ONLY WITHIN THE CURRENT STATEMENT EXECUTING NOW, thus such functions cannot be 
used in an index since they *may* be different for each statement execution and 
an index persists across statements.

Functions that have neither of the above flags are "volatile" and the results 
*may be different each time the function is called, even with the same 
arguments*.  An example is the random() function.  Or the uuid() function.  
These cannot be used in index expressions either.  Nor in generated columns.

>The standard way to handle this is that instead understanding the string
>'now' you have a function now() marked as non-deterministic.  Then
>julianday() can be marked as deterministic.  But it's too late for that.

Well, the issue is that one has to look inside the function parameters to 
determine whether or not the function is ytuly deterministic, however, no 
matter the parameters the datetime functions are all constant within a 
statement and the problem only arises if you want to use them in an index 
expression.

Personally, I do not see a problem with permitting them to be used anywhere 
EXCEPT in an index expression.

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