Re: [Haskelldb-users] Querying an empty table

[Gregory W. <gw...@co...>]

On May 9, 2004, at 7:21 PM, Bjorn Bringert wrote:

> 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
>

I'll give that a try in the morning. (I have to give baby a bath now.)


>
>> 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.
>

I will do that!  I appreciate your generous help: thank you and get 
some sleep!

Best,
Greg


> /Bjorn
>