JSON functions stopped working with blobs in SQLite 3.45 (opens in new tab)

(sqlite.org)

50 pointsinian2y ago11 comments

11 comments

o11c2y ago

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 people assumed everything was fine.

lifthrasiir2y ago

That got me nerd-sniped. The following grammar describes all possible JSON-JSONB polyglots:

    d = '0' | ... | '9'
    e = 'E' | 'e'
    
    int-frag = d d d
    canon-float-frag = int-frag | d e d | d '.' d
    float-frag = canon-float-frag | '.' d d | d d '.'
    float-suffix-frag = float-frag | e '+' d | e '-' d | e d d | '.' e d
    
    string-type = '7' | '8' | '9'
    
    polyglot-json =
      '3' int-frag |
      '5' canon-float-frag |
      '6' float-frag |
      string-type float-suffix-frag |
      '[' '\x09' string-type float-suffix-frag ']'

There are some additional possibilities like `40e3` which are rejected by the validation code but otherwise accepted. (`4` encodes a three-byte-long hexadecimal literal.)

hitekker2y ago

> 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?

o11c2y ago

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.

alberth2y ago

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 break backward compatibility (even for a prior unknown bug), they are considering persisting the bug as seen here:

https://sqlite.org/forum/forumpost/bcec95d836ad4048

chubot2y ago

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 known about it, I would have done something about it. I probably would have tried to preserve the buggy behavior - to maintain "bug compatibility". But I didn't know.

So there is a bug -- which is fine, all software has bugs -- but it's not a USER error, which you're implying.

fisf2y ago

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).

userbinator2y ago

Personally, I don't think that "you're doing it wrong" by calling the behaviour previously expected to be a bug is a good excuse, and in fact is an example of being user-hostile. Imagine being on the receiving end. When the documentation says one thing but the actual behaviour is different, people will sensibly follow reality.

hitekker2y ago

The GP seems to have a quoted a commenter in the SQLite thread, who not a maintainer or a representative of the community. The actual maintainer wrote:

> This bug in prior versions of SQLite was unknown to the developers. If I had known about it, I would have done something about it. I probably would have tried to preserve the buggy behavior - to maintain "bug compatibility". But I didn't know.

That comment is much more patient, and less easily misconstrued by drive-by commenters.

1 more reply

zimpenfish2y ago

> But because SQLite doesn’t want to break backward compatibility (even for a prior unknown bug)

Having been bitten by them breaking compatibility by fixing a "bug" that worked for a long time[0], I would dispute this (albeit mildly.)

[0] Using CTEs with views.

j / k navigate · click thread line to collapse

11 comments

o11c2y ago

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;

lifthrasiir2y ago

That got me nerd-sniped. The following grammar describes all possible JSON-JSONB polyglots:

    d = '0' | ... | '9'
    e = 'E' | 'e'
    
    int-frag = d d d
    canon-float-frag = int-frag | d e d | d '.' d
    float-frag = canon-float-frag | '.' d d | d d '.'
    float-suffix-frag = float-frag | e '+' d | e '-' d | e d d | '.' e d
    
    string-type = '7' | '8' | '9'
    
    polyglot-json =
      '3' int-frag |
      '5' canon-float-frag |
      '6' float-frag |
      string-type float-suffix-frag |
      '[' '\x09' string-type float-suffix-frag ']'

There are some additional possibilities like `40e3` which are rejected by the validation code but otherwise accepted. (`4` encodes a three-byte-long hexadecimal literal.)

hitekker2y ago

> 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?

o11c2y ago

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.

alberth2y ago

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 break backward compatibility (even for a prior unknown bug), they are considering persisting the bug as seen here:

https://sqlite.org/forum/forumpost/bcec95d836ad4048

chubot2y ago

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.

So there is a bug -- which is fine, all software has bugs -- but it's not a USER error, which you're implying.

fisf2y ago

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

userbinator2y ago

hitekker2y ago

The GP seems to have a quoted a commenter in the SQLite thread, who not a maintainer or a representative of the community. The actual maintainer wrote:

That comment is much more patient, and less easily misconstrued by drive-by commenters.

1 more reply

zimpenfish2y ago

> But because SQLite doesn’t want to break backward compatibility (even for a prior unknown bug)

Having been bitten by them breaking compatibility by fixing a "bug" that worked for a long time[0], I would dispute this (albeit mildly.)

[0] Using CTEs with views.

j / k navigate · click thread line to collapse