> Bryan Pendleton wrote:
>
> Can you post a complete example script, with full DDL for the tables,
> that demonstrates the problem?
My mistake. Mondrian mis-reported the SQL statement which was in error. The
actual statement contained only "count(*)" in the select clause, yet
referenced a number of columns in the ORDER BY clause.
The actual query was
select count(*)
from "store" as "store",
"sales_fact_1997" as "sales_fact_1997",
"time_by_day" as "time_by_day",
"product" as "product",
"product_class" as "product_class",
"promotion" as "promotion",
"customer" as "customer"
where "sales_fact_1997"."store_id" = "store"."store_id"
and "sales_fact_1997"."time_id" = "time_by_day"."time_id"
and "time_by_day"."the_year" = 1997
and "sales_fact_1997"."product_id" = "product"."product_id"
and "product"."product_class_id" = "product_class"."product_class_id"
and "sales_fact_1997"."promotion_id" = "promotion"."promotion_id"
and "sales_fact_1997"."customer_id" = "customer"."customer_id"
and "customer"."city" = 'Berkeley'
and "customer"."state_province" = 'CA'
order by "store"."store_name" ASC,
"store"."store_city" ASC,
"store"."store_state" ASC,
"store"."store_country" ASC,
"store"."store_sqft" ASC,
"store"."store_type" ASC,
"time_by_day"."month_of_year" ASC,
"time_by_day"."quarter" ASC,
"time_by_day"."the_year" ASC,
"product"."product_name" ASC,
"product"."brand_name" ASC,
"product_class"."product_subcategory" ASC,
"product_class"."product_category" ASC,
"product_class"."product_department" ASC,
"product_class"."product_family" ASC,
"promotion"."media_type" ASC,
"promotion"."promotion_name" ASC,
"customer"."customer_id" ASC,
"customer"."fullname" ASC,
"customer"."city" ASC,
"customer"."state_province" ASC,
"customer"."education" ASC,
"customer"."gender" ASC,
"customer"."marital_status" ASC,
"customer"."yearly_income" ASC
The testcase can be reduced to
select count(*)
from "store" as "store"
order by "store"."store_name"
As it happens, Oracle and MySQL both execute this query without error, which
is why I suspected that Derby's behavior was wrong. But by my reading of the
SQL standard, Derby is correct in rejecting this statement.
Julian
