Re: [Haskelldb-users] Querying an empty table

[Bjorn B. <d00...@dt...>]

Gregory Wright wrote:
> I have a question about how to query an empty table. (I have to query the
> table to find the maximum value of a field; if there are any entries at 
> all the
> field is non-null.)
> 
> I used
> 
> last  = do
>             events <- table event
>             restrict (events!sid .==. constant 1)
>             u <- project (cid << _max(events!cid))
>             return u
> 
> 
> The fields are sid and cid, and the goal is to find the largest value
> of cid. Since cid is non-null as a type, this gives an error if the table
> is empty.
> 
> If there are any entries at all in the table, there are over 100,000,
> so I don't want to return the whole table just to see if it is non-empty.
> 
> My question: is there a way to simply have the query return empty
> list if the table is empty, in a way that typechecks, or

You have found an problem is HaskellDB's type system that I don't think
we were aware of. The result type of _max, _min, avg etc. should be 
nullable,
since they seem to return NULL if the relation is empty. We'll look into 
that.

One way of solving your problem is to do something like (not tested):

-- return the max value of the expression, or 0 if the relation is empty
safeMax e = _case [(count e .>. 0, _max e)] (constant 0)

last  = do
             events <- table event
             restrict (events!sid .==. constant 1)
             u <- project (cid << safeMax (events!cid))
             return u


> is it just as efficient to do a lazy query (no "project"), see if the head
> is non-empty and then repeat the query with the projection? Or is
> there some better way of structuring this that I haven't learned yet?

In theory, the lazy approach should work, but there are problems with the
implementation of lazy queries which would make this inefficient.

> By the way, the previous advice about using _default for auto-increment
> fields works perfectly with PostgreSQL v. 7.4.2. Thanks for the hint. I'm
> slowly getting better at finding examples in the test programs but
> I'm still very much an HDB newbie.

Requiring users to plow through undocumented demo programs to find 
anything but
the basic features is not very good. Mary is working on a tutorial, but 
I'm guessing
that she probably won't get to features like auto increment columns for 
a while.
You are welcome to contribute things that you find out to the AvianWiki 
tutorial
that is linked from the haskelldb web site.

/Bjorn