I reproduced the difference in behavior using XP Pro, pre-compiled binary command-line-utility for 3.6.4.
Interestingly, placing another "plain word" at the END of the select resulted in the unaltered column text being selected as expected. Guess there's an advantage to "having the last word." ;-) sqlite> SELECT word, cast(word AS INTEGER), word FROM junk; 0|0|. 0|0|0 1|1|1 10|10|10 100|100|100 11|11|11 12|12|12 0|0|applied 0|0|appointments 0|0|assigned 0|0|axillary 0|0|B 0|0|back Also, modifying the original select to "CAST (WORD AS TEXT)" prevented the zero's. May be a problem. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brian Bender Sent: Thursday, November 06, 2008 8:58 AM To: sqlite-users@sqlite.org Subject: [sqlite] Bug in "cast" function? Hi all, I was using the results of the cast function to filter out numeric strings from a wider list of numeric and non-numeric strings ('... WHERE word = cast(word AS INTEGER)'. It works fine in v3.4.x, but in v3.6.4 it looks like the cast is messing up part of the results. Take a simple table of one string field, insert a few numeric strings and a few non-numeric strings: > BEGIN TRANSACTION; > CREATE TABLE junk (word TEXT); > INSERT INTO "junk" VALUES('.'); > INSERT INTO "junk" VALUES('0'); > INSERT INTO "junk" VALUES('1'); > INSERT INTO "junk" VALUES('10'); > INSERT INTO "junk" VALUES('100'); > INSERT INTO "junk" VALUES('11'); > INSERT INTO "junk" VALUES('12'); >> > INSERT INTO "junk" VALUES('applied'); > INSERT INTO "junk" VALUES('appointments'); INSERT INTO "junk" > VALUES('assigned'); INSERT INTO "junk" VALUES('axillary'); INSERT INTO > "junk" VALUES('B'); INSERT INTO "junk" VALUES('back'); COMMIT; If I 'SELECT word, cast(word AS INTEGER) FROM junk;' in 3.4, I get what I'd expect: > word cast(word AS INTEGER) > ---------- --------------------- > . 0 > 0 0 > 1 1 > 10 10 > 100 100 > 11 11 > 12 12 > applied 0 > appointmen 0 > assigned 0 > axillary 0 > B 0 > back 0 But if I do the same thing in 3.6, it looks like the cast also gets applied to my first field: > word cast(word AS INTEGER) > ---------- --------------------- > 0 0 > 0 0 > 1 1 > 10 10 > 100 100 > 11 11 > 12 12 > 0 0 > 0 0 > 0 0 > 0 0 > 0 0 > 0 0 Am I misusing the cast function? Or is this a bug that crept in between 3.4 and 3.6? (I think it worked correctly in 3.5 as well -- I can go back and try, if that helps) Thanks, -- Brian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
