4 ms·

Basically, if it doesn't look like an INT, it won't be coerced into it. The edge case I hit was when it did look like an integer, but had leading zeroes that w

Basically, if it doesn't look like an INT, it won't be coerced into it. The edge case I hit was when it did look like an integer, but had leading zeroes that were important: sqlite> create table example(a int, b text); sqlite> insert into example values ('00123', '00123'); sqlite> select * from example; 123|00123 My fault for a bunch of reasons, but still, surprised me.

usmannk 5y ago
This is a classic mixup in Excel/spreadsheet tools too, as they'll often assume any pasted numerical IDs are ints and "helpfully" strip the leading zeroes.
asddubs 5y ago
well, it sure is one hell of a footgun
em500 5y ago
I'm surprised that sqlite is singled out for this, AFAIK this is standard RDBMS behavior. Your example gives exactly the same results in mysql.
- banana_giraffe 5y ago
  Sure, but I think what throws people for a loop is that in SQLite, you can store text in that column: sqlite> create table example(a int); sqlite> insert into example values('00123'); sqlite> insert into example values('abcdef'); sqlite> select * from example; +--------+ | a | +--------+ | 123 | | abcdef | +--------+ Not to mention, "create table example(a)" is perfectly fine, and then the '00123' survives the trip to the database and back again unchanged. Whereas in most other RDBMS implementations this is at best a warning: mysql> create table example(test int); Query OK, 0 rows affected (0.06 sec) mysql> insert into example values ('00123'); Query OK, 1 row affected (0.03 sec) mysql> insert into example values ('abcdef'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from example; +------+ | test | +------+ | 123 | | 0 | +------+ 3 rows in set (0.02 sec) This "sometimes it's text, sometimes it's a number" property of SQLite is well documented, but can lead to some impressive "what just happened to my data?" scenarios if you're not used to it.
  - em500 5y ago
    Right, the unique (for an RMDBS) properties of sqlite is that (1) individual values rather than columns are typed and that (2) "column types" are affinities that only affect the casting rules on insertion. But your initial example only shows standard casting behavior in common with other RMDBS implementations.
mpwoz 5y ago
In this example, isn't the problem that you're declaring column A as an 'int' type when what you really wanted was a string? Column B would be the "correct" one for your use case I'd think, unless you also need numeric sorting behavior. Is there a type in e.g. Postgresql that would let you store '000123', give you numeric sorting (000123 > 1), and still return the leading zero characters? AFAIK that doesn't exist but I'm not that familiar with DB types.
- banana_giraffe 5y ago
  Yep. To be clear, the real problem where I shot myself in the foot was a column declared as an int, and stored hex strings. No one noticed the issue, because 100% of the time SQLite left the strings alone, or the string->int->string conversions where safe. Then came along a case where the hex string was all ints, and had leading zeroes that were dropped. Those leading zeroes had meaning, so wacky things happened. And yep, it's all my fault for the column definition. Still an annoying surprise, esp since in this specific case, the surprise happened years after I screwed up.

usmannk 5y ago

This is a classic mixup in Excel/spreadsheet tools too, as they'll often assume any pasted numerical IDs are ints and "helpfully" strip the leading zeroes.

asddubs 5y ago

well, it sure is one hell of a footgun

em500 5y ago

I'm surprised that sqlite is singled out for this, AFAIK this is standard RDBMS behavior. Your example gives exactly the same results in mysql.

banana_giraffe 5y ago
Sure, but I think what throws people for a loop is that in SQLite, you can store text in that column: sqlite> create table example(a int); sqlite> insert into example values('00123'); sqlite> insert into example values('abcdef'); sqlite> select * from example; +--------+ | a | +--------+ | 123 | | abcdef | +--------+ Not to mention, "create table example(a)" is perfectly fine, and then the '00123' survives the trip to the database and back again unchanged. Whereas in most other RDBMS implementations this is at best a warning: mysql> create table example(test int); Query OK, 0 rows affected (0.06 sec) mysql> insert into example values ('00123'); Query OK, 1 row affected (0.03 sec) mysql> insert into example values ('abcdef'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from example; +------+ | test | +------+ | 123 | | 0 | +------+ 3 rows in set (0.02 sec) This "sometimes it's text, sometimes it's a number" property of SQLite is well documented, but can lead to some impressive "what just happened to my data?" scenarios if you're not used to it.
- em500 5y ago
  Right, the unique (for an RMDBS) properties of sqlite is that (1) individual values rather than columns are typed and that (2) "column types" are affinities that only affect the casting rules on insertion. But your initial example only shows standard casting behavior in common with other RMDBS implementations.

mpwoz 5y ago

In this example, isn't the problem that you're declaring column A as an 'int' type when what you really wanted was a string? Column B would be the "correct" one for your use case I'd think, unless you also need numeric sorting behavior. Is there a type in e.g. Postgresql that would let you store '000123', give you numeric sorting (000123 > 1), and still return the leading zero characters? AFAIK that doesn't exist but I'm not that familiar with DB types.

banana_giraffe 5y ago
Yep. To be clear, the real problem where I shot myself in the foot was a column declared as an int, and stored hex strings. No one noticed the issue, because 100% of the time SQLite left the strings alone, or the string->int->string conversions where safe. Then came along a case where the hex string was all ints, and had leading zeroes that were dropped. Those leading zeroes had meaning, so wacky things happened. And yep, it's all my fault for the column definition. Still an annoying surprise, esp since in this specific case, the surprise happened years after I screwed up.