4 ms·

It really should be default, but it isn't due to backward compatibility (i assume).

by tehlike 3mo ago

tuvix 3mo ago
I’m kind of curious why the decision to have implicit casting like this was made in the first place. I can’t think of a single upside other than not having to type out cast(foo as bar)
- pstuart 3mo ago
  IIRC, the project started out as TCL code and it carried that vibe through to what it is today.
- notRobot 3mo ago
  SQLite docs: The Advantages Of Flexible Typing: https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html
- rogerbinns 3mo ago
  SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to int or float, add them, and generate a stringified number as a result. The vast majority of implementation code did not have to care about types, and very local decisions could be made such as in the addition example. TCL was used as a dev wrapper language at the time, and it functioned the same way. It was only in mid-2004 that SQLite 3 was released which used its own storage backend, and that allowed for the 5 supported storage types (int64, string, bytes, float, null). It was API compatible (with minor adjustments) with the earlier SQLite 2, so the lack of static typing continued, otherwise everyone would have to rewrite their code. You do get dynamic typing, which hasn't been a problem for the vast majority of SQLite users. Do remember that SQLite is competition for fopen, not Oracle / Postgres etc. It is trying to make things as effective as possible in that scenario. If you don't want numbers in your string column, then don't do that!
  - deleted 3mo ago
    [deleted]
  - somat 3mo ago
    If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb. I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.
    - SoftTalker 3mo ago
      OpenLDAP also originally used BerkeleyDB
- mb7733 3mo ago
  It's even worse than implicit casting, if the value can't be cast to the the column's type, it's just inserted without casting. Eg. into an integer column, '10' -> 10 and '1O' -> '1O'
  - rogerbinns 3mo ago
    That is documented behaviour - think of it as making a best effort, and not losing the value. As of January 2006 you could add CHECK constraints using the TYPEOF function to reject that at the SQL level. And it is your own code - there is no server - doing the insertions. As was common back then, protecting you from your own bugs was not a high priority for APIs!
poidos 3mo ago
It’s a stated [0] goal of the project: > SQLite strives to be flexible regarding the datatype of the content that it stores. [0]: https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html
- masklinn 3mo ago
  You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.
  - poidos 3mo ago
    Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.
    - masklinn 3mo ago
      The intent of ANY is obviously that the values be flexible. That’s why it’s there if you need it.
    - drdexebtjl 3mo ago
      “I intended this to be an integer but it could really be anything” is not very useful.
      - poidos 3mo ago
        Sure it is. If you encounter something that’s not an int, that could be a signal you have a bug in your writers. Or in the source of the data. That’s useful information compared to “oh, I have some ints and some strings, that’s ANY, everything is ok.”
        FridgeSeal 3mo ago
        > If you encounter something that’s not an int, that could be a signal you have a bug in your writers Which, is something you could have caught before it got written at all if you had your db enforcing your types.

It really should be default, but it isn't due to backward compatibility (i assume).

by tehlike 3mo ago

It really should be default, but it isn't due to backward compatibility (i assume).

tuvix 3mo ago

I’m kind of curious why the decision to have implicit casting like this was made in the first place. I can’t think of a single upside other than not having to type out cast(foo as bar)

pstuart 3mo ago
IIRC, the project started out as TCL code and it carried that vibe through to what it is today.
notRobot 3mo ago
SQLite docs: The Advantages Of Flexible Typing: https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html
rogerbinns 3mo ago
SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to int or float, add them, and generate a stringified number as a result. The vast majority of implementation code did not have to care about types, and very local decisions could be made such as in the addition example. TCL was used as a dev wrapper language at the time, and it functioned the same way. It was only in mid-2004 that SQLite 3 was released which used its own storage backend, and that allowed for the 5 supported storage types (int64, string, bytes, float, null). It was API compatible (with minor adjustments) with the earlier SQLite 2, so the lack of static typing continued, otherwise everyone would have to rewrite their code. You do get dynamic typing, which hasn't been a problem for the vast majority of SQLite users. Do remember that SQLite is competition for fopen, not Oracle / Postgres etc. It is trying to make things as effective as possible in that scenario. If you don't want numbers in your string column, then don't do that!
- deleted 3mo ago
  [deleted]
- somat 3mo ago
  If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb. I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.
  - SoftTalker 3mo ago
    OpenLDAP also originally used BerkeleyDB
mb7733 3mo ago
It's even worse than implicit casting, if the value can't be cast to the the column's type, it's just inserted without casting. Eg. into an integer column, '10' -> 10 and '1O' -> '1O'
- rogerbinns 3mo ago
  That is documented behaviour - think of it as making a best effort, and not losing the value. As of January 2006 you could add CHECK constraints using the TYPEOF function to reject that at the SQL level. And it is your own code - there is no server - doing the insertions. As was common back then, protecting you from your own bugs was not a high priority for APIs!

poidos 3mo ago

It’s a stated [0] goal of the project: > SQLite strives to be flexible regarding the datatype of the content that it stores. [0]: https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html

masklinn 3mo ago
You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.
- poidos 3mo ago
  Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.
  - masklinn 3mo ago
    The intent of ANY is obviously that the values be flexible. That’s why it’s there if you need it.
  - drdexebtjl 3mo ago
    “I intended this to be an integer but it could really be anything” is not very useful.
    - poidos 3mo ago
      Sure it is. If you encounter something that’s not an int, that could be a signal you have a bug in your writers. Or in the source of the data. That’s useful information compared to “oh, I have some ints and some strings, that’s ANY, everything is ok.”
      - FridgeSeal 3mo ago
        > If you encounter something that’s not an int, that could be a signal you have a bug in your writers Which, is something you could have caught before it got written at all if you had your db enforcing your types.