You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Julian Hyde <ju...@speakeasy.net> on 2006/11/13 21:06:24 UTC

Unexpected error "When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions."

The query

select "store"."store_name" as "Store Name",
    "store"."store_city" as "Store City",
    "store"."store_state" as "Store State",
    "store"."store_country" as "Store Country",
    "store"."store_sqft" as "Store Sqft",
    "store"."store_type" as "Store Type",
    "time_by_day"."month_of_year" as "Month",
    "time_by_day"."quarter" as "Quarter",
    "time_by_day"."the_year" as "Year",
    "product"."product_name" as "Product Name",
    "product"."brand_name" as "Brand Name",
    "product_class"."product_subcategory" as "Product Subcategory",
    "product_class"."product_category" as "Product Category",
    "product_class"."product_department" as "Product Department",
    "product_class"."product_family" as "Product Family",
    "promotion"."media_type" as "Media Type",
    "promotion"."promotion_name" as "Promotion Name",
    "customer"."customer_id" as "Name (Key)",
    "customer"."fullname" as "Name",
    "customer"."city" as "City",
    "customer"."state_province" as "State Province",
    "customer"."education" as "Education Level",
    "customer"."gender" as "Gender",
    "customer"."marital_status" as "Marital Status",
    "customer"."yearly_income" as "Yearly Income",
    "sales_fact_1997"."unit_sales" as "Unit Sales"
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

gives the error

SQL Exception: Column reference 'store.store_name' is invalid. When the
SELECT list contains at least one aggregate then all entries must be valid
aggregate expressions.

But the query does not contain any aggregate functions and, even curioser,
it succeeds if I remove the ORDER BY clause.

Is this a bug in derby? Is there a workaround? I'm running Derby 10.1.2.1 on
Fedora Core 5.

FYI, the query is generated by mondrian-2.2 to drill through to the set of
fact table rows underlying a particular cell in a multidimensional result.
If there is an equivalent query which doesn't encounter this bug, let me
know, and I can change mondrian's query generator.

Julian

RE: Unexpected error "When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions."

Posted by Julian Hyde <ju...@speakeasy.net>.
> 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

Re: Unexpected error "When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions."

Posted by Bryan Pendleton <bp...@amberpoint.com>.
> Is this a bug in derby? Is there a workaround? I'm running Derby 
> 10.1.2.1 on Fedora Core 5.

I don't have an explanation for the behavior you are seeing.
I spent a little bit of time trying to reproduce your problem
with a smaller query, but couldn't.

Can you post a complete example script, with full DDL for the tables,
that demonstrates the problem?

thanks,

bryan