3 ms·

I learnt this the hard way when I decided to go for SQLite for a hobby project for 'simplicity'. Depending on how you store your values you could have a column

by koyote 5y ago

I learnt this the hard way when I decided to go for SQLite for a hobby project for 'simplicity'. Depending on how you store your values you could have a column with both floats and ints where a simple query like 'number < 10' will result in weird results. On the performance side I wonder how much of an effect this has as the DB can't guarantee the size of any given column/row. I'd also love to know why it was designed this way; I don't see a single advantage to be honest...

wenc 5y ago
I also found out the hard way -- I was initially excited to adopt SQLite everywhere because I had heard so many good things about it (which are in fact true), but its dynamic typing/type-affinity based design made it difficult to use as a data store especially for analytics work. I mean, you can still use it, but you have to do your own type checking/coercion in code -- basically you have to re-implement a subset of a type handling system -- which you'd get for free in a full SQL db. You also have to standardize certain types like DATEs because SQLite has no native date type -- the 3 ways to implement dates in SQLite are ISO 8601 strings, Julian and UNIX epochs, and you can convert between them, but you lose some performance on joins and aggregations. It's best to think of SQLite as its own data structure/file format which happens to have a SQL engine on top of it, rather than an actual database format. In fact the docs makes a case for SQLite as an application file format [1]. For analytics work however, I'm finding DuckDB (https://duckdb.org https://duckdb.org) to be quite amazing. It is SQLite-ish (i.e. also an embedded database), but has a columnar query engine, can read parquet files, and has native types (including date!). It also runs in-process in Python and queries Pandas dataframes using a more optimized query engine than Pandas itself. Side: Many of the main contributors work at CWI Amsterdam, the Dutch institute where Guido van Rossum conceived Python. [1] https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
- clnhlzmn 5y ago
  > I mean, you can still use it, but you have to do your own type checking/coercion in code Can you explain why you wouldn't have to check what you're putting into the database? If you're just stuffing values of unknown type into a statically typed DB you're going to get errors up front and if you stuff values of unknown type into SQLite you might get errors down the road. Either way you probably should know what you're putting into the DB in the first place.
  - deleted 5y ago
    [deleted]
  - mumblemumble 5y ago
    SQLite doesn't fit perfectly cleanly into this, but, for me, the big distinction is between schema-on-read and schema-on-write. This captures the fact that you always need to have some sort of schema constraint when you're working with data. The question is, do you apply those constraints when you're writing the data, or when you're reading it? Schema-on-write is useful when you know exactly what the schema should be ahead of time, and it's static. That happens quite often in OLTP and business intelligence applications, but that's not always what you're doing. Schema-on-read is very useful in those situations where the schema constraints you need can't be known ahead of time, or might vary from situation to situation. At that point you're kind of stuck delaying the schema bits (including making sure everything is an appropriate type) until the last minute. This comes up in, for example, big data applications such as data lakes.
    - wenc 5y ago
      Schema-on-read vs Schema-on-write are definitely relevant concepts. I think SQLite sort of straddles the two -- it's sort of a loose schema-on-write (with type affinity to its base types), and schema-on-read (with respect to more complex types which is inferred from what is stored in base types)
      - mumblemumble 5y ago
        Agreed. Which kind of makes it even more complicated to talk about. Whether its approach offers the best of both worlds, or the worst of both words, depends on your application, and the dividing line doesn't cleanly follow any of the distinctions we normally make among types of application.
  - wenc 5y ago
    It's not so much that one is stuffing random/unknown types into the database without type-checking. Programming language types do not map exactly to database types even in statically typed DBs. ORMs manage this translation for you (imperfectly at times), but for those of those of us work with directly with SQL, we do typecheck, otherwise the INSERTs will fail. It's more that static database types provide a standard contractual interface that is enforced (agnostic of application and programming language) and has reproducible behavior upon retrieval. The advantage of static types is the guarantee that if a data point is successfully INSERTed, it can be successfully retrieved in the future. In a dynamically typed DB you have a problem of standardization across codebases -- every new program/microservice that is written will have to use the exact same typechecking code, or else risk future retrieval issues. Those that do type coercion on the other end are essentially guessing and hoping that the original type was successfully reproduced upon retrieval. Plus if you work with different programming languages, that same code has to be ported to all the different languages. You also find yourselves having to reinvent the wheel a lot -- for instance the SQL DECIMAL type. In SQLite you can either store it as an INTEGER or REAL, and then either store metadata in another field or create a specific function to retrieve the INTEGER and recreate the specific DECIMAL type with the right number of digits (say DECIMAL(18,0)). On the other hand you can rely on SQL types and get all this for free and have the assurance that it will work impeccably.
wruza 5y ago
query like 'number < 10' will result in weird results Can you elaborate?
BulgarianIdiot 5y ago
> Depending on how you store your values you could have a column with both floats and ints where a simple query like 'number < 10' will result in weird results Can you specify what weird results you got?
- jolmg 5y ago
  I'm not koyote, but this one caught me off-guard once: sqlite> .import /dev/stdin t x 1 2 sqlite> select * from t where x < 10; 1 sqlite> select * from t where x > 10; 2
  - BulgarianIdiot 5y ago
    What I'm guessing here is that the column is text-biased and so what's being compared is "2" to 10, and the implicit cast is "2" > "10" because strings are compared char by char left to right. In schemas, when you define a column as integer or float, input is interpreted this way WHEN POSSIBLE (i.e. with 1 and 2 it's possible, but if you pass "foo" it'll be stored as a text attribute, obviously). What people should know with SQLite is that it's flexible about types, and stores everything you pass, but it still cares what the column type is and it affects how it interprets input.
    - jolmg 5y ago
      > What people should know with SQLite is that it's flexible about types and flexible how. What caught me off-guard is that I thought that since the types aren't specified with .import, sqlite would behave like how it does when you create a table without specifying the types: sqlite> create table t(x); sqlite> insert into t values (1), (2); sqlite> select * from t where x < 10; 1 2 sqlite> select * from t where x > 10; But that's not the case. It behaves like the types were explicitly set as text: sqlite> create table t(x text); sqlite> insert into t values (1), (2); sqlite> select * from t where x < 10; 1 sqlite> select * from t where x > 10; 2

by koyote 5y ago

wenc 5y ago

I also found out the hard way -- I was initially excited to adopt SQLite everywhere because I had heard so many good things about it (which are in fact true), but its dynamic typing/type-affinity based design made it difficult to use as a data store especially for analytics work. I mean, you can still use it, but you have to do your own type checking/coercion in code -- basically you have to re-implement a subset of a type handling system -- which you'd get for free in a full SQL db. You also have to standardize certain types like DATEs because SQLite has no native date type -- the 3 ways to implement dates in SQLite are ISO 8601 strings, Julian and UNIX epochs, and you can convert between them, but you lose some performance on joins and aggregations. It's best to think of SQLite as its own data structure/file format which happens to have a SQL engine on top of it, rather than an actual database format. In fact the docs makes a case for SQLite as an application file format [1]. For analytics work however, I'm finding DuckDB (https://duckdb.org https://duckdb.org) to be quite amazing. It is SQLite-ish (i.e. also an embedded database), but has a columnar query engine, can read parquet files, and has native types (including date!). It also runs in-process in Python and queries Pandas dataframes using a more optimized query engine than Pandas itself. Side: Many of the main contributors work at CWI Amsterdam, the Dutch institute where Guido van Rossum conceived Python. [1] https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html

clnhlzmn 5y ago
> I mean, you can still use it, but you have to do your own type checking/coercion in code Can you explain why you wouldn't have to check what you're putting into the database? If you're just stuffing values of unknown type into a statically typed DB you're going to get errors up front and if you stuff values of unknown type into SQLite you might get errors down the road. Either way you probably should know what you're putting into the DB in the first place.
- deleted 5y ago
  [deleted]
- mumblemumble 5y ago
  SQLite doesn't fit perfectly cleanly into this, but, for me, the big distinction is between schema-on-read and schema-on-write. This captures the fact that you always need to have some sort of schema constraint when you're working with data. The question is, do you apply those constraints when you're writing the data, or when you're reading it? Schema-on-write is useful when you know exactly what the schema should be ahead of time, and it's static. That happens quite often in OLTP and business intelligence applications, but that's not always what you're doing. Schema-on-read is very useful in those situations where the schema constraints you need can't be known ahead of time, or might vary from situation to situation. At that point you're kind of stuck delaying the schema bits (including making sure everything is an appropriate type) until the last minute. This comes up in, for example, big data applications such as data lakes.
  - wenc 5y ago
    Schema-on-read vs Schema-on-write are definitely relevant concepts. I think SQLite sort of straddles the two -- it's sort of a loose schema-on-write (with type affinity to its base types), and schema-on-read (with respect to more complex types which is inferred from what is stored in base types)
    - mumblemumble 5y ago
      Agreed. Which kind of makes it even more complicated to talk about. Whether its approach offers the best of both worlds, or the worst of both words, depends on your application, and the dividing line doesn't cleanly follow any of the distinctions we normally make among types of application.
- wenc 5y ago
  It's not so much that one is stuffing random/unknown types into the database without type-checking. Programming language types do not map exactly to database types even in statically typed DBs. ORMs manage this translation for you (imperfectly at times), but for those of those of us work with directly with SQL, we do typecheck, otherwise the INSERTs will fail. It's more that static database types provide a standard contractual interface that is enforced (agnostic of application and programming language) and has reproducible behavior upon retrieval. The advantage of static types is the guarantee that if a data point is successfully INSERTed, it can be successfully retrieved in the future. In a dynamically typed DB you have a problem of standardization across codebases -- every new program/microservice that is written will have to use the exact same typechecking code, or else risk future retrieval issues. Those that do type coercion on the other end are essentially guessing and hoping that the original type was successfully reproduced upon retrieval. Plus if you work with different programming languages, that same code has to be ported to all the different languages. You also find yourselves having to reinvent the wheel a lot -- for instance the SQL DECIMAL type. In SQLite you can either store it as an INTEGER or REAL, and then either store metadata in another field or create a specific function to retrieve the INTEGER and recreate the specific DECIMAL type with the right number of digits (say DECIMAL(18,0)). On the other hand you can rely on SQL types and get all this for free and have the assurance that it will work impeccably.

wruza 5y ago

query like 'number < 10' will result in weird results Can you elaborate?

BulgarianIdiot 5y ago

> Depending on how you store your values you could have a column with both floats and ints where a simple query like 'number < 10' will result in weird results Can you specify what weird results you got?

jolmg 5y ago
I'm not koyote, but this one caught me off-guard once: sqlite> .import /dev/stdin t x 1 2 sqlite> select * from t where x < 10; 1 sqlite> select * from t where x > 10; 2
- BulgarianIdiot 5y ago
  What I'm guessing here is that the column is text-biased and so what's being compared is "2" to 10, and the implicit cast is "2" > "10" because strings are compared char by char left to right. In schemas, when you define a column as integer or float, input is interpreted this way WHEN POSSIBLE (i.e. with 1 and 2 it's possible, but if you pass "foo" it'll be stored as a text attribute, obviously). What people should know with SQLite is that it's flexible about types, and stores everything you pass, but it still cares what the column type is and it affects how it interprets input.
  - jolmg 5y ago
    > What people should know with SQLite is that it's flexible about types and flexible how. What caught me off-guard is that I thought that since the types aren't specified with .import, sqlite would behave like how it does when you create a table without specifying the types: sqlite> create table t(x); sqlite> insert into t values (1), (2); sqlite> select * from t where x < 10; 1 2 sqlite> select * from t where x > 10; But that's not the case. It behaves like the types were explicitly set as text: sqlite> create table t(x text); sqlite> insert into t values (1), (2); sqlite> select * from t where x < 10; 1 sqlite> select * from t where x > 10; 2