SQLite Forum

SELECT with LIMIT OFFSET clause return unexpected result (with sample db and query)
Login

SELECT with LIMIT OFFSET clause return unexpected result (with sample db and query)

(1.1) By LT (LT123456) on 2022-01-06 14:35:23 edited from 1.0 [link] [source]

I'm working with an SQLite DB in my application. Some query returns unexpected results. Below I try to describe the issue with a minimal example:

  • I'm using SQLite v3.31 and I have tested the same problem with the latest v3.37.1 and the results are the same.

  • Suppose I have a DB called test.db created with the following script:

-- Table: series

BEGIN TRANSACTION;

DROP TABLE IF EXISTS "series";

CREATE TABLE IF NOT EXISTS "series" ( "id" INTEGER NOT NULL DEFAULT 1 PRIMARY KEY AUTOINCREMENT UNIQUE, "acqtime" TEXT );

INSERT INTO "series" ("id","acqtime") VALUES (1,'2021-01-02T22:33:44');

INSERT INTO "series" ("id","acqtime") VALUES (2,'2021-01-03T01:12:23');

INSERT INTO "series" ("id","acqtime") VALUES (3,'2021-01-03T01:02:41');

INSERT INTO "series" ("id","acqtime") VALUES (4,'2021-01-03T01:47:55');

COMMIT;

This just produces a simply DB with one table "series" and with 4 records.

  • Then I query this table with

SELECT (

       SELECT DISTINCT date(series.acqtime) 

         FROM series

        ORDER BY date(series.acqtime) DESC

        LIMIT 1 OFFSET 400 -- note this supper large OFFSET value

   ) as subquery_result,

   date(series.acqtime) < (

                              SELECT DISTINCT date(series.acqtime) 

                                FROM series

                               ORDER BY date(series.acqtime) DESC

                               LIMIT 1 OFFSET 400  -- note this supper large OFFSET value

                          ) as cmp_result

FROM series;

As you can see, this query just produces two columns based on the table contents.

  1. The 1st column "subquery_result" contains the result of a subquery
  2. The 2nd column "cmp_result" contains the result of a comparison between the "acqtime" with the returned value of column 1.

Here is the problem:

  • According to the content of the table, I expect the subquery

    (
           SELECT DISTINCT date(series.acqtime) 
             FROM series
            ORDER BY date(series.acqtime) DESC
            LIMIT 1 OFFSET 400 -- note this supper large OFFSET value
    )
    
    returns NULL, because I put a ridiculously large number for the OFFSET value in the LIMIT clause. (There are only 4 records in the table). However, if you run the query above, you will see the output is
    "subquery_result","cmp_result"
    "2021-01-03",1
    "2021-01-03",0
    "2021-01-03",0
    "2021-01-03",0
    
    This indicates that the subquery does not return NULL but returns a valid record which I don't expect.

  • In addition to this issue, I found other two problems:

    • one is that if I replace the ordering keyword "DESC" with "ASC", the subquery does turn NULL.
    • The 2nd is that if I add another record into the "series" table which contains a new different distinct date such as '2021-01-02T01:47:55', the result will be as what I expect. I.e.:
      "subquery_result","cmp_result"
      NULL	NULL	
      NULL	NULL	
      NULL	NULL	
      NULL	NULL
      
      Note the "subquery_result" is NULL.

Questions
1. Is that correct that I expect a NULL from the subquery? I didn't find clear documentation describing the behavior of the LIMIT OFFSET clause when the OFFSET value is beyond the total number of records. But I think a NULL should be a logical result. (Unless we can confirm this will lead to an undefined result)
2. Can some of you give some explanation of the different behaviors when changing the number of distinct records or changing the ordering method?
3. If the OFFSET value beyond the total number of records will lead to undefined behavior, can this be mentioned in the doc? If it will give a NULL, can you confirm that the unexpected error case I described above is an SQLite bug?

(2) By LT (LT123456) on 2022-01-12 16:22:51 in reply to 1.1 [link] [source]

Can this be confirmed as a bug? Anyone can give it a try?

(5) By Keith Medcalf (kmedcalf) on 2022-01-12 18:56:11 in reply to 2 [link] [source]

THe current tip of trunk displays the behaviour shown.

(3) By Simon Slavin (slavin) on 2022-01-12 18:19:21 in reply to 1.1 [link] [source]

I fail to reproduce the above in SQLite 3.36.0:

simon@183 Desktop % sqlite3 test.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS "series" ( "id" INTEGER NOT NULL DEFAULT 1 PRIMARY KEY AUTOINCREMENT UNIQUE, "acqtime" TEXT );
sqlite> INSERT INTO "series" ("id","acqtime") VALUES (1,'2021-01-02T22:33:44');
sqlite> INSERT INTO "series" ("id","acqtime") VALUES (2,'2021-01-03T01:12:23');
sqlite> INSERT INTO "series" ("id","acqtime") VALUES (3,'2021-01-03T01:02:41');
sqlite> INSERT INTO "series" ("id","acqtime") VALUES (4,'2021-01-03T01:47:55');
sqlite> .mode table
sqlite> SELECT * FROM series;
+----+---------------------+
| id |       acqtime       |
+----+---------------------+
| 1  | 2021-01-02T22:33:44 |
| 2  | 2021-01-03T01:12:23 |
| 3  | 2021-01-03T01:02:41 |
| 4  | 2021-01-03T01:47:55 |
+----+---------------------+
sqlite> SELECT DISTINCT date(series.acqtime)
   ...>          FROM series
   ...>         ORDER BY date(series.acqtime) DESC;
+----------------------+
| date(series.acqtime) |
+----------------------+
| 2021-01-03           |
| 2021-01-02           |
+----------------------+
sqlite> SELECT DISTINCT date(series.acqtime)
   ...>          FROM series
   ...>         ORDER BY date(series.acqtime) DESC
   ...> LIMIT 1 OFFSET 400;
sqlite>

I get the expected zero-row result to the above SELECT whereas the OP says he gets four rows. Did I misunderstand the query ?

(4) By David Raymond (dvdraymond) on 2022-01-12 18:38:16 in reply to 3 [link] [source]

Did I misunderstand the query ?

Yes. It being a sub-query is part of whatever the bug is.

Can add that I'm seeing what they're seeing with the 3.37.2 CLI

(6) By Ryan Smith (cuz) on 2022-01-12 19:10:05 in reply to 1.1 [source]

Confirmed a bug.

Here is a minimal Query to reproduce:

WITH dt(cdate) AS (
    SELECT '2021-01-01' UNION ALL
    SELECT '2021-01-01'
)
SELECT CASE WHEN
       (SELECT DISTINCT date(cdate)
          FROM dt
         ORDER BY date(cdate) DESC
         LIMIT 1 OFFSET 10
       ) IS NULL THEN 'Pass' ELSE 'FAIL' END AS Test
  FROM dt;

  -- Test  
  -- ------
  -- FAIL  
  -- FAIL  



The problem seems to be with the ordering being DESC and the two dates being exactly the same.

Here are two similar examples, one with the ordering being ASC in stead of DESC, and the next with the dates being dissimilar:


WITH dt(cdate) AS (
    SELECT '2021-01-01' UNION ALL
    SELECT '2021-01-01'
)
SELECT CASE WHEN
       (SELECT DISTINCT date(cdate)
          FROM dt
         ORDER BY date(cdate) ASC
         LIMIT 1 OFFSET 10
       ) IS NULL THEN 'Pass' ELSE 'FAIL' END AS Test
  FROM dt;

  -- Test  
  -- ------
  -- Pass  
  -- Pass  



WITH dt(cdate) AS (
    SELECT '2021-01-01' UNION ALL
    SELECT '2021-01-02'
)
SELECT CASE WHEN
       (SELECT DISTINCT date(cdate)
          FROM dt
         ORDER BY date(cdate) DESC
         LIMIT 1 OFFSET 10
       ) IS NULL THEN 'Pass' ELSE 'FAIL' END AS Test
  FROM dt;


  -- Test  
  -- ------
  -- Pass  
  -- Pass 
 

Hope that helps.

(7) By Richard Hipp (drh) on 2022-01-12 19:33:00 in reply to 1.1 [link] [source]

Simplified test case:

CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1),(1),(1);
SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 100) FROM t1;

This returns three 1's, but ought to return three NULLs, I think.

(8) By Richard Hipp (drh) on 2022-01-12 19:40:56 in reply to 7 [link] [source]

Bisects to check-in 2a74129a21f9745f. I do not believe that is the source of the problem - I think it is just a change that enabled this particular test-case to work. Probably the problem goes back a lot further.

(9) By Richard Hipp (drh) on 2022-01-12 20:32:45 in reply to 7 [link] [source]

I think this is now fixed at check-in 9282bcde301cee2a.