SQLite User Forum

sqldiff: SQL statement error
Login

sqldiff: SQL statement error

(1) By Tim van der Molen (tbvdmolen) on 2023-05-27 18:55:58 [source]

sqldiff sometimes produces invalid SQL to compare two tables. Here is an example:

$ sqlite3 db1 'CREATE TABLE t(a INTEGER PRIMARY KEY)'
$ sqlite3 db2 'CREATE TABLE t(a INTEGER PRIMARY KEY, b)'
$ sqldiff db1 db2
ALTER TABLE t ADD COLUMN b;
sqldiff: SQL statement error: near ".": syntax error
"SELECT B.a, 1 -- changed row
       B.b IS NOT NULL, B.b
  FROM main.t A, aux.t B
 WHERE A.a=B.a
   AND (B.b IS NOT NULL)
 UNION ALL
SELECT A.a, 2 -- deleted row
       NULL, NULL
  FROM main.t A
 WHERE NOT EXISTS(SELECT 1 FROM aux.t B
                   WHERE A.a=B.a)
 UNION ALL
SELECT B.a, 3 -- inserted row
       1, B.b
  FROM aux.t B
 WHERE NOT EXISTS(SELECT 1 FROM main.t A
                   WHERE A.a=B.a)
 ORDER BY 1;
"

Note that in each SELECT statement the second column is not followed by a comma.

The following diff seems to fix it:

diff --git a/tool/sqldiff.c b/tool/sqldiff.c
index 0a017037c..519b55c24 100644
--- a/tool/sqldiff.c
+++ b/tool/sqldiff.c
@@ -605,7 +605,7 @@ static void diff_one_table(const char *zTab, FILE *out){
       strPrintf(&sql, "%sB.%s", zSep, az[i]);
       zSep = ", ";
     }
-    strPrintf(&sql, ", 1%s -- changed row\n", nPk==n ? "" : ",");
+    strPrintf(&sql, ", 1%s -- changed row\n", nPk2==n2 ? "" : ",");
     while( az[i] ){
       strPrintf(&sql, "       A.%s IS NOT B.%s, B.%s%s\n",
                 az[i], az2[i], az2[i], az2[i+1]==0 ? "" : ",");
@@ -642,7 +642,7 @@ static void diff_one_table(const char *zTab, FILE *out){
     strPrintf(&sql, "%sA.%s", zSep, az[i]);
     zSep = ", ";
   }
-  strPrintf(&sql, ", 2%s -- deleted row\n", nPk==n ? "" : ",");
+  strPrintf(&sql, ", 2%s -- deleted row\n", nPk2==n2 ? "" : ",");
   while( az2[i] ){
     strPrintf(&sql, "       NULL, NULL%s\n", i==n2-1 ? "" : ",");
     i++;
@@ -660,7 +660,7 @@ static void diff_one_table(const char *zTab, FILE *out){
     strPrintf(&sql, "%sB.%s", zSep, az[i]);
     zSep = ", ";
   }
-  strPrintf(&sql, ", 3%s -- inserted row\n", nPk==n ? "" : ",");
+  strPrintf(&sql, ", 3%s -- inserted row\n", nPk2==n2 ? "" : ",");
   while( az2[i] ){
     strPrintf(&sql, "       1, B.%s%s\n", az2[i], az2[i+1]==0 ? "" : ",");
     i++;

(2) By Larry Brasfield (larrybr) on 2023-05-27 20:32:54 in reply to 1 [link] [source]

Richard has fixed this on the trunk branch.