[Haskelldb-users] Bug in the optimizer.

[Lemmih <le...@gm...>]

Hey,

I'm having trouble with the optimizer when doing a one-to-many mapping.

My database structure looks like this:
mappingTable = makeTInfo "mapping"
               [makeCInfo "topic" (StringT,False),
                makeCInfo "mid" (IntT,False),
                makeCInfo "outid" (IntT,False)]
optionsTable = makeTInfo "options"
               [makeCInfo "optid" (IntT,False),
                makeCInfo "optname" (StringT,False),
                makeCInfo "optvalue" (StringT,False)]

[Queries]
unoptimized
    = ppSql . toSql . runQuery
getMappings what idcode
    = do t <- table mapping
         restrict (t!topic .==. what .&&. t!mid .==. idcode)
         project (outid << t!outid)
getOptions what gid
    = do t <- table options
         m <- getMappings what gid
         restrict (t!optid .==. m!outid)
         project (optname << t!optname # optvalue << t!optvalue)

'unoptimized (getOptions (constant "") (constant 0))' generates the
somewhat verbose but correct SQL:
SELECT DISTINCT optname4 as optname,
                optvalue4 as optvalue
FROM  (SELECT DISTINCT optname1 as optname4,
                       optvalue1 as optvalue4,
                       outid3,
                       topic2,
                       mid2,
                       outid2,
                       optid1,
                       optname1,
                       optvalue1
       FROM  (SELECT DISTINCT outid2 as outid3,
                              topic2,
                              mid2,
                              outid2,
                              optid1,
                              optname1,
                              optvalue1
              FROM  (SELECT DISTINCT topic as topic2,
                                     mid as mid2,
                                     outid as outid2
                     FROM  mapping as T1) as T1,
                    (SELECT DISTINCT optid as optid1,
                                     optname as optname1,
                                     optvalue as optvalue1
                     FROM  options as T1) as T2
              WHERE ((topic2 = '') AND (mid2 = 0))) as T1
       WHERE (optid1 = outid3)) as T1

but 'getOptions (constant "") (constant 0)' generates the following
incorrect SQL where 'topic2' isn't selected anywhere:
SELECT DISTINCT optname1 as optname,
                optvalue1 as optvalue
FROM  (SELECT DISTINCT optid as optid1,
                       optname as optname1,
                       optvalue as optvalue1
       FROM  options as T1
       WHERE ((topic2 = '') AND (mid2 = 0))) as T1,
      (SELECT DISTINCT outid as outid2
       FROM  mapping as T1) as T2
WHERE (optid1 = outid2)

Friendly,
  Lemmih