Re: [sqlite] Option to control implicit casting

[Joshua Thomas Wise]

When you need a feature-packed embedded SQL database, there aren’t many other 
options to reach for. I’m not suggesting that SQLite3 has a responsibility to 
satisfy every need just because it has beat out most other competition, but I’m 
in a situation where either I write every elementary integer operation as a 
custom function and convince my entire team to ONLY use those functions, or I 
write my own fork of SQLite3. Of course, option 1 will be much easier, but it’s 
also very messy and awkward. It would just be nice if SQLite3, being a 
relational database that seems to take database corruption very seriously, 
could provide some guarantees about the precision of answers given for basic 
arithmetic.

> On Apr 10, 2019, at 10:55 AM, Keith Medcalf  wrote:
> 
> 
> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise 
> mailto:joshuathomasw...@gmail.com>> wrote:
> 
>> This is not enough. Because of implicit casting, an integer (a
>> precise value) could be passed through a series of operations that
>> outputs an integer, satisfying the check constraint, but it still
>> could’ve been converted to a floating point (imprecise value) at some
>> intermediate step due to integer overflow, potentially resulting in
>> an incorrect answer. There’s currently no way to guarantee that a
>> value will always yield precise results in SQLite3.
> 
>> Here’s an example:
>> CREATE TABLE squares (
>>  x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
>>  y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
>> );
>> INSERT INTO squares VALUES (1 << 40, 1 << 40);
>> SELECT x * y & ~1 AS even_numbered_area FROM squares;
> 
>> In many cases, it’s better for the above SELECT statement to return
>> an error or NULL, but currently it gives an incorrect answer.
>> Checking its type won’t help either, because it does indeed return an
>> integer.
> 
> The answers are correct.  SELECT x * y from squares returns the correct 
> result, as does the bitwise and with -2.
> 
> If you wish to "be able to multiply two 64-bit integers" entirely in the 
> integer domain with the overflow doing something other than "convert the 
> arguments into floating point and do the operation in floating point" then 
> you are completely free to write a function that does precisely and exactly 
> what you think you want it to do and behave and do precisely that which you 
> wish.  It is so simple to do that during the time taken to read your message 
> and compose this response I could have written pretty much all the basic 
> operators written this way, compiled, tested, and moved the code into 
> production.
> 
> Some languages when multiplying a trail of 47 64-bit integers might return a 
> 3000 bit integer.  Others might explode.  Some might cause the universe to 
> reach heat death.  SQLite3 attempts to do what you told it to do by 
> converting the overflowing operands into floating point, and then using 
> floating point.  
> 
> If you do not like that you are free to either (a) write your own 
> multiplication function that works the way you think it ought to work, and 
> use that or (b) use something else that is more akin to your liking.
> 
> I do not like Java.  It is long winded, requires a 400" monitor to be able to 
> see anything at all, and is just about the stupedest hunk of crap that I have 
> ever seen in my entire life.  Since I have no wish to "fix" it, I just use 
> something more apropos.  Mutatis mutandis JavaCripple / Rust / Go / Varnish / 
> JollyGoodCrap / C# / Cflat  and most of the other newfangled hogwash -- I 
> will stick to C, PL/1, COBOL, FORTRAN and RPG thank-yee-very-much!
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org 
> <mailto:sqlite-users@mailinglists.sqlite.org>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
> <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] Option to control implicit casting

[Joshua Thomas Wise]

This is not enough. Because of implicit casting, an integer (a precise value) 
could be passed through a series of operations that outputs an integer, 
satisfying the check constraint, but it still could’ve been converted to a 
floating point (imprecise value) at some intermediate step due to integer 
overflow, potentially resulting in an incorrect answer. There’s currently no 
way to guarantee that a value will always yield precise results in SQLite3.

Here’s an example:
CREATE TABLE squares (
x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
);
INSERT INTO squares VALUES (1 << 40, 1 << 40);
SELECT x * y & ~1 AS even_numbered_area FROM squares;

In many cases, it’s better for the above SELECT statement to return an error or 
NULL, but currently it gives an incorrect answer. Checking its type won’t help 
either, because it does indeed return an integer.


> On Apr 9, 2019, at 2:06 PM, James K. Lowden  wrote:
> 
> On Mon, 8 Apr 2019 23:08:18 -0400
> Joshua Thomas Wise  wrote:
> 
>> I propose there should be a compile-time option to disable all
>> implicit casting done within the SQL virtual machine. 
> 
> You can use SQLite in a "strict" way: write a CHECK constraint for
> every numerical column.  
> 
> Just don't do that for tables that are loaded by the .import comand.
> As I reported here not long ago, .import rejects numeric literals.
> Apparently, the value is inserted as a string and rejected, instead of
> being converted to a number first.  
> 
> --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] Option to control implicit casting

[Joshua Thomas Wise]

SQLite3 uses manifest typing, which is great and provides a ton of flexibility. 
However, due to implicit casting rules, many operations can accidentally result 
in a different value than what was desired. If programmers don’t guard against 
every possible cause of implicit casting, many error situations are swallowed 
and instead can result in data corruption. I propose there should be a 
compile-time option to disable all implicit casting done within the SQL virtual 
machine. The option could cause all type-incompatible operations to return 
NULL, or it could cause these operations to throw hard errors. Either approach 
would be similarly useful.

Here are some examples of how implicit casting can lead to surprising results:

1. If invoking SUM() would cause integer overflow, a hard error is returned. 
The same things happens with ABS(). However, if integer overflow occurs when 
using the + operator, a REAL value is returned instead.

2. Many built-in string functions will automatically cast BLOBs to TEXTs, but 
those could contain embedded nuls, leading to undefined behavior.

3. Declaring a column with INTEGER affinity does not actually force its values 
to be integers. An integer that is out of range could be stored as a REAL 
value, unexpectedly changing the behavior of functions such as SUM() and ABS().


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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Joshua Thomas Wise]

> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
> severals moons ago, who convinced me that the IEEE 754 encoding was not
> an ideal storage format for databases]


I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?

> On Apr 3, 2019, at 1:56 PM, Simon Slavin  wrote:
> 
> On 3 Apr 2019, at 6:51pm, Warren Young  wrote:
> 
>> On Apr 3, 2019, at 6:30 AM, Lifepillar  wrote:
>> 
>>> does SQLite support indexes on blobs?
>> 
>> It claims to:
> 
> Indeed.  Be careful to verify whether, from the perspective of your 
> programming language, it considers the first or the last byte to be most 
> significant.  I've seen people caught out by a similar issue.
> ___
> 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] Feature request: dynamically referenced bind parameters

[Joshua Thomas Wise]

Oooo this is really neat. Thanks!


> On Mar 27, 2019, at 5:12 PM, Richard Hipp  wrote:
> 
> See https://www.sqlite.org/carray.html
> 
> -- 
> 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] Feature request: dynamically referenced bind parameters

[Joshua Thomas Wise]

I’ve commonly encountered cases where I have a many-to-many relationship, and I 
would like to retrieve those relationships in a single query.

For example:

CREATE TABLE staff (
  email TEXT PRIMARY KEY,
  name TEXT
);
CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  body TEXT,
  publish_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
);
CREATE TABLE contributions(
  article INTEGER REFERENCES articles(id),
  staff TEXT REFERENCES staff(email),
  PRIMARY KEY(article, staff),
);

First, I select the N most recently published articles:

SELECT * FROM articles
WHERE publish_date <= CURRENT_TIMESTAMP
ORDER BY publish_date DESC LIMIT ?;

Then, I’ll build a query like this to retrieve the staff that are responsible 
for writing those articles:

SELECT staff.* FROM staff, contributions
WHERE contributions.staff = staff.email
AND contributions.article IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); -- assuming 
N was 12 in the first query

However, I need to prepare a new statement every time I do this, depending on 
the value of N in the first query.

SQLite3 already allows us to create a large number of bind parameters without 
explicitly declaring each one, via ?999 syntax. Now, if we had the ability to 
reference those bind parameters dynamically, the second query above could be 
something like this:

WITH ids(id, n) AS (
SELECT param(1), 1
UNION ALL
SELECT param(n + 1), n + 1 FROM ids WHERE n < param_count())
SELECT DISTINCT ids.id, staff.* FROM staff, contributions, ids
WHERE contributions.staff = staff.email
AND contributions.article = ids.id;




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

Re: [sqlite] sqlite3_db_filename returns an empty string when null pointer is promised

[Joshua Thomas Wise]

I recently ran into this as well. NULL is not the same as “”, and it took me a 
really long time of debugging before I realized it was the sqlite3 
documentation at fault.


> On Mar 13, 2019, at 6:03 PM, Alex Alabuzhev  wrote:
> 
> Hi,
> 
> https://www.sqlite.org/c3ref/db_filename.html:
> 
>> If there is no attached database N on the database connection D, or if
> database N is a temporary or in-memory database, then a NULL pointer is
> returned.
> 
> However, when called for :memory: db the function actually returns "".
> 
> Looking at the code:
> 
> /*
> ** Return the full pathname of the database file.
> **
> ** Except, if the pager is in-memory only, then return an empty string if
> ** nullIfMemDb is true.  This routine is called with nullIfMemDb==1 when
> ** used to report the filename to the user, for compatibility with legacy
> ** behavior.  But when the Btree needs to know the filename for matching to
> ** shared cache, it uses nullIfMemDb==0 so that in-memory databases can
> ** participate in shared-cache.
> */
> SQLITE_PRIVATE const char *sqlite3PagerFilename(Pager *pPager, int
> nullIfMemDb){
>  return (nullIfMemDb && pPager->memDb) ? "" : pPager->zFilename;
> }
> 
> - as the comment says, it returns an empty string in case of in-memory mode
> (although "nullIfMemDb" confusingly implies null).
> 
> I have no idea who is correct here - the code or the documentation - but
> one of them should probably be corrected?
> 
> Thanks.
> 
> -- 
> Best regards,
>  Alex
> ___
> 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] Recursive CTE on tree with doubly linked items

[Joshua Thomas Wise]

Another way of implementing ordered siblings is to use a floating point 
“position” column instead of maintaining links to siblings via foreign keys. 
The advantage of a “position” column is that the data model maintains 
consistency automatically—you don’t need to painstakingly make sure all sibling 
pointers are correct. When using sibling pointers, there are many “invalid” 
states you could find yourself in. With a position column, all possible states 
are valid. This is a much more “relational” approach.

CREATE TABLE tree (
id INTEGER PRIMARY KEY,
parent INTEGER,
position REAL,
UNIQUE(parent, position),
CHECK((parent IS NULL) = (position IS NULL)),
FOREIGN KEY(parent) REFERENCES tree(id) ON DELETE CASCADE ON UPDATE SET 
NULL
);

Now, basic tree operations become simple:
Create root node:
INSERT INTO tree DEFAULT VALUES
Append child:
INSERT INTO tree (parent, position) VALUES (@parent, @position)
To insert a node between two existing nodes, set @position to be 
((left.position + right.position) / 2).
Delete a node:
DELETE FROM tree WHERE id = @id
No need to maintain sibling links
Swap two sibling nodes:
Simply swap their positions (using some intermediate value to get around the 
UNIQUE constraint)

You can even create a view to dynamically expose sibling links, without having 
to manually maintain them:

CREATE VIEW doubly_linked_tree(id, parent, prev, next) AS
SELECT id, parent, lag(id) OVER siblings, lead(id) OVER siblings
FROM tree
WINDOW siblings AS (PARTITION BY parent ORDER BY position);

One downside to “position” column approach is the finite precision of floating 
point values. For example, inserting a new node between two existing nodes 
implies finding the average of the two sibling positions. If those siblings 
have position values of 1 and 2, only 52 nodes can be inserted between them 
before we run out of floating point real-estate.

One solution is to use a view and trigger to implement a “normalize” function:

CREATE TEMP VIEW normalize_tree(parent) AS SELECT NULL;
CREATE TEMP TABLE _children(id INTEGER PRIMARY KEY, position REAL);
CREATE TEMP TRIGGER normalize_tree_impl INSTEAD OF UPDATE ON normalize_tree
BEGIN
INSERT INTO _children
SELECT id, row_number() OVER (ORDER BY position)
FROM tree
WHERE parent = new.parent
ORDER BY position;
UPDATE tree
SET position = (SELECT position FROM _children WHERE id = 
tree.id)
WHERE EXISTS(SELECT position FROM _children WHERE id = tree.id);
DELETE FROM _children;
END;

You can then normalize the positions of all direct children of a given node, so 
that those children all have integral positions ascending from 1:

UPDATE normalize_tree SET parent = @parent

Hopefully these ideas are helpful to you.

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

Re: [sqlite] [EXTERNAL] Re: Missing docs

[Joshua Thomas Wise]

Sure it’s documented in the release log, but it’s not documented in the C-API 
(https://sqlite.org/c3ref/expanded_sql.html), and it’s not documented among 
every other compile-time option (https://www.sqlite.org/compile.html).

I don’t know why you listed that second link. It doesn’t mention anything about 
SQLITE_ENABLE_NORMALIZE. Anyone reading that documentation would simply assume 
it’s available by default. Perhaps some CDN is causing us to view different 
versions of that page? This is what I see: https://imgur.com/J6ctQqT


> On Mar 15, 2019, at 1:33 PM, Hick Gunter  wrote:
> 
> Actually It is documented
> 
> See https://sqlite.org/changes.html look for release 3.26.0
> and https://sqlite.org/c3ref/expanded_sql.html
> and https://sqlite.org/releaselog/3_26_0.html where the compile option is 
> mentioned
> 
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Joshua Thomas Wise
> Gesendet: Freitag, 15. März 2019 18:26
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] Missing docs
> 
> Yes sorry, it was a erroneous copy-paste. I was referring to 
> sqlite3_normalized_sql().
> 
> As Keith said, it’s only available if the SQLITE_ENABLE_NORMALIZE compile 
> option is used, but that is not documented anywhere.
> 
> 
>> On Mar 15, 2019, at 3:56 AM, Keith Medcalf  wrote:
>> 
>> 
>> These docs:
>> 
>> https://sqlite.org/c3ref/expanded_sql.html
>> 
>> The sqlite3_sql and sqlite3_expanded_sql are always be available.
>> 
>> The sqlite3_normalized_sql interface is only available if the 
>> SQLITE_ENABLE_NORMALIZE compile option is used.
>> 
>> ---
>> 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 [mailto:sqlite-users-
>>> boun...@mailinglists.sqlite.org] On Behalf Of Joshua Thomas Wise
>>> Sent: Thursday, 14 March, 2019 15:40
>>> To: SQLite mailing list
>>> Subject: [sqlite] Missing docs
>>> 
>>> Nowhere in the current documentation does it mention the existence of
>>> the SQLITE_ENABLE_NORMALIZE compile-time option, and nowhere does it
>>> mention that sqldark_source_normalized() requires that option to be
>>> present.
>>> 
>>> The docs should probably mention this.
>>> 
>>> - Josh
>>> ___
>>> 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
> 
> 
> ___
> 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

Re: [sqlite] Missing docs

[Joshua Thomas Wise]

Yes sorry, it was a erroneous copy-paste. I was referring to 
sqlite3_normalized_sql().

As Keith said, it’s only available if the SQLITE_ENABLE_NORMALIZE compile 
option is used, but that is not documented anywhere.


> On Mar 15, 2019, at 3:56 AM, Keith Medcalf  wrote:
> 
> 
> These docs:
> 
> https://sqlite.org/c3ref/expanded_sql.html
> 
> The sqlite3_sql and sqlite3_expanded_sql are always be available.  
> 
> The sqlite3_normalized_sql interface is only available if the 
> SQLITE_ENABLE_NORMALIZE compile option is used.
> 
> ---
> 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 [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org] On Behalf Of Joshua Thomas Wise
>> Sent: Thursday, 14 March, 2019 15:40
>> To: SQLite mailing list
>> Subject: [sqlite] Missing docs
>> 
>> Nowhere in the current documentation does it mention the existence of
>> the SQLITE_ENABLE_NORMALIZE compile-time option, and nowhere does it
>> mention that sqldark_source_normalized() requires that option to be
>> present.
>> 
>> The docs should probably mention this.
>> 
>> - Josh
>> ___
>> 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] Missing docs

[Joshua Thomas Wise]

Nowhere in the current documentation does it mention the existence of the 
SQLITE_ENABLE_NORMALIZE compile-time option, and nowhere does it mention that 
sqldark_source_normalized() requires that option to be present.

The docs should probably mention this.

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

Re: [sqlite] missing SQLITE_DEFAULT_TEMP_CACHE_SIZE

[Joshua Thomas Wise]

On Mar 10, 2019, at 1:33 PM, Joshua Thomas Wise  
wrote:

Hello,

The documentation here 
<https://www.sqlite.org/tempfiles.html#other_temporary_file_optimizations> 
(https://www.sqlite.org/tempfiles.html#other_temporary_file_optimizations 
<https://www.sqlite.org/tempfiles.html#other_temporary_file_optimizations>) 
mentions that temporary tables and indexes each use their own page cache, which 
is sized by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time option. However, I 
can’t find any mention of that identifier anywhere in the sqlite3 source code. 
Additionally, that documentation conflicts with this documentation 
<https://www.sqlite.org/pragma.html#pragma_cache_size> 
(https://www.sqlite.org/pragma.html#pragma_cache_size 
<https://www.sqlite.org/pragma.html#pragma_cache_size>) which states that the 
TEMP database always has a cache size of 0. What’s the true story behind page 
caches of temporary files, and how can I control their sizes?

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

[sqlite] missing SQLITE_DEFAULT_TEMP_CACHE_SIZE

[Joshua Thomas Wise]

Hello,

The documentation here 
 
(https://www.sqlite.org/tempfiles.html#other_temporary_file_optimizations 
) 
mentions that temporary tables and indexes each use their own page cache, which 
is sized by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time option. However, I 
can’t find any mention of that identifier anywhere in the sqlite3 source code. 
Additionally, that documentation conflicts with this documentation 
 
(https://www.sqlite.org/pragma.html#pragma_cache_size 
) which states that the 
TEMP database always has a cache size of 0. What’s the true story behind page 
caches of temporary files, and how can I control their sizes?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation is lying to me

[Joshua Thomas Wise]

I’ve updated SQLite3 to version 3.27.2, and the previous error has gone away. 
However, the docs are still lying, because information_schema.tables cannot be 
implemented (evidence below). It seems that we need a pragma_table_list() 
function, which takes a schema as a parameter (similar to pragma_table_info(), 
pragma_index_list(), etc.)

Joshuas-MBP:sqldark josh$ sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ATTACH ':memory:' AS 'information_schema';
sqlite> CREATE VIEW information_schema.tables(table_name) AS
   ...> SELECT name FROM main.sqlite_master
   ...> WHERE type = 'table';
Error: view tables cannot reference objects in database main



> On Feb 27, 2019, at 2:48 PM, Joshua Wise  wrote:
> 
> Ahh that’s very interesting. I suspect this is the relevant entry in the 
> release notes:
> 
> SQLite Release 3.26.0 On 2018-12-01
> Enhanced triggers so that they can use table-valued functions that exist in 
> schemas other than the schema where the trigger is defined.
> 
> Although, we’re talking about views here, not triggers.
> 
> 
>> On Feb 27, 2019, at 2:43 PM, David Raymond > <mailto:david.raym...@tomtom.com>> wrote:
>> 
>> For me it gives an error up through 3.25.3 and starts working at 3.26.0
>> 
>> 
>> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org 
>> <mailto:sqlite-users-boun...@mailinglists.sqlite.org>] On Behalf Of Joshua 
>> Wise
>> Sent: Wednesday, February 27, 2019 2:13 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Documentation is lying to me
>> 
>> I can confirm that it also doesn’t work with SQLite version 3.24.0 on a 
>> completely different machine.
>> 
>>> On Feb 27, 2019, at 9:23 AM, Joshua Thomas Wise >> <mailto:joshuathomasw...@gmail.com>> wrote:
>>> 
>>> Joshuas-MBP:sqldark josh$ sqlite3
>>> SQLite version 3.25.0 2018-09-15 04:01:47
>>> Enter ".help" for usage hints.
>>> Connected to a transient in-memory database.
>>> Use ".open FILENAME" to reopen on a persistent database.
>>> sqlite> SELECT sqlite_version();
>>> 3.25.0
>>> sqlite> ATTACH ':memory:' AS 'information_schema';
>>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>>>   ...> SELECT name FROM pragma_database_list();
>>> sqlite> select * from schemata;
>>> Error: no such table: information_schema.pragma_database_list
>>> sqlite> select * from pragma_database_list();
>>> 0|main|
>>> 2|information_schema|
>>> 
>>> It worked when using `pragma_database_list()` directly, but not from inside 
>>> the view.
>>> 
>>> 
>>> 
>>>> On Feb 26, 2019, at 9:26 PM, Keith Medcalf >>> <mailto:kmedc...@dessus.com> <mailto:kmedc...@dessus.com 
>>>> <mailto:kmedc...@dessus.com>>> wrote:
>>>> 
>>>> 
>>>> Unable to reproduce:
>>>> 
>>>>> sqlite
>>>> SQLite version 3.28.0 2019-02-25 18:43:54
>>>> Enter ".help" for usage hints.
>>>> Connected to a transient in-memory database.
>>>> Use ".open FILENAME" to reopen on a persistent database.
>>>> sqlite> ATTACH ':memory:' AS 'information_schema';
>>>> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>>>>  ...> SELECT name FROM pragma_database_list();
>>>> sqlite>
>>>> sqlite> select * from schemata;
>>>> main
>>>> information_schema
>>>> sqlite> .exit
>>>> 
>>>> What version of SQLite are you using?
>>>> What do the following commands do?
>>>> 
>>>> pragma database_list;
>>>> 
>>>> select * from pragma_database_list();
>>>> 
>>>> 
>>>> ---
>>>> 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 [mailto:sqlite-users-
>>>>> boun...@mailinglists.sqlite.org <mailto:boun...@mailinglists.sqlite.org> 
>>>>> <mailto:boun...@mailinglists.sqlite.org 
>>>>> <mailto:boun...@mailinglists.sqlit

Re: [sqlite] Documentation is lying to me

[Joshua Thomas Wise]

Joshuas-MBP:sqldark josh$ sqlite3
SQLite version 3.25.0 2018-09-15 04:01:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT sqlite_version();
3.25.0
sqlite> ATTACH ':memory:' AS 'information_schema';
sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
   ...> SELECT name FROM pragma_database_list();
sqlite> select * from schemata;
Error: no such table: information_schema.pragma_database_list
sqlite> select * from pragma_database_list();
0|main|
2|information_schema|

It worked when using `pragma_database_list()` directly, but not from inside the 
view.



> On Feb 26, 2019, at 9:26 PM, Keith Medcalf  wrote:
> 
> 
> Unable to reproduce:
> 
>> sqlite
> SQLite version 3.28.0 2019-02-25 18:43:54
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> ATTACH ':memory:' AS 'information_schema';
> sqlite> CREATE VIEW information_schema.schemata(schema_name) AS
>   ...> SELECT name FROM pragma_database_list();
> sqlite>
> sqlite> select * from schemata;
> main
> information_schema
> sqlite> .exit
> 
> What version of SQLite are you using?
> What do the following commands do?
> 
> pragma database_list;
> 
> select * from pragma_database_list();
> 
> 
> ---
> 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 [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org] On Behalf Of Joshua Thomas Wise
>> Sent: Tuesday, 26 February, 2019 19:06
>> To: sqlite-users@mailinglists.sqlite.org
>> Subject: [sqlite] Documentation is lying to me
>> 
>> In this SQLite3 documentation
>> (https://www.sqlite.org/pragma.html#pragfunc), it says that
>> information_schema could be implemented by doing something like this:
>> 
>> ATTACH ':memory:' AS 'information_schema';
>> CREATE VIEW information_schema.schemata(schema_name) AS
>> SELECT name FROM pragma_database_list();
>> 
>> However, when attempting to do this, we get an error: "no such table:
>> information_schema.pragma_database_list”.
>> ___
>> 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] Documentation is lying to me

[Joshua Thomas Wise]

In this SQLite3 documentation (https://www.sqlite.org/pragma.html#pragfunc), it 
says that information_schema could be implemented by doing something like this:

ATTACH ':memory:' AS 'information_schema';
CREATE VIEW information_schema.schemata(schema_name) AS
SELECT name FROM pragma_database_list();

However, when attempting to do this, we get an error: "no such table: 
information_schema.pragma_database_list”.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Joshua Thomas Wise]

I’ll interpret the silence as a “no”?


> On Feb 7, 2019, at 11:53 AM, Joshua Thomas Wise  
> wrote:
> 
> Is the SQLite team aware of these issues?
> 
> 
>> On Feb 4, 2019, at 7:00 AM, sqlite-users-requ...@mailinglists.sqlite.org 
>> <mailto:sqlite-users-requ...@mailinglists.sqlite.org> wrote:
>> 
>> Hello,
>> 
>> I found some behaviors that should probably be considered bugs (and should 
>> be fixed and/or documented).
>> 
>> Let’s start the explanation by observing some behavior that actually is 
>> correct and consistent. Below, we observe which type of action is reported 
>> by sqlite3_set_authorizer(), given some SQL input:
>> 
>> "CREATE TEMP TABLE foo(x)” -> SQLITE_CREATE_TEMP_TABLE
>> "CREATE TEMP VIEW foo AS SELECT 1” -> SQLITE_CREATE_TEMP_VIEW
>> "CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
>> SQLITE_CREATE_TEMP_TRIGGER
>> 
>> So far so good. But what happens when we use the “temp.foo” syntax instead 
>> of the TEMP keyword?
>> 
>> "CREATE TABLE temp.foo(x)" -> SQLITE_CREATE_TABLE
>> "CREATE INDEX temp.foo ON t(x)" -> SQLITE_CREATE_TEMP_INDEX
>> "CREATE VIEW temp.foo AS SELECT 1" -> SQLITE_CREATE_VIEW
>> "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
>> SQLITE_CREATE_TEMP_TRIGGER
>> 
>> Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW 
>> report their regular CREATE_* variants, while CREATE INDEX and CREATE 
>> TRIGGER do report their TEMP_* variants.
>> 
>> I recommend that either all or none of those statements should report their 
>> TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
>> made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
>> this case) should ideally be included as an argument to the 
>> sqlite3_set_authorizer() callback.
>> 
>> I also found strange inconsistencies regarding error messages. If we execute 
>> the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB 
>> compile-time option present, we get the following error messages 
>> (respectively):
>> 
>> 'SQL logic error'
>> 'cannot create a TEMP index on non-TEMP table “t”'
>> 'SQL logic error’
>> 'SQL logic error’
>> 
>> However, if we replace “temp.foo” with “miss.foo” in each of those 
>> statements, we get the much better error message:
>> 
>> 'unknown database miss’
>> 
>> All of the observations described in this email were very surprising to me. 
>> Hopefully they can be fixed and/or documented.
>> 
>> Best regards,
>> 
>> Josh
> 

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

Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Joshua Thomas Wise]

Is the SQLite team aware of these issues?


> On Feb 4, 2019, at 7:00 AM, sqlite-users-requ...@mailinglists.sqlite.org 
> wrote:
> 
> Hello,
> 
> I found some behaviors that should probably be considered bugs (and should be 
> fixed and/or documented).
> 
> Let’s start the explanation by observing some behavior that actually is 
> correct and consistent. Below, we observe which type of action is reported by 
> sqlite3_set_authorizer(), given some SQL input:
> 
> "CREATE TEMP TABLE foo(x)” -> SQLITE_CREATE_TEMP_TABLE
> "CREATE TEMP VIEW foo AS SELECT 1” -> SQLITE_CREATE_TEMP_VIEW
> "CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
> SQLITE_CREATE_TEMP_TRIGGER
> 
> So far so good. But what happens when we use the “temp.foo” syntax instead of 
> the TEMP keyword?
> 
> "CREATE TABLE temp.foo(x)" -> SQLITE_CREATE_TABLE
> "CREATE INDEX temp.foo ON t(x)" -> SQLITE_CREATE_TEMP_INDEX
> "CREATE VIEW temp.foo AS SELECT 1" -> SQLITE_CREATE_VIEW
> "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
> SQLITE_CREATE_TEMP_TRIGGER
> 
> Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW 
> report their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER 
> do report their TEMP_* variants.
> 
> I recommend that either all or none of those statements should report their 
> TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
> made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
> this case) should ideally be included as an argument to the 
> sqlite3_set_authorizer() callback.
> 
> I also found strange inconsistencies regarding error messages. If we execute 
> the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB 
> compile-time option present, we get the following error messages 
> (respectively):
> 
> 'SQL logic error'
> 'cannot create a TEMP index on non-TEMP table “t”'
> 'SQL logic error’
> 'SQL logic error’
> 
> However, if we replace “temp.foo” with “miss.foo” in each of those 
> statements, we get the much better error message:
> 
> 'unknown database miss’
> 
> All of the observations described in this email were very surprising to me. 
> Hopefully they can be fixed and/or documented.
> 
> Best regards,
> 
> Josh

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

Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Joshua Thomas Wise]

> On Feb 4, 2019, at 7:00 AM, sqlite-users-requ...@mailinglists.sqlite.org 
> wrote:
> 
> For the last point, using the SQLITE_OMIT_TEMPDB option, did you compile from 
> the amalgamation or the full cannonical sources? According to 
> https://www.sqlite.org/compile.html#_options_to_omit_features 
> , many "OMIT" 
> options only work when the cannonical sources are used (my guess if compiling 
> the amalgamation is the parser still recognises TEMP as a keyword but there's 
> no code to implement it, hence the "logic error").


Rest assured, I compiled from the full canonical source. When using 
SQLITE_OMIT_TEMPDB, the “TEMP” keywords do result in syntax errors (as 
expected), but “temp.foo” schema names result in those cryptic error messages, 
rather than the expected "unknown database temp”.

I should also note that when using SQLITE_OMIT_TEMPDB, all four SQL statements 
previously mentioned will report the their normal variants to the 
sqlite3_set_authorizer() callback (i.e., not their TEMP_* variants), which is 
good and desired.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Joshua Thomas Wise]

Hello,

I found some behaviors that should probably be considered bugs (and should be 
fixed and/or documented).

Let’s start the explanation by observing some behavior that actually is correct 
and consistent. Below, we observe which type of action is reported by 
sqlite3_set_authorizer(), given some SQL input:

"CREATE TEMP TABLE foo(x)” -> SQLITE_CREATE_TEMP_TABLE
"CREATE TEMP VIEW foo AS SELECT 1” -> SQLITE_CREATE_TEMP_VIEW
"CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
SQLITE_CREATE_TEMP_TRIGGER

So far so good. But what happens when we use the “temp.foo” syntax instead of 
the TEMP keyword?

"CREATE TABLE temp.foo(x)" -> SQLITE_CREATE_TABLE
"CREATE INDEX temp.foo ON t(x)" -> SQLITE_CREATE_TEMP_INDEX
"CREATE VIEW temp.foo AS SELECT 1" -> SQLITE_CREATE_VIEW
"CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
SQLITE_CREATE_TEMP_TRIGGER

Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW report 
their regular CREATE_* variants, while CREATE INDEX and CREATE TRIGGER do 
report their TEMP_* variants.

I recommend that either all or none of those statements should report their 
TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
this case) should ideally be included as an argument to the 
sqlite3_set_authorizer() callback.

I also found strange inconsistencies regarding error messages. If we execute 
the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB compile-time 
option present, we get the following error messages (respectively):

'SQL logic error'
'cannot create a TEMP index on non-TEMP table “t”'
'SQL logic error’
'SQL logic error’

However, if we replace “temp.foo” with “miss.foo” in each of those statements, 
we get the much better error message:

'unknown database miss’

All of the observations described in this email were very surprising to me. 
Hopefully they can be fixed and/or documented.

Best regards,

Josh


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