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
|
