>> Hi all,
>>
>> There are cases that constant evaluation come in handy in queries. One is
>> when you want to remove a condition from your query:
>>
>> select [fields] from [table]
>> where (f1 = :cond1 or 1=:cond2)
>> and (f2 = :cond3 or 1=:cond4)
>> and (f3 = :cond5 or 1=:cond6)
>>
>>assume that 'table' is a huge table and you want to give user the
ability
>> to filter data on whatever column she wants, and save yourself from
>> building a query each time.
>That smacks me as pure laziness -- it would not take any significant
>effort to write a routine to build a valid query based on the filter
>criteria provided.
There are times you can not build queries, for example in PSQL (and it is not a good practice) Also, you may not have enough permission/access/privilege to do so in 3-tier applications. And above all, you may be using Delphi/Datasnap or another nice framework, and want to use parameters instead of sending plain queries which are prone to be injected, and provide information about your metadata structure for a sniffing third-party when packet encryption is not possible/viable/desired.
>You are saying that you don't want the engine/optimizer to advance
>because you have been too lazy in your implementation.
I'm confused. Did you think I was against query optimization?! I was totally for it.
To dear Firebird developers:
Does Firebird engine do short-circuit boolean evaluation? For example in
(f1 = 9) or (f2 > 5)
assuming that f1 is 9, does it evaluate the second expression (b>5) or bypasses it?
All the best,
Khosrow
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

> -----Original Message-----
> From: K. A. [mailto:parshua@...]
> Hi all,
>
> There are cases that constant evaluation come in handy in queries. One
is
> when you want to remove a condition from your query:
>
> select [fields] from [table]
> where f1 = :cond1 or 1=:cond2
> and f2 = :cond3 or 1=:cond4
> and f3 = :cond5 or 1=:cond6
>
> assume that 'table' is a huge table and you want to give user the
ability
> to filter data on whatever column she wants, and save yourself from
> building a query each time.
That smacks me as pure laziness -- it would not take any significant
effort to write a routine to build a valid query based on the filter
criteria provided.
You are saying that you don't want the engine/optimizer to advance
because you have been too lazy in your implementation.
It's one thing to say that you are using A+0=:Param because the
optimizer chooses bad indexes, but you are talking about something
completely different.
Sean

Hi all,
There are cases that constant evaluation come in handy in queries. One is when you want to remove a condition from your query:
select [fields] from [table]
where f1 = :cond1 or 1=:cond2
and f2 = :cond3 or 1=:cond4
and f3 = :cond5 or 1=:cond6
assume that 'table' is a huge table and you want to give user the ability to filter data on whatever column she wants, and save yourself from building a query each time.
You can eliminate any condition from the query by setting the :cond2, :cond4 or :cond6 to 1.
There are many other cases in which a smart code may produce a 'dumb!' looking query to add functionality, simplicity or productivity. (Most of them are specially useful in debugging)
I think the "out-smarting" approach is not the solution to
development issues, specially optimization!
>I'd suggest us not trying to distinguish anything explicitly. Let's just
>create a code that separates the constant boolean sub-expression from
>the composite predicate and evaluates it beforehand (either places that
>sub-expression into the artificial inner-most boolean RSB or introduces
>some other "first phase" execution branch). As for the "hint vs real"
>expression handling, I'd prefer to leave it "as is", i.e. handle it
>naturally.
Totally agree.

Roman Simakov wrote:
>
> Thank you. It's what i understand and know.
Okay, sorry for lecturing then :-)
> Disabling index is the other case than i consider. I suggest we can
> distinguish these cases and return EOF in one case (when all
> condition is false) and disable index in other cases. This keeps
> exists behavior and should not break exists applications using it.
I'd suggest us not trying to distinguish anything explicitly. Let's just
create a code that separates the constant boolean sub-expression from
the composite predicate and evaluates it beforehand (either places that
sub-expression into the artificial inner-most boolean RSB or introduces
some other "first phase" execution branch). As for the "hint vs real"
expression handling, I'd prefer to leave it "as is", i.e. handle it
naturally.
Dmitry

Leyne, Sean wrote:
> Why wouldn't a "field+constant=value" to "field = value - constant"
> optimization be beneficial?
If the index on "field" is bad for a query.
> I think that there could be some boundaries/rules which could be applied
> to balance off the need for "hinting out" bad indexes as well as
> handling dumb user queries.
>
> Example:
>
> field+0=value --> Hint out
>
> field+30=value --> field = value -30 -- > optimization to enable index
> usage
>
> field||''=value --> Hint out
Yes, it could work. But basically, I agree with Ann that we should not
over complicate the optimization process, especially via introducing any
guess based approaches :-)
Dmitry