COALESCE() ignores LIMIT 0 clause in subquery?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

COALESCE() ignores LIMIT 0 clause in subquery?

Justin Ng
Is this the appropriate place to discuss this?

The below examples are expected to return 3.
The first example returns 4, the second returns 3.

It seems like LIMIT 0 is ignored by COALESCE().
https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/0
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
    ),
    3
  );

Adding WHERE FALSE does the trick.
https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/1
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
    ),
    3
  );


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: COALESCE() ignores LIMIT 0 clause in subquery?

Richard Hipp-3
Fix checked in at https://sqlite.org/src/info/82e5dcf5c1d500ed

On 9/22/19, Justin Ng <[hidden email]> wrote:

> Is this the appropriate place to discuss this?
>
> The below examples are expected to return 3.
> The first example returns 4, the second returns 3.
>
> It seems like LIMIT 0 is ignored by COALESCE().
> https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/0
> 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
>     ),
>     3
>   );
>
> Adding WHERE FALSE does the trick.
> https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/1
> 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
>     ),
>     3
>   );
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users