Hacker News Reader

Comment by mumblemumble | Hacker News Reader

I suppose I should mention that this was 15 years ago, so the behavior very well could have changed since then.

At the time, though, this was happening even though I had set the columns' type affinity to TEXT. That was the very first thing I checked when the bug rolled in.

View on HN

Just for curiosity's sake, I built SQLite 3.2.7 (from 2005-09-24) and 2.8.17 (bugfix on the old 2.8 line, from 2005-12-19) and tried this in the shell in both:

    sqlite> create table test_tbl ( test TEXT );
    sqlite> insert into test_tbl values ( "0123" );
    sqlite> select * from test_tbl;

Both give the same output: 0123. Were you using an API wrapper around SQLite that was doing its own coercions, perhaps?

Thanks for taking the trouble. Small syntax nitpick: in ANSI SQL, double quotes are used for identifiers (such as table or column names), single quotes for string literals ('01234'). Most databases (including sqlite) are a bit more permissive, but it's better to stick to the rules (https://www.sqlite.org/lang_keywords.html)

Could it even be that the double quotes around values are interpreted as some kind of "strong quotes", where single quotes wouldn't? So, inserting '0123' gets coerced to integer 123, whereas "0123" in doubles is interpreted as "No really, I mean it, this is a string!" and therefore keeps the leading zero?

Just a WAG.

Just tried it with single-quotes and the output is the same.

Reply on news.ycombinator.com