db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde" <julianh...@speakeasy.net>
Subject RE: Unexpected error "When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions."
Date Tue, 14 Nov 2006 00:14:40 GMT
> 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


Mime
View raw message