JSON functions stopped working with blobs in SQLite 3.45 • Hacker News Board

Re: JSON functions stopped working with blobs in SQLite 3.45

#11

Post by fisf » Mon, Jan 22, 2024, 11:09 AM UTC

chubot wrote: post #8

alberth wrote: post #2

This an incorrect title. Per this comment: https://sqlite.org/forum/forumpost/b6f940e87dd28cf8 > Fix your code with CASTs instead of complaining about it. You didn't follow the documentation Essentially, the person was using json incorrectly (as per documentation even prior to this release) and now this version of SQLite is causing their incorrect code to not behave as they wanted. But because SQLite doesn’t want to…

This response seems misleading, the "fix your code" reply isn't from the author of sqlite. He considers it a bug (in an EARLIER version of having unspecified behavior, not a later version, but there's no difference in practice): That you could use a BLOB as the left-hand side argument to -> in 3.44.0 is a previously unreported bug in 3.44.0. This bug in prior versions of SQLite was unknown to the developers. If I had…

Yes, the response could be more patient, but ultimately, that's absolutely a user error.

If the documentation clearly says that something should not work, the same thing does not work in other DBs (-> semantics follow Postgres for example), and the definition of the underlying DB types does not really allow for this to work, then the smart thing is to not rely on the behavior (and open a bug report).

Re: JSON functions stopped working with blobs in SQLite 3.45

#12

Post by hitekker » Mon, Jan 22, 2024, 5:56 PM UTC

o11c wrote: post #3

The most interesting part here is the constructed polyglot: with ambiguous(j) as (select '[' || char(9) || '721]') select json_valid(j, 0x1) as "RFC 8259", json_valid(cast(j as blob), 0x8) as "JSONB" from ambiguous; Also, this is an example of why dynamic typing is never safe, whether in a normal programming language or in your sql database. It turns out that the sqlite documentation has been lying for years and peop…

> It turns out that the sqlite documentation has been lying for years and people assumed everything was fine.

More like the SQLite docs made an assumption and people, maintainers and users, didn't realize it until later.

Why the sudden hostility?

Re: JSON functions stopped working with blobs in SQLite 3.45

#13

Post by o11c » Mon, Jan 22, 2024, 7:10 PM UTC

hitekker wrote: post #12

o11c wrote: post #3

The most interesting part here is the constructed polyglot: with ambiguous(j) as (select '[' || char(9) || '721]') select json_valid(j, 0x1) as "RFC 8259", json_valid(cast(j as blob), 0x8) as "JSONB" from ambiguous; Also, this is an example of why dynamic typing is never safe, whether in a normal programming language or in your sql database. It turns out that the sqlite documentation has been lying for years and peop…

> It turns out that the sqlite documentation has been lying for years and people assumed everything was fine. More like the SQLite docs made an assumption and people, maintainers and users, didn't realize it until later. Why the sudden hostility?

Because when you explicitly document "if you do this, it will fail", and people's code doesn't fail, they assume they must not have been doing that.

Especially remember that most people don't care about opening files in text mode vs binary mode. This is only more true in a UTF-8-only world.