1

am am trying to write a function in python to use sqlite and while I managed to get it to work there is a behavior in sqlite that I dont understand when using the count command. when I run the following sqlite counts as expected, ie returns an int.

SELECT COUNT (*) FROM Material WHERE level IN (?) LIMIT 10

however when I add, shown below, an offset to the end sqlite returns an emply list, in other words nothing.

SELECT COUNT (*) FROM Material WHERE level IN (?) LIMIT 10 OFFSET 82

while omitting the offset is an easy fix I don't understand why sqlite returns nothing. Is this the expected behavior for the command I gave?

thanks for reading

user169808
  • 503
  • 1
  • 6
  • 27
  • (1) for the query that *counts as expected*, what is the point of LIMIT? (2) for the query that *returns an empty list* what is the point of LIMIT and OFFSET? – forpas Dec 20 '22 at 16:47
  • @MidhunMP when offset is 5 it returns nothing – user169808 Dec 20 '22 at 16:49
  • @forpas I noticed that the limit did not effect the result when counting. so instead of having 2 functions (for counting and for searching) I tried mashing both together it was my hope that other than the start of the command I wouldn't have to change anything else. – user169808 Dec 20 '22 at 16:52
  • 1
    An aggregation query like yours returns only 1 row. Any limit that you apply will have no effect because the query will always return that 1 row. By applying also OFFSET what do you want to achieve? It's only 1 row. – forpas Dec 20 '22 at 16:56
  • @forpas So is it correct for me to think that the offset is acting on what sqlite returns? so it runs the command as if there is no limit and offset when searching on the database and then applies a limit and offset when giving an output. I imagined that limit and offset changed the way it searches in the database – user169808 Dec 20 '22 at 17:24

1 Answers1

1

When you execute that COUNT(*) it will return you only a single row.
The LIMIT function is for limiting the number of rows returned. You are setting the limit to 10 which doesn't have any effect here (Because it is returning only a single row).
OFFSET is for offsetting/skipping specified number of rows. Which also doesn't have any effect here.

In simple terms your query translates to COUNT number of rows, then return 10 rows starting from 83rd position. Since you've a single row it will always return empty.

Read about LIMIT and OFFSET

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
  • thanks, like I just mentioned to @forpas, am I correct in assuming that LIMIT and OFFSET are only applied when SQLite outputs its result? instead of altering how it searches the database? – user169808 Dec 20 '22 at 17:27
  • @user169808 Yes it applies before returning the result. It won't affect the count operation in this case. – Midhun MP Dec 20 '22 at 17:31