SQLite Forum

SELECT with LIMIT OFFSET clause return unexpected result (with sample db and query)
Login
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.