SQLite LIMIT 0 does not work as expected with COALESCE(), use WHERE FALSE instead

[AnyhowStep]

This is a copy-paste from the README

---

Investigate Bug with SQLite,

CREATE TABLE "myTable" (
	"myColumn" INT PRIMARY KEY
);
INSERT INTO
	"myTable"("myColumn")
VALUES
	(4);

SELECT (
  3e0 IN(
    COALESCE(
      (
        SELECT
          "myTable"."myColumn" AS "myTable--myColumn"
        FROM
          "myTable"
        LIMIT
          0
        OFFSET
          0
      ),
      3e0
    )
  )
);

• Expected result : true
• PostgreSQL : true
  • https://www.db-fiddle.com/f/aaKrWx7aAuzzC2HWPcrsBn/8
• MySQL : true
  • https://www.db-fiddle.com/f/tJNBFe4ECTJcHzgAjKvTz4/1
• SQLite : false
  • https://www.db-fiddle.com/f/vvtNQMEZ4FGfpvmRp3Linv/1

---

Investigate Bug with SQLite,

CREATE TABLE "myTable" (
  "myColumn" INT PRIMARY KEY
);
INSERT INTO
  "myTable"("myColumn")
VALUES
  (4);

SELECT
  COALESCE(
    (
      SELECT
        "myTable"."myColumn" AS "myTable--myColumn"
      FROM
        "myTable"
      LIMIT
        0
      OFFSET
        0
    ),
    3e0
  );

• Expected result : 3
• PostgreSQL : 3
• MySQL : 3
• SQLite : 4
  • COALESCE() seems to ignore the LIMIT 0 clause.
  • Use WHERE FALSE when building SQL string.

CREATE TABLE "myTable" (
  "myColumn" INT PRIMARY KEY
);
INSERT INTO
  "myTable"("myColumn")
VALUES
  (4);

SELECT
  COALESCE(
    (
      SELECT
        "myTable"."myColumn" AS "myTable--myColumn"
      FROM
        "myTable"
      WHERE
        FALSE
      LIMIT
        0
      OFFSET
        0
    ),
    3e0
  );

---

This isn't related to this library directly, because it's a problem with SQLite.
But it's something to look out for.

[AnyhowStep]

Holy fuck. They fixed it.
https://sqlite.org/src/info/82e5dcf5c1d500ed

I reported this on 2019-09-22.
They fixed it on 2019-09-23.

And I only just realized it... Today. 2020-03-11.

I just tested it on SQLite 3.31 and it has, indeed, been fixed!

[AnyhowStep]

http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-September/086060.html