[Haskelldb-users] [ haskelldb-Bugs-1064382 ] Optimizer loses fields used in restrict

[SourceForge.net <no...@so...>]

Bugs item #1064382, was opened at 2004-11-11 10:19
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=629040&aid=1064382&group_id=101095

Category: optimizer
Group: None
Status: Open
Resolution: None
Priority: 9
Submitted By: Bjorn Bringert (bring)
Assigned to: Nobody/Anonymous (nobody)
Summary: Optimizer loses fields used in restrict

Initial Comment:
Reported by Lemmih:

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)


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=629040&aid=1064382&group_id=101095