Subscribe to this thread
Home - General / All posts - No rows when count(*) = 0
rk
621 post(s)
#27-Feb-17 09:01

In Radian 9.0.159.1

SELECT count(*) as [result] FROM [mfd_root] where false;

gives no rows.

I would expect this query to return a row with [result]=0.

tjhb
10,094 post(s)
#27-Feb-17 09:09

Would you though?

WHERE operates first, and filters out all rows.

There is nothing to be counted, not even any NULL values.

What happens in other DBMS?

What does this give in Radian? (I'm not sure what it should give.)

SELECT COUNT(*) AS [result]

FROM [mfd_root]

GROUP BY TRUE -- or FALSE, or any constant

HAVING FALSE;

tjhb
10,094 post(s)
#27-Feb-17 09:27

(I think there again the result should be nothing. We initially have a non-empty group--but HAVING leaves no rows to be counted.)

tjhb
10,094 post(s)
#27-Feb-17 09:30

What should this give?

SELECT * FROM [mfd_root] WHERE FALSE;

A row with no columns, or no rows?

No rows.

Now apply COUNT.

adamw


10,447 post(s)
#27-Feb-17 12:15

We will look into it.

(Tim: other databases return 0. Yes, the behavior is inconsistent in that SELECT Count(*) FROM t returns 0 if t is empty, but SELECT Count(*) FROM t GROUP BY f obviously does not return records with 0 for all those values of f that are not listed in t, but it is established practice.)

tjhb
10,094 post(s)
#27-Feb-17 12:38

Thanks for explaining Adam. But that sucks! You can't count no rows! Established practice is wrong!

What does

SUM(1)

give, for no rows?

[added] OK, I do get it. The empty set ordinarily has cardinal number 0.

What I'm saying amounts to: logically impossible sets are not the empty set, and they have no cardinal number (not even zero). That might be true, I don't know.

If impossible sets do have cardinality zero, it is a different zero from that if empty possible sets (like a different aleph, at the other extreme).

adamw


10,447 post(s)
#27-Feb-17 12:45

SELECT Sum(f), Avg(f), Min(f), Max(f) FROM t WHERE FALSE returns all NULL values. Same for Sum(1), etc. It is just Count(f) / Count(*) that return 0.

0 for Count vs NULL for everything else is fine - the inconsistency is that a full table group has to produce a record, even though groups created by GROUP BY only produce records for values that are in the table.

tjhb
10,094 post(s)
#27-Feb-17 13:08

A full table group should not produce a record if there is no such group. (If there is such a group but it is empty, that is different.)

KlausDE

6,410 post(s)
#27-Feb-17 16:35

I cannot imagine a situation where I would like to distinguish between COUNT()=0 and COUNT()=NULL. This is in contrast to AVG()=0 and AVG=NULL.

So COUNT()=0 in both cases is OK for me and saves an ugly CASE WHEN COUNT(*) IS NULL 0 ELSE COUNT(*)

Or is there an easier way like the VBA nz(...)?


Do you really want to ruin economy only to save the planet?

adamw


10,447 post(s)
#28-Feb-17 07:24

Regarding converting a NULL into something useful - use COALESCE.

Try running this in the Command Window (? outputs the result of an expression, it's a special command in the Command Window):

?COALESCE(NULLNULL, 1/0, NULL, 5, 8)

The function walks through the arguments and returns the first non-NULL value. The first four values are NULLs and are skipped, the fifth produces the result, the sixth is ignored.

KlausDE

6,410 post(s)
#28-Feb-17 14:17

Thx. I should know. It's in Mfd 8, too, but I've never used it.


Do you really want to ruin economy only to save the planet?

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.