https://kotlinlang.org
Join Slack
Anyone have thoughts on writing a query in SQLDelight where my goal is to see if the table is empty?...
# squarelibraries
c
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
Sounds very reasonable to me
K 1
j
you could also do 
== 0
to get a boolean
c
Cool. tableIsEmpty: SELECT _COUNT_(*) == 0; it is. thanks
ooh. wonder if
Copy code
tableIsEmpty:
SELECT COUNT(*) FROM myDbTable LIMIT 1 == 0;
would work...
j
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
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
Yeah sorry I kind of assumed you had a 
FROM
in there
c
rookie mistake on my part 😂
s
I'm surprised 
SELECT COUNT(*) == 0
even works
j
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
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
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
31 Views
Open in Slack
PreviousNext
Powered by

kotlinlang

A modern programming language that makes developers happier.

Powered by