JSON functions stopped working with blobs in SQLite 3.45

JSON functions stopped working with blobs in SQLite 3.45(sqlite.org)

50 points by inian 2 years ago | 11 comments

o11c 2 years 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.

lifthrasiir 2 years 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.)

hitekker 2 years 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?

o11c 2 years 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.

alberth 2 years 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

chubot 2 years 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.

fisf 2 years 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).

userbinator 2 years 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.

hitekker 2 years 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.

zimpenfish 2 years 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.

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 ']'