Hacker News Reader

Comment by banana_giraffe | Hacker News Reader

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.

View on HN

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.

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.

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.

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.

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.

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.

well, it sure is one hell of a footgun

Reply on news.ycombinator.com