SQLite Forum

Timeline
Login

21 forum posts by user rparkins

2021-03-21
22:12 Post: When is SELECT ... FROM with no table-or-subquery valid. (artifact: 9ed02582fe user: rparkins)

The syntax diagram for SELECT allows a FROM not followed by any table-or-subquery. The Determination of input data (FROM clause processing) section of the description says "If a FROM clause is specified, the data on which a simple SELECT query operates comes from the one or more tables or subqueries". What happens if there are zero tables or subqueries as apparently permitted by the syntax diagram?

21:54 Reply: Help getting excel/csv file into SQLite? please help (artifact: b7a88fd7fc user: rparkins)

sqliteman will also import excel files for you, if you are on Linux or you are prepared to build it for your operating system from the sources. It gives you a few more choices for formats, although I haven't tested the MS Excel XML format since I forked it from pvanek's version. The CSV-like format certainly works. Somewhere I have code to import the Psion database format as well, although it isn't in the published sources.

2021-03-10
14:11 Post: checkpointing 9th attached database checkpoints all databases (artifact: a006d86f72 user: rparkins)

The problem is in the interface to sqlite3Checkpoint(), where the value SQLITE_MAX_ATTACHED for the argument iDb is used to indicate that all databases are to be checkpointed.

Presumably the assumption of the writer of this code was that index into the array aDb in a struct sqlite3 must be less than SQLITE_MAX_ATTACHED. Unfortunately this is incorrect.

As stated in the comment on the definition of struct Db, aDb[0] and aDb[1] are reserved for the main and temp databases respectively, and SQLITE_MAX_ATTACHED further databases can be attached, making the maximum possible index SQLITE_MAX_ATTACHED+1.

I think that line 103198
assert( iDb>=0 && iDb<SQLITE_MAX_ATTACHED );
is a bit dubious as well. sqlite3SchemaToIndex() can validly return SQLITE_MAX_ATTACHED or SQLITE_MAX_ATTACHED+1 as noted above.

This is a script that demonstrates the problem (assuming that sqlite is compiled with the default SQLITE_MAX_ATTACHED of 10):- gdb sqlite3 start break sqlite3.c:166381 c .open main.sqlite ATTACH 'attach1.sqlite' AS 'attach1'; ATTACH 'attach2.sqlite' AS 'attach2'; ATTACH 'attach3.sqlite' AS 'attach3'; ATTACH 'attach4.sqlite' AS 'attach4'; ATTACH 'attach5.sqlite' AS 'attach5'; ATTACH 'attach6.sqlite' AS 'attach6'; ATTACH 'attach7.sqlite' AS 'attach7'; ATTACH 'attach8.sqlite' AS 'attach8'; ATTACH 'attach9.sqlite' AS 'attach9'; PRAGMA attach8.wal_checkpoint; -- checkpoints one database display i display db->aDb[i].zDbSName c PRAGMA attach9.wal_checkpoint; -- checkpoints all of them c c c c c c c c c c c .quit q This is suggested diff that fixes the problem. It isn't the simplest possible patch, but it has in my opinion better maintainability because any change to the set of reserved slots in the aDb array is propagated to all places in the code that are affected.

    16740,16741c16740,16741
    < ** aDb[1] is the database file used to hold temporary tables.  Additional
    < ** databases may be attached.
    ---
    > ** aDb[1] is the database file used to hold temporary tables.
    > ** SQLITE_LIMIT_ATTACHED additional databases may be attached.
    16742a16743
    > #define SQLITE_MAX_DATABASES (SQLITE_MAX_ATTACHED + 2)
    103198c103199
    <     assert( iDb>=0 && iDb<SQLITE_MAX_ATTACHED );
    ---
    >     assert( iDb>=0 && iDb<SQLITE_MAX_DATABASES );
    116369c116370
    <   assert( iDb<SQLITE_MAX_ATTACHED+2 );
    ---
    >   assert( iDb<SQLITE_MAX_DATABASES);
    129425c129426
    <     int iBt = (pId2->z?iDb:SQLITE_MAX_ATTACHED);
    ---
    >     int iBt = (pId2->z?iDb:SQLITE_MAX_DATABASES);
    166292c166293
    <   int iDb = SQLITE_MAX_ATTACHED;  /* sqlite3.aDb[] index of db to checkpoint */
    ---
    >   int iDb = SQLITE_MAX_DATABASES; /* sqlite3.aDb[] index of db to checkpoint */
    166363c166364
    < ** If iDb is passed SQLITE_MAX_ATTACHED, then all attached databases are
    ---
    > ** If iDb is passed SQLITE_MAX_DATABASES, then all attached databases are
    166380c166381
    <     if( i==iDb || iDb==SQLITE_MAX_ATTACHED ){
    ---
    >     if( i==iDb || iDb==SQLITE_MAX_DATABASES ){

2021-02-18
14:42 Reply: CASE WHEN False THEN MyFunction() END calls MyFunction (artifact: f331612206 user: rparkins)

Does ths answer imply a guarantee of the order of evaluation of a CASE expression?

For example if I write

CASE WHEN MyFunction(1) THEN 'one' WHEN MyFunction(2) THEN 'two' ELSE 'neither' END

is it guaranteed that if the SQLITE_DETERMINISTIC flag is not set and MyFunction (1) returns true, then MyFunction(2) will not be called?

MyFunction() has side-effects, and I can't use two separate statements because the CASE expression is inside an UPDATE statement over a whole table.

2021-02-08
06:20 Reply: "expr" syntax diagram differs from behaviour (artifact: 0d8528ab26 user: rparkins)

Sorry about that, you're right. I didn't spot the bypass above select-stmt.

06:17 Reply: Apparent bug in ALTER TABLE (artifact: 2cba536d44 user: rparkins)

The question of using a transaction is already discussed in my comment. It is not possible to enclose all 12 steps in a transaction, because step #1

PRAGMA foreign_keys = off

is a no-op within a transaction as documented at https://www.sqlite.org/pragma.html#pragma_foreign_keys.

05:39 Reply: Apparent bug in ALTER TABLE (artifact: 197284d085 user: rparkins)

My improvement suggestion would be that the check whether any view references a non-existent table, and the error if one does, should simply be removed from ALTER TABLE. I can't see any benefit from it at all. The existence of such a view does not, as far as I can see, prevent ALTER TABLE from working as specified. Nobody in this thread has yet offered any explanation of why the check needs to be there.

If it is desired to have such a check available somewhere (and I can see reasons why it might be wanted), it should be implemented independently from ALTER TABLE, perhaps by a PRAGMA as I suggested.

I can't see why people would be likely to object to this. The check was not in the legacy ALTER TABLE, and its existence in the post 3.26 ALTER TABLE appears to be undocumented, so normal sqlite users would not be relying on it.

I have not suggested any change to the legacy behaviour as implied by Larry. The eheck did not exist then, or at least if it was done internally it did not provoke any error.

2021-01-28
19:04 Post: Grammatical error in CREATE TABLE documentation (artifact: 95a7096c25 user: rparkins)

On page https://www.sqlite.org/lang_createtable.html, the text Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. should be Hence, it has been decided to merely document the fact that SQLite allows NULLs in most PRIMARY KEY columns.

It's a minor grammatical error, and I would offer to correct it for you, but I can't see where the sources for the documentation live.

15:37 Reply: Apparent bug in ALTER TABLE (artifact: 8baf296bb3 user: rparkins)

IMHO this is definitely a bug.

It introduced an unnecessary and undocumented interaction between processes working in entirely different parts of the database. Suppose for example that process A is doing the sequence described in section 5 of the ALTER TABLE documentation on page https://www.sqlite.org/lang_altertable.html. Between steps 6 and 7 any view that referenced table X now refers to a nonexistent table. If process B (perhaps belonging to a different user) attempts to do an ALTER TABLE in another part of the database at this point, it will get an error.

In particular if process A is actually being driven by a human running through the sequence (rather than a program), there may be quite a long time between steps 6 and 7.

Of course it is possible for process A to use an EXCLUSIVE transaction, but this is not required and would adversely affect throughput since it prevents other database connections from reading the database if not in WAL mode. It would also be possible for process A to drop all views that reference table X before doing step 6, but this is not specified, and the possibility does not change the fact that this is a perfectly valid sequence of sqlite statements which, after the change to ALTER TABLE, has an unexpected effect on another otherwise valid statement.

Why was it considered necessary to do this test? Such a test is not performed for DROP TABLE (where it might be more logical) or CREATE TABLE. If a test was needed for the existence of views referencing nonexistent tables, why not make a PRAGMA view_check by analogy with PRAGMA foreign_key_check instead of introducing it into ALTER TABLE where IMHO it does not belong? Of course ALTER TABLE has to scan the schema in order to detect whether a table or a view with the new name already exists, but it only needs to fail if a table or a view with the new name does already exist.

Incidentally dan's comment is not quite correct. The error is only generated if the ALTER TABLE statement renames a column or a table, not if it adds a column.

For example:-

-- Script started

PRAGMA legacy_alter_table = 0 ;

-- No error

--

DROP TABLE IF EXISTS table1 ;

-- No error

--

DROP TABLE IF EXISTS table2 ;

-- No error

--

CREATE TABLE table1 (t1column1 t1column2) ;

-- No error

--

CREATE TABLE table2 (t2column1 t2column2) ;

-- No error

--

CREATE VIEW view1 AS SELECT t1column1 FROM table1 ;

-- No error

--

DROP TABLE table1 ;

-- No error

--

ALTER TABLE table2 ADD COLUMN newcolumn3 ;

-- No error

--

ALTER TABLE table2 RENAME COLUMN newcolumn3 TO t2column3 ;

-- Error: error in view view1: no such table: main.table1 Unable to fetch row.

14:36 Post: "expr" syntax diagram differs from behaviour (artifact: e9572bfaa2 user: rparkins)

The syntax diagram for expressions at https://www.sqlite.org/lang_expr.html shows that if IN is followed by a list of expressions (rather than any of the other options allowed) the list must be nonempty: it could just as easily have allowed an empty list as for a function argument list.

In fact the actual behaviour of sqlite 3.28.0 is to accept an empty list, and the value of expression IN () is zero, since whatever the expression is, it isn't in the list.

It isn't a major problem, but either the syntax diagram or the code is wrong.

2021-01-23
20:07 Reply: Cannot change my email settings (artifact: 839a8a596a user: rparkins)

Other forums that I use allow me to request email notifications of replies to any thread that I have posted to or replied to. For me, and possibly many others, this would be far more useful than either notifications for, or a digest of, posts or replies to all threads in the forum.

The forum does not need to know which posts I have read to do this: it only needs to know which threads I have posted or replied to, which can be found by a search.

2021-01-22
12:47 Reply: DROP VIEW IF EXISTS failure (artifact: b0442ba1da user: rparkins)

I have several comments here:-

First, is there any documentation of this "eval" function? I can't find anything by searching on the sqlite web site. I wrote a user-defined function which can be used in a script to evaluate a possibly dynamically constructed SQL statement, and return any error message as a text string, which solves the OP's problem for me: I don't know if "eval" would have helped me with that.

Second, if tables and views share a namespace, the documentation doesn't appear to mention this.

Third, if tables and views share a namesapce, IMHO it ought to be possible to delete an object in that namespace without needing to know whether it is a table or a view. The current implementation does not allow this.

Fourth, I described what I believe is a perfectly reasonable use case in another post at https://sqlite.org/forum/forumpost/53a2dec184. I maintain a program which has a UI which allows a user to do various useful things on more or less any sqlite database: it examines the schema to find out which objects exist. I have a bunch of test scripts (the program can load and run a script of SQL statements), which of course I run on a test database. A failed test can leave a view or a table still existing. I don't want to have to clean up by hand after errors, so I want my test scripts to DROP any views or tables that they will create. So I want to start off with

DROP VIEW IF EXISTS xxx ; DROP TABLE IF EXISTS xxx ;

for each table or view xxx that the script is subsequently going to create.

I fell over the same issue as the OP in this post.

The solution that works for me is

SELECT exec(NULL, 'DROP VIEW IF EXISTS xxx') ; SELECT exec(NULL, 'DROP TABLE IF EXISTS xxx') ;

where "exec" is the user-defined function that I mentioned above. If the first argument is NULL, it just ignores any error.

Incidentally the program I mentioned above is a valid example of a database using program which does not know what objects exist in the database, except of course by examining the schema.

12:09 Reply: DROP VIEW IF EXISTS fails if a TABLE of that name exists, and vice versa (artifact: 53a2dec184 user: rparkins)

Thanks for the link. I will post in that thread.

2021-01-16
22:30 Post: DROP VIEW IF EXISTS fails if a TABLE of that name exists, and vice versa (artifact: 7c87a63519 user: rparkins)

-- Script started

DROP TABLE IF EXISTS "xxx" ;

-- No error

DROP VIEW IF EXISTS "xxx" ;

-- No error

DROP TABLE IF EXISTS "yyy" ;

-- No error

DROP VIEW IF EXISTS "yyy" ;

-- No error

CREATE TABLE "xxx" (col1, col2, col3) ;

-- No error

CREATE VIEW "yyy" AS SELECT * FROM "xxx" ;

-- No error

DROP VIEW IF EXISTS "xxx" ;

-- Error: use DROP TABLE to delete table xxx Unable to execute statement.

DROP TABLE IF EXISTS "yyy" ;

-- Error: use DROP VIEW to delete view yyy Unable to execute statement.

I don't think that either of these error messages is justified. DROP VIEW IF EXISTS should succeed if no view of that name exists.

https://www.sqlite.org/lang_naming.html says "When searching database schemas for a named object, objects of types that cannot be used in the context of the reference are always ignored." A DROP VIEW statement drops a view, and a table name cannot logically be used there.

If I try to create a view and a table of that name exists, it will fail, and it I try to create a table and a view of that name exists, it will also fail. IMHO it's perfectly reasonable to DROP either of them which exists as I do at the start of the script. This is part of a set of tests which create lots of tables and views, and I really don't think that I should have to remember which of them I have created earlier.

Should you argue that at the end of each test I can drop any views and tables that I have created, I can't do that in the script because I need to look at the result of the test first.

20:19 Reply: Apparent bug in ALTER TABLE (artifact: 15ce0122c2 user: rparkins)

Well, in that case the documentation is incomplete, since the description of ALTER TABLE contains no such statement.

I can understand why the implementation of the new version of ALTER TABLE needs to scan the schema to check if any other tables or views need to be updated, but the behaviour that Dan has described is not necessary to implement the new version. The legacy version scanned the schema too in the case where a column is renamed. A schema containing a view with a missing reference is valid, even if a little perverse, and might for example exist if another user is performing the sequence of actions described for making changes to a table which ALTER TABLE can't do. I found this issue when testing an application which does exactly that.

2021-01-04
12:11 Post: Apparent bug in ALTER TABLE (artifact: 2976d4cb91 user: rparkins)

If a view exists on a table which does not exist, and PRAGMA LEGACY_ALTER_TABLE is currently 0, and an attempt is made to rename another table to be the one which the view refers to, sqlite reports an error.

To reproduce, run this sequence of SQL statements:-
CREATE TABLE main (column1, column2) ; CREATE VIEW sorted AS SELECT * FROM main ORDER BY column1 ; PRAGMA LEGACY_ALTER_TABLE = 1 ; ALTER TABLE main RENAME TO other ; PRAGMA LEGACY_ALTER_TABLE = 0 ; ALTER TABLE other RENAME TO main ;

The last statement reports Error: error in view sorted: no such table: main.main Unable to fetch row.

IMHO there is no error here: the statement is attempting to create the missing table, not to remove it.

Found using Sqlite: 3.28.0
Script run using github.com/rparkins999/sqliteman
OS: openSUSE Leap 15.1 Linux 4.12.14-lp151.28.87-default

2020-12-16
11:08 Post: Save and restore a PRAGMA state (artifact: 43e7e52893 user: rparkins)

Is it possible to save and restore the state of a PRAGMA in an SQL script?

Obviously one can do it in code, but a pragma_value has to be a literal, which seems to prevent me from doing it in a script.

The only solution that I can think of is to create a user-defined function which executes a PRAGMA with a value passed to the function, but it seems unfortunate that this is not a built-in.

Should you say that this is no different from doing it in code, the reason why it is different is that I have a program that presents a UI allowing the user to create, edit, and execute SQL statements and scripts which can be saved in or read from files: the UI can also create some simple types of statement for the user and directly edit or search a table.

My user might want to create a script which only runs correctly if some PRAGMA has a particular value (for example PRAGMA legacy_alter_table may need to be true), but does not permanently change the value of the PRAGMA.

Providing a built-in function in my program would enable users to do this without having to write code. The SQL script to do this is a bit tricky, but looks something like this, where set_pragma is the user-defined function:-

CREATE TABLE temp.save_pragma_legacy_table AS
    SELECT * FROM pragma_legacy_alter_table() ;

body of script

SELECT * FROM set_pragma('pragma_legacy_table', 
    SELECT * FROM temp.save_pragma_legacy_table) ;
DROP TABLE temp.save_pragma_legacy_table ;
2020-12-12
20:09 Reply: Reference to RECURSIVE table column apparently not allowed in nested SELECT (artifact: c29da5eb31 user: rparkins)

Sorry, my response was a bit hasty. I see now that cnt.x (a column reference as opposed to a reference to the whole table) should be allowed within the scope of a SELECT ... FROM cnt ....

However I was (I think quite reasonably) expecting that inside a recursive SELECT cnt.x should refer to the content of the currently active row in column cnt.x (see The basic algorithm 2.c) and not the whole column as you suggest. This does not seem to work inside a subquery.

I still think that there is a problem here, and in any case the error message given (no such column) is entirely unhelpful as other posters have suggested.

Of course my example is wrong anyway because, even if cnt.x was valid, when it is >1 the result of the subquery is a vector where a scalar value is required. The original intention was LIMIT 1 OFFSET cnt.x which delivers a scalar value. I was simplifying my original code in order to avoid an overly large post and I overdid it.

BTW I'm using sqlite 3.28.0, the latest version available as a prebuilt library on my Linix distro (OpenSUSE Leap 14.1).

I was somewhat struggling as this was my first attempt at a recursive select. I was eventually able to do what I wanted (extract lists of items from a table which contained items with pointers to next and previous ones).

The key was realising by looking at the Alice example that I could SELECT from more than one table at a time. This is of course allowed by the SELECT syntax diagram, but I had never had occasion to use it before: it avoids the troublesome subquery.

2020-12-11
13:45 Reply: Reference to RECURSIVE table column apparently not allowed in nested SELECT (artifact: 1a72094ad0 user: rparkins)

Well, if "The recursive table" includes references to columns in it, then this rule is broken in the first example given in section 3.1

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
SELECT x FROM cnt;
where "x" appears twice outside the FROM clause

If I remove the "cnt." from my example

WITH RECURSIVE
  cnt ( x , list , done , Pagelist ) AS (
    VALUES ( 1 , 1 , 0 , '' ) UNION ALL
    SELECT x + 1 AS x ,
    ( list || ',' || ( cnt.x + 1 ) ) AS list,
    ( x == 9 ) AS done ,
    ( SELECT Page FROM GetreuerMusicMeister LIMIT x ) AS Pagelist
    FROM cnt WHERE x < 10
  )
SELECT list , Pagelist FROM cnt WHERE done == 1 ;
it still doesn't work, but the error message refers to "x" rather than "cnt.x".

So the rule seems to be applied more strictly in subqueries.

12:33 Post: Reference to RECURSIVE table column apparently not allowed in nested SELECT (artifact: d579daaccb user: rparkins)

The following statement works as expected

WITH RECURSIVE
  cnt ( x , list , done , Pagelist ) AS (
    VALUES ( 1 , 1 , 0 , '' ) UNION ALL
    SELECT x + 1 AS x ,
    ( list || ',' || ( cnt.x + 1 ) ) AS list,
    ( x == 9 ) AS done ,
    ( SELECT Page FROM GetreuerMusicMeister LIMIT 1 ) AS Pagelist
    FROM cnt WHERE x < 10
  )
SELECT list , Pagelist FROM cnt WHERE done == 1 ;

and produces a single row with fields '1,2,3,4,5,6,7,8,9,10' and a null (which happens to be the Page field of the first record of GetreuerMusicMeister). The following statement

WITH RECURSIVE
  cnt ( x , list , done , Pagelist ) AS (
    VALUES ( 1 , 1 , 0 , '' ) UNION ALL
    SELECT x + 1 AS x ,
    ( list || ',' || ( cnt.x + 1 ) ) AS list,
    ( x == 9 ) AS done ,
    ( SELECT Page FROM GetreuerMusicMeister LIMIT cnt.x ) AS Pagelist
    FROM cnt WHERE x < 10
  )
SELECT list , Pagelist FROM cnt WHERE done == 1 ;
fails with the error no such column: cnt.x Unable to execute statement

It looks as if the table "cnt" is somehow no longer visible inside the nested SELECT statement used as an expression.

2020-05-21
10:26 Post: Feature Request: Allow overwriting existing table with new one (artifact: 1e872675fe user: rparkins)
Currently the procedure for altering a table in ways that the ALTER TABLE statement doesn't directly support is very complicated. I would like to be able to say:-

ALTER TABLE <name> AS <select-statement>

where the select-statement can reference the original table, although it doesn't have to. Alternatively this could be done with an option to CREATE TABLE allowing overwrite of an existing table.

This should not be too difficult to implement. Essentially you make a new table, and atomically rename it over the old one, like "mv" in Unix/Linux. Handling triggers in the table or views and foreign keys that reference the original table is a bit tricky, but it may be good enough to support the legacy_alter_table behaviour. It would certainly be good enough for me.

Rationale:

I maintain an sqlite database manipulating program at https://github.com/rparkins999/sqliteman. It was originally a fork from https://github.com/pvanek/sqliteman, but I have fixed many bugs and added new features. It supports a GUI for an extended version of ALTER TABLE which allows deleting and reordering columns as well as adding and renaming them. I do it by a similar sequence to the twelve steps in the ALTER TABLE documentation, but I don't currently quite get it right because I don't handle triggers in the table. I handle indices, views, and foreign keys pointing into the table by using

PRAGMA legacy_alter_table = ON;

but this doesn't work if a referenced column is renamed or deleted. I have plans to do it properly one day, but it's a lot of work for a feature that I don't use myself.


I also like to keep my tables sorted. I know I can look at a sorted view, but I can't edit the rows in it without some messy trickery to force any changes that I try to make in the sorted view to actually occur in the real table. Since my databases are all small enough to fit in the (quite large) memory of a modern PC, it's easier to keep my tables sorted. To re-sort I do something like

PRAGMA legacy_alter_table = ON;
create table main__sorted as select * from sorted ; drop table main ;
alter table main__sorted rename to main;

The PRAGMA is needed to stop post-3.25 version of sqlite from throwing away the sorted view when the original table is dropped. Of course this doesn't handle triggers, but I don't use them.

Anyway it would be much nicer to be able to say

ALTER TABLE MAIN AS SELECT * FROM SORTED;