I would agree that adding one-off tricks to handle optimization of
"stupid" queries is a short term and undesirable solution overall.
However, I would like the optimizer gain more "semantic" intelligence
for such things as:

1) constraints
- If I have a constraint on a column "column1 > 10" and I have a query
"SELECT .. FROM table WHERE column1 = 5" then the optimizer should
consider this a no-op.
- "column1 = 5" need not necessarily be written explicitly. It can be
implicitly determined as a result of prior evaluation/execution steps
of the query
- of course there is an issue with constraints not being enforced for
existing rows when the constraint is created...but thats another
issue.

2) rule-sets or row-level security mechanisms which can be considered
a form of constraints
- a user or group has a restriction on table Employee "Position <>
'Manager'" . This user then issues a query "SELECT ..Employee WHERE...
AND Position = 'Manager'..."
- again, no-op

3) evaluation of UNION views (ala MS SQL Server's distributed
partitioned views) together with constraints in #1
- you have a view "CREATE VIEW HistoricalView AS SELECT .. FROM
CurrentData UNION SELECT ... FROM HistoricalData"
- you have constraints on table CurrentData "EXTRACT(YEAR FROM
TransactionDate) = EXTRACT(YEAR FROM CURRENT_TIMESTAMP)" and on table
HistoricalData "EXTRACT(YEAR FROM TransactionDate) < EXTRACT(YEAR FROM
CURRENT_TIMESTAMP)"
- a "SELECT...FROM HistoricalView WHERE (some_condition_in_year_2005)"
should just evaluate to accessing CurrentDate and should not need to
touch HistoricalData. An index on TransactionDate should not be
necessary

I have more scenarios in mind if anyone is interested =)

I would support any effort to bring Firebird to this level but not
thru hacks or kludges. I can imagine this would automatically solve
those "1=0" hacks.

That level of intelligence in an optimizer is not just "academic".

Should this be posted to firebird-architect as well?

--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> Adam wrote:
> >
> > Just an aside, and a possible gotcha, the condition you use is

not

> > very efficient in Firebird
> >
> > WHERE 1=0
> >
> > will be evaluated for every record in the table (in Firebird

1.5.2,

> > not sure about later versions).
> >
> > Hopefully some time in the future Firebird will be optimised to
> > improve this, but till then, this works a treat.
> >
>
> That's actually an interesting question. Academic optimizers are

full

> of little tricks to catch idiotic queries and make them fast, with

no

> benefit to queries that a reasonable programmer would ever write.
> Should we go that way? How much code - and execution time - should

we

> devote that effort?
>
>
> Regards,
>
>
> Ann