Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages
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").Graham HoldenSent from my Samsung Galaxy S7 - powered by Three Original message From: Joshua Thomas Wise Date: 04/02/2019 03:18 (GMT+00:00) To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages 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_TRIGGERSo 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_TRIGGERUh-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 listsqlite-users@mailinglists.sqlite.orghttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Inconsistent behavior in sqlite3_set_authorizer() and error messages
From https://www.sqlite.org/c3ref/c_alter_table.html
"The 5th parameter to the authorizer callback is the name of the database
("main", "temp", etc.) if applicable."
Is that not the case in your tests?
I guess the difference between "CREATE TEMP TABLE x" and "CREATE TABLE temp.x"
ist hat the former already knows that the action is "create a temporary table"
before the table name is parsed, whereas the latter has to check the schema
name first. Note that you can do "CREATE TEMP TABLE temp.x".
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Joshua Thomas Wise
Gesendet: Montag, 04. Februar 2019 04:19
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Inconsistent behavior in sqlite3_set_authorizer()
and error messages
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
___
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] Inconsistent behavior in sqlite3_set_authorizer() and error messages
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] Min/Max and skip-scan optimizations
On 3 Feb 2019, at 9:52am, Gerlando Falauto wrote: > I do want them sorted, and I also want the whole (huge) dataset to be > processable without having to store it all in memory or temp files. > Sounds like the whole purpose of an index, doesn't it? > I do know SQL is all about the result, not how it's obtained, though. Absolutely. You should be fine. Try using the procedure in my previous message (the one which mentions ANALYZE) to find out the most useful index. Once you've found that, you can delete the other index. Then you've solved the problem and can move on. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Min/Max and skip-scan optimizations
Simon, Tim, Il sab 2 feb 2019, 23:40 Simon Slavin ha scritto: > On 2 Feb 2019, at 10:19pm, Gerlando Falauto > wrote: > > > Results should be naturally ordered by source1, source2,ts. > > [Sorry, I missed this the first time. Thanks, Tim.] > > Sorry, no. You're making assumptions about how SQLite works internally. > If you want your results sorted, ask for them sorted. If you don't, don't. > I do want them sorted, and I also want the whole (huge) dataset to be processable without having to store it all in memory or temp files. Sounds like the whole purpose of an index, doesn't it? I do know SQL is all about the result, not how it's obtained, though. > Note that if you don't ask for them sorted and they come out sorted by > good luck, there's no guarantee that they'll be sorted if you put different > data in the table, or the same data with the rows in a different order, or > if you update to a later version of SQLite. > I do realize that, that's why I'm not happy with that seems to do the trick without an explicit order by clause. Thank you for your help! Gerlando > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
