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.