haskelldb-users

[Haskelldb-users] composing relational operations with aggregate expressions

[Matthias R. <mat...@so...>]

I am trying to construct a haskelldb query that returns the count of the
ids in one table that are not present in another table. Here's what I
came up with:

test1Query =
    do t <- table Table1.table1
       project (idField << t1!Table1.xid)

test2Query =
    do t <- table Table2.table2
       project (idField << t2!Table2.xid)

test3Query =
    do ids <- minus test1Query test2Query
       project (countField << count (ids!idField))

Unfortunately this is not producing the right SQL. showSql returns

(SELECT DISTINCT COUNT(id) as count
 FROM  table1 as T1)
EXCEPT
(SELECT DISTINCT COUNT(id) as count
 FROM  table2 as T1)

The same happens for other combinations of relational operations with
aggregate expressions, e.g. 'union' combined with '_min'. In all cases
the aggregate expression gets pushed under the relational operation,
which is just plain wrong.

Am I not constructing my queries the right way or is this a bug?


Matthias.
PS: Is the haskelldb bug tracker at
http://sourceforge.net/tracker/?group_id=101095&atid=629040 still in
use? There hasn't been any activity on it for nearly 18 months.

Re: [Haskelldb-users] composing relational operations with aggregate expressions

[Matthias R. <mat...@so...>]

Matthias Radestock <mat...@so...> writes:

> test1Query =
>     do t <- table Table1.table1
>        project (idField << t1!Table1.xid)
>
> test2Query =
>     do t <- table Table2.table2
>        project (idField << t2!Table2.xid)

Small correction: The projections should read ... <<t!....


Matthias.

Re: [Haskelldb-users] composing relational operations with aggregate expressions

[Bjorn B. <bri...@cs...>]

On Jul 11, 2006, at 2:38 PM, Matthias Radestock wrote:

> I am trying to construct a haskelldb query that returns the count =20
> of the
> ids in one table that are not present in another table. Here's what I
> came up with:
>
> test1Query =3D
>     do t <- table Table1.table1
>        project (idField << t1!Table1.xid)
>
> test2Query =3D
>     do t <- table Table2.table2
>        project (idField << t2!Table2.xid)
>
> test3Query =3D
>     do ids <- minus test1Query test2Query
>        project (countField << count (ids!idField))
>
> Unfortunately this is not producing the right SQL. showSql returns
>
> (SELECT DISTINCT COUNT(id) as count
>  FROM  table1 as T1)
> EXCEPT
> (SELECT DISTINCT COUNT(id) as count
>  FROM  table2 as T1)
>
> The same happens for other combinations of relational operations with
> aggregate expressions, e.g. 'union' combined with '_min'. In all cases
> the aggregate expression gets pushed under the relational operation,
> which is just plain wrong.
>
> Am I not constructing my queries the right way or is this a bug?

That sure seems like a bug. Try changing line 187 in Optimize.hs to

           	=3D not (any (isAggregate.snd) assoc)

Let me know if that helps and I'll fix it in darcs.

> Matthias.
> PS: Is the haskelldb bug tracker at
> http://sourceforge.net/tracker/?group_id=3D101095&atid=3D629040 still =
in
> use? There hasn't been any activity on it for nearly 18 months.

It's still in use in the sense that most of those bugs still haven't =20
been fixed. It's not in use in the sense that noone has reported any =20
new bugs since then. It's basically a result of the lack of users and =20=

releases. Which in turn probably depends on a lack of developer time. =20=

Basically we would only enter stuff there if it couldn't be fixed =20
right away.

/Bj=F6rn

Re: [Haskelldb-users] composing relational operations with aggregate expressions

[Matthias R. <mat...@so...>]

Bjorn,

Bjorn Bringert <bri...@cs...> writes:

> Try changing line 187 in Optimize.hs to
>
>           	= not (any (isAggregate.snd) assoc)
>
> Let me know if that helps and I'll fix it in darcs.

That seems to do the trick. Thanks! I now get

SELECT DISTINCT count6 as count
FROM  (SELECT DISTINCT COUNT(id5) as count6
       FROM  ((SELECT DISTINCT id as id5
               FROM  table1 as T1)
              EXCEPT
              (SELECT DISTINCT id as id5
               FROM  table2 as T1)) as T1) as T1

The outer select is unnecessary but harmless.


Matthias

Re: [Haskelldb-users] composing relational operations with aggregate expressions

[Bjorn B. <bri...@cs...>]

On Jul 11, 2006, at 3:33 PM, Matthias Radestock wrote:

> Bjorn Bringert <bri...@cs...> writes:
>
>> Try changing line 187 in Optimize.hs to
>>
>>           	=3D not (any (isAggregate.snd) assoc)
>>
>> Let me know if that helps and I'll fix it in darcs.
>
> That seems to do the trick. Thanks! I now get
>
> SELECT DISTINCT count6 as count
> FROM  (SELECT DISTINCT COUNT(id5) as count6
>        FROM  ((SELECT DISTINCT id as id5
>                FROM  table1 as T1)
>               EXCEPT
>               (SELECT DISTINCT id as id5
>                FROM  table2 as T1)) as T1) as T1
>
> The outer select is unnecessary but harmless.

Thank you for the report, the change is in darcs now.

/Bj=F6rn