Re: Counting propositions

From: Jonathan Leffler <>
Date: Mon, 14 Jun 2004 15:48:33 GMT
Message-ID: <lNjzc.160$>

x wrote:

> "Laconic2" <> wrote:

>>"x" <> wrote:
>>> If tuples in a relational relation(ship) (or relvar) are
>>> propositions, what is the meaning of:
>>>select count(distinct *) as nr
>>>from <relvar>
>>>and the like ?
>> Before dealing with your question, we have to decode the intent
>> behind the wildcard.
>> In particular, when does the wildcard get expanded to the list of
>> all the columns? At compile time? at execution time?
>>It makes a difference.
> At compile time.
> But when is compile time ? :-)

Logically equivalent to when the query starts execution - in embedded SQL, at the time of the OPEN of a cursor.

If the system has no pre-compilation phase, then that's a given - all statements are handled that way.

If, like IBM DB2, you have an elaborate pre-compilation phase, then an automatic recompile would occur if the table had changed enough to invalidate the previous query plan - and adding a column would surely do that!

If something changes between the time of PREPARE and OPEN, then either the OPEN will fail or the query will be recompiled - so that doesn't matter either.

As a matter of good practice (and following relational theory rather than SQL practice), the answer to the query should be the same as:

SELECT COUNT(*) AS nr FROM <relvar>

because the <relvar> should have either a primary key or one or more candidate keys that enforce uniqueness without needing the DISTINCT.

Jonathan Leffler                   #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 --
Received on Mon Jun 14 2004 - 17:48:33 CEST

Original text of this message