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

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

[Graham Holden]

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

[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