Alexander Gräf wrote:

>
>
> Every database (it does not need to be a relational database) has a
> problem with too many records. So selecting 80,000 records, and then
> joining and grouping them is a lot of mork in many cases.

In this case, it's not the 80 thousand rows that worries me. To get
those 80 thousand, the application is reading and sorting ten million
records. However, if it works in the particular instance...

> ...This wont give you any rows, no matter what data you have in the table.
> A smart optimizer would execute this query in 0 seconds, because
> weighted_value cannot be greater than weighted_value. But because it
> is not common to create queries which wont return anything, most

optimizers

> are not used to see those void queries, and will happily execute them.

The question of optimizing for dumb queries comes up more often than you
might think. Is it smart for the system to carry around a lot of code
to catch and optimize queries so bad as to be pointless? I'd argue that
it isn't.

>
> It wont be executed if link is not a primary key or has no unique and
> not null constraint, and I'm not sure if Firebird will execute this
> at all, because there could be the possibility of the subselect
> returning more than one row, or zero rows (dont know if Firebird detects
> this from the constraints on link).

Firebird detects that multiple results exist on a singleton select by
continuing to select after the first results are returned. Thus if you
happen to have only one record that qualifies, the singleton select
succeeds. With different data, the same query will fail. I believe
that is the correct (standard) way evaluate a singleton select.


Regards,


Ann