Missing ORDER BY in .dump?
(1) By anonymous on 2024-01-21 20:20:20 [source]
Setting reverse_unordered_selects sometimes makes .dump output unusable. Minimum example:
$ sqlite3
SQLite version 3.45.0 2024-01-15 17:01:13
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view v as select 1;
sqlite> create trigger t instead of insert on v begin select 1; end;
sqlite> pragma reverse_unordered_selects = on;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TRIGGER t instead of insert on v begin select 1; end;
CREATE VIEW v as select 1;
COMMIT;
sqlite> ^D
The dump file attempts to recreate the trigger before its associated view, resulting in a "no such table" error.
I'm guessing there's a select somewhere in the implementation of .dump that's missing an order by clause.
(2) By Bo Lindbergh (_blgl_) on 2024-01-21 22:56:21 in reply to 1 [link] [source]
Indeed. In case you don't want to wait for an official fix:
Index: src/shell.c.in
==================================================================
--- src/shell.c.in
+++ src/shell.c.in
@@ -8373,11 +8373,12 @@
sqlite3_free(zSql);
if( (p->shellFlgs & SHFLG_DumpDataOnly)==0 ){
zSql = sqlite3_mprintf(
"SELECT sql FROM sqlite_schema AS o "
"WHERE (%s) AND sql NOT NULL"
- " AND type IN ('index','trigger','view')",
+ " AND type IN ('index','trigger','view')"
+ " ORDER BY type='trigger'",
zLike
);
run_table_dump_query(p, zSql);
sqlite3_free(zSql);
}
(3) By Larry Brasfield (larrybr) on 2024-01-22 02:30:19 in reply to 1 [link] [source]
While I agree that an ordering clause would be in order, my initial reaction to your post was to recall a short comic strip that I saw after unwrapping it from a piece of bubble-gum years ago. In the first panel, a guy is complaining to the doctor that when he hits his head with a hammer, it hurts. In the last panel, the doctor recommends, "Stop doing that."
This blemish is now removed on trunk in the repo.
(4) By Bo Lindbergh (_blgl_) on 2024-01-22 04:05:56 in reply to 3 [link] [source]
I reserve "Stop doing that." for people who create a table with columns named rowid, _rowid_, and oid and then complain that they can't access the real rowid.
(5) By anonymous on 2024-01-22 12:10:06 in reply to 3 [link] [source]
Okay, mea culpa. I should have added something like "Note: there's an obvious workaround". Of course, I should also have remembered that I'd set the pragma before spending time figuring out why my data wouldn't re-import. :-)
As to "blemish", however, that's your call, not mine. For .dump, yes, it's close to a noise-level bug report (maybe even worthy of fixing in documentation rather than by a code change), but I have no way of knowing where else this, or a similar, snippet of SQL is used. Like you, I'm reminded of something I once read: "and so many applications mistakenly come to depend on the arbitrary output order whatever that order happens to be".
(BTW: six hours from initial bug report to trunk commit. Quick work!)