4 ms·

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 th

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.

professoretc 5y ago
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?
- em500 5y ago
  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 https://www.sqlite.org/lang_keywords.html)
  - CRConrad 5y ago
    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.
    - professoretc 5y ago
      Just tried it with single-quotes and the output is the same.