#25 Optimizer loses fields used in restrict

closed-fixed
nobody
optimizer (1)
9
2004-11-11
2004-11-11
No

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)

Discussion

  • Bjorn Bringert

    Bjorn Bringert - 2004-11-11

    Logged In: YES
    user_id=123762

    pushRestrict sometimes pushed restrictions into the wrong
    branch of binary operations because of a typo. Bug located
    by Lemmih, fixed in CVS.

     
  • Bjorn Bringert

    Bjorn Bringert - 2004-11-11
    • status: open --> open-fixed
     
  • Bjorn Bringert

    Bjorn Bringert - 2004-11-11
    • status: open-fixed --> closed-fixed
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks