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?