The following code is intended to change text values 'UND' into NULL values.
However, I see that string values different from 'UND' get corrupted. In the
example below the value 'ENG' is corrupted and the new value is a string of
three non-printable characters (ascii values 0x03 0x17 0x13) in the destination
table.
CREATE TABLE orig (name TEXT, l CHAR(3));
INSERT INTO "orig" VALUES('name1','ENG');
INSERT INTO "orig" VALUES('name2',NULL);
INSERT INTO "orig" VALUES('name3','UND');
CREATE TABLE dest (n text, l char(3));
-- The statement below messes up column l
INSERT INTO dest(n, l)
SELECT orig.name AS n,
CASE orig.l WHEN 'UND' THEN NULL
ELSE orig.l END AS l
FROM orig;
When inspecting table dest we see that the first record has a garbage value in
column l:
.dump dest
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE dest (n text, l char(3));
INSERT INTO "dest" VALUES('name1','???'); --- 'ENG'
expected, but has 3 non-printable chars 0x03 0x17 0x13
INSERT INTO "dest" VALUES('name2',NULL);
INSERT INTO "dest" VALUES('name3',NULL);
COMMIT;
Tested with sqlite versions 3.4.2 and 3.7.2. Occurs in both versions.
Do I miss something in my code or is this an sqlite bug?
Regards
Rob
PS: In the mean while I changed my code to use the nullif(l,'UND') function
instead of the above CASE..END construct. Nullif(l.'UND') works fine.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
