Subject Re: Count(id) returns null. although count should never return null...
Author Rommel Abesames
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

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
- 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

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, "Ann W. Harrison"
<aharrison@i...> wrote:
> Adam wrote:
> >
> > Just an aside, and a possible gotcha, the condition you use is
> > very efficient in Firebird
> >
> > WHERE 1=0
> >
> > will be evaluated for every record in the table (in Firebird
> > 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
> of little tricks to catch idiotic queries and make them fast, with
> benefit to queries that a reasonable programmer would ever write.
> Should we go that way? How much code - and execution time - should
> devote that effort?
> Regards,
> Ann