Re: [Haskelldb-users] Querying an empty table

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

Hi Bjorn,

I tried the suggest method and it didn't typecheck. (_case requires 
Expr,
not ExprAggr). This seems unnecessarily restrictive; I would expect 
_case
to be able to take both an Expr and ExprAggr in the conditional 
position.

For my program I have a workaround, but in developing it I ran into an
interesting question.  (The workaround is just to query the count first,
and if it is nonzero query for the maximum element.)

Assume I have a database with a single table, all of whose fields are 
Strings.
In hdb, how can I count the number of records matching a condition? The
query would look something like

myquery = do
         t  <- table mytab
         restrict (t!myfield .==. constant the_interesting_field_value)
         u <- project ( <this is the problem>  << count(t!myfield))
         return u

The projection is where the trouble is again. (I understand that I could
return the entire list of match records and take the length, but if the 
list
is long and all I want is the count, that is unreasonably inefficient.)

Since we don't have a field of type Int, how do we return the count?

The solution may be straightforward, since we just need a way to easily 
make
a record with known fields. Something like

         project(returnAs("foo", IntT, True) << count(t!myfield))

which would result in the query

         SELECT COUNT(myfield) AS foo ...

This could also solve the problem of _max and _min returning nothing on
empty tables, if the column named in returnAs were nullable.

Is it possible to declare a fake table (not present in the database) and
use it as a field in project? Then I could say

         project (fakecol << count(t!myfield))

where fakecol was a column of type nullable Int. That might would be
easy, and it would be easy to explain that DBDirect automatically made
row declarations for each table in the database, but to return synthetic
rows you have to declare them yourself.

  Thank for your help.

Best Wishes,
Greg



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
>