https://kotlinlang.org logo
#squarelibraries
Title
# squarelibraries
c

Colton Idle

10/19/2023, 2:22 PM
Anyone have thoughts on writing a query in SQLDelight where my goal is to see if the table is empty? Currently I have this
Copy code
sizeOfTable:
SELECT COUNT(*);
but open to other ideas if anyone knows better. 😅
b

Benoit Quenaudon

10/19/2023, 2:24 PM
Sounds very reasonable to me
K 1
j

jw

10/19/2023, 2:25 PM
you could also do
== 0
to get a boolean
c

Colton Idle

10/19/2023, 2:49 PM
Cool. tableIsEmpty: SELECT _COUNT_(*) == 0; it is. thanks
ooh. wonder if
Copy code
tableIsEmpty:
SELECT COUNT(*) FROM myDbTable LIMIT 1 == 0;
would work...
j

jw

10/19/2023, 2:53 PM
don't think that's valid SQL, but even if it is that is effectively
LIMIT false
COUNT(*)
is always optimized to O(1) so there's nothing to improve upon here except possibly returning the boolean directly rather than doing it in code.
🦜 3
Maybe 20 years ago you needed to do
COUNT(column_in_index)
, but not anymore.
🦜 2
today i learned 1
c

Colton Idle

10/19/2023, 3:04 PM
Well apparently my table is empty (according to android studio db inspector), but SELECT _COUNT_(*) == 0 returns false. I wonder if I need to add the table name to the select statement.
yeah. just updated tableCount: SELECT _COUNT_(*) FROM myDbTable and then just checking if it == 0 later. and that works as expected 🤷
j

jw

10/19/2023, 3:07 PM
Yeah sorry I kind of assumed you had a
FROM
in there
c

Colton Idle

10/19/2023, 3:12 PM
rookie mistake on my part 😂
s

saket

10/19/2023, 3:25 PM
I'm surprised
SELECT COUNT(*) == 0
even works
j

jw

10/19/2023, 3:31 PM
Copy code
sqlite> SELECT COUNT(*);
1

sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*);
QUERY PLAN
`--SCAN CONSTANT ROW

sqlite> EXPLAIN SELECT COUNT(*);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    0   Start at 1
1     Null           0     1     1                    0   r[1..1]=NULL
2     AggStep        0     0     1     count(0)       0   accum=r[1] step(r[0])
3     AggFinal       1     0     0     count(0)       0   accum=r[1] N=0
4     Copy           1     2     0                    0   r[2]=r[1]
5     ResultRow      2     1     0                    0   output=r[2]
6     Halt           0     0     0                    0
weird
If you do
SELECT *;
it yells.
SELECT COUNT(0);
is 1, but not sure why
SELECT COUNT(*);
works...
😄 2
e

ephemient

10/19/2023, 6:31 PM
SELECT COUNT(*)
of any table (regardless of number of columns) is fine but
SELECT *
of a zero-column table isn't, I guess?
not that sqlite supports zero-column tables though…
j

jessewilson

10/20/2023, 5:11 AM
I assume you’re in SQLite. MySQL Innodb is surprisingly slow at count queries and must do a full table scan! https://www.percona.com/blog/count-vs-countcol-in-mysql/
today i learned 1
2 Views