SQLite Forum

Missing ORDER BY in .dump?
Login

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