[Haskelldb-users] Bug: over-eager query optimizer

[Volker W. <po...@vo...>]

Hi.

I've stumbled into another bug, in version 0.9.

I want to query a table for whether a certain row exists. I don't want to
actually return anything. Since the query needs a project, I return a
constant as a dummy value. This is my query:

      erg <- query db $ do tab <- table T.t
                           restrict (       tab!T.praed .==. constant 1
                                      .&&.  tab!T.arg0 .==. constant 2
                                    )
                           project (T.praed << constant 0)

The query always returns the constant, regardless of whether the row
exists. It should only return a result when the row exists:

mysql> select 0 as praed;
+-------+
| praed |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select 0 as praed from t where praed = 1 and arg0 = 2;
Empty set (0.01 sec)

mysql> insert into t (praed, arg0) values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select 0 as praed from t where praed = 1 and arg0 = 2;
+-------+
| praed |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)


I've traced the problem to Database.lazyQuery, and found that the actual
query is optimized away. The unoptimised query is (why so compliated?):

Project [("praed",AttrExpr "praed2")] (Project [("praed2",ConstExpr
(IntegerLit 0)),("praed1",AttrExpr "praed1"),("arg01",AttrExpr
"arg01"),("daten1",AttrExpr "daten1")] (Restrict (BinExpr OpAnd (BinExpr
OpEq (AttrExpr "praed1") (ConstExpr (IntegerLit 1))) (BinExpr OpEq
(AttrExpr "arg01") (ConstExpr (IntegerLit 2)))) (Project
[("praed1",AttrExpr "praed"),("arg01",AttrExpr "arg0"),("daten1",AttrExpr
"daten")] (BaseTable "t" ["praed","arg0","daten"]))))

SELECT DISTINCT praed2 as praed
FROM  (SELECT DISTINCT 0 as praed2,
                       praed1,
                       arg01,
                       daten1
       FROM  (SELECT DISTINCT praed as praed1,
                              arg0 as arg01,
                              daten as daten1
              FROM  t as T1) as T1
       WHERE ((praed1 = 1) AND (arg01 = 2))) as T1


The optimised query is:

Project [("praed",ConstExpr (IntegerLit 0))] Empty

SELECT DISTINCT 0 as praed


Regards,
Volker

--
Volker Wysk <po...@vo...>
http://www.volker-wysk.de