Re: Design: Escort info from WHERE clause to executor?

<peter(dot)trautmeier(at)gmx(dot)de> writes:

>> Why? What are you trying to achieve?
>
> I am implementing a technique that sorts a result set according to weight annotations in the WHERE.
>
> The query
>
> SELECT * FROM cars
> WHERE (cdChanger=1){2}
> OR (mp3player=1){1}
>
> would be sorted according to partial conditions that hold.
>
> Cars that have both a CD changer AND a MP3 player get a weight of 3, i.e. (2+1).
> Cars that only have a CD changer get a weight of 2.
> Cars that only have a MP3 player get a weight of 1.
> Cars that have neither a CD changer nor a MP3 player do not belong to the result set anyway.

You're trying to do it by annotating the clauses early on in the parse stage,
and then looking at the annotations in the executor. But I think you'll find
that there are too many steps in between those two which risk destroying or
making it impossible to make heads or tails of the annotations.

An alternative approach would be to notice the annotations early in the parse
stage and construct copies of those expressions to put into a constructed
ORDER BY clause. Then allow the planner, optimizer, and executor to proceed as
normal. This means it may have to re-evaluate those expressions twice, once
for the query and once for the ordering.

So in the above case it would construct an ORDER BY clause like:
ORDER BY
((CASE WHEN cdChanger=1 THEN 2 ELSE 0 END) +
(CASE WHEN mp3Player=1 THEN 1 ELSE 0 END))

Then it can forget about the annotations and allow them to get destroyed.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com