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
|
