Ole,

<< Seems to be an issue with SMALLINT (the column type of "Order
Details".Quantity). A cast to any other numeric type returns the correct
values, e.g.: >>

Yep, it's ignoring SMALLINT values in SUM, RUNSUM, and AVG.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< "ElevateDB does not optimize join expressions in the WHERE clause,
otherwise known as SQL-89 style joins. You must use the JOIN clause in order
to have  ElevateDB optimize the joins." >>

OK, that's a precise description of the behavior.

I see now that the behavior is also documented in the SQL Manual, under the
SELECT statement:

<quote>
Note
If you specify multiple tables in the FROM clause without specifying JOIN
clauses between all of them, then the tables without applicable JOIN clauses
will be joined using a CROSS JOIN, which is a join that joins every row from
the source table to every row in the target table.  This produces a
cartesian product of both tables, and even very small tables can result in
very large result sets, so one should be careful to ensure that join
conditions are always specified for all tables in the SELECT statement.
</quote>

So I guess what happened with my old-style join was that the FROM clause
produced a large cartesian join *before* any filter conditions in the WHERE
clause took place. That would explain the 18-19 minutes.

Ole Willy Tuv

Ole,

<< So I guess what happened with my old-style join was that the FROM clause
produced a large cartesian join *before* any filter conditions in the WHERE
clause took place. That would explain the 18-19 minutes. >>

Yep.  The issue with keeping the SQL-89 style joins in place is that they
are very dicey when trying to arrange them in a fashion so that the engine
can perform optimized nested-loop processing on them without getting into
circular references, etc.   It was one area where DBISAM had a very large
number of bugs, and so I had to decide whether to keep that type of
optimization in place, or to simply punt and state that you have to use the
newer style of joins to get the optimization.  Since the results are the
same in both cases and its only an optimization issue, I decided that it was
best to make the product as stable as possible out of the gate.

--
Tim Young
Elevate Software
www.elevatesoft.com