[Mondrian] Re: Regression tests MonetDB
Julian Hyde
jhyde at pentaho.com
Mon Feb 23 20:59:46 EST 2009
Yes, mondrian generates sum and count in the order by clause for mdx
queries that involve topcount over a measure. The code that does this
is in RolapNativeTopCount.
I can see that not all SQL engines would support this, so we could add
'boolean supportsOrderByAggregate()' if necessary.
Looks like LucidDB already supports it, John. :)
Julian
On Feb 23, 2009, at 4:55 PM, Stefan de Konink <stefan at konink.de> wrote:
> John V. Sichi wrote:
>> Stefan de Konink wrote:
>>> Maybe you are better than google, but do you by any chance know
>>> where we can find the syntax requirement for the following:
>>>
>>> select "store"."store_country" as "c0", "store"."store_state" as
>>> "c1", "store"."store_city" as "c2" from "store" as "store",
>>> "sales_fact_1997" as "sales_fact_1997", "time_by_day" as
>>> "time_by_day" 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 group
>>> by "store"."store_country", "store"."store_state",
>>> "store"."store_city" order by
>>>
>>> count("sales_fact_1997"."product_id") DESC,
>>>
>>> "store"."store_country" ASC, "store....
>>>
>>>
>>> It is the count part in the order by section.
>> I've never seen Mondrian generate an agg call inside the ORDER
>> BY...what MDX and schema did you use to make it do that?
>
> 5) queryTest_29ce2f3512f79d38_TOPCOUNT.xml
>
> (Attached for your convenience)
>
>
> If you really want to see some incredble weird stuff take a look in:
>
> 11) queryTest_aggregate_ffc012281623093.xml
>
> Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal
> error: while building member cache; sql=[select
> "store"."store_country" as "c0" from "store" as "store" where
>
> UPPER("store"."store_country") = UPPER('20319')
>
> group by "store"."store_country" order by "store"."store_country" ASC]
>
> sql>select store_country from store;
> +---------------+
> | store_country |
> +===============+
> | USA |
> | Mexico |
> | USA |
> | USA |
> | Mexico |
> | Mexico |
> | USA |
> | USA |
> | Mexico |
> | Mexico |
> | Mexico |
> | USA |
> | Mexico |
> | USA |
> | USA |
> | USA |
> | USA |
> | USA |
> | Mexico |
> | Canada |
> | Canada |
> | Mexico |
> | USA |
> | USA |
> | USA |
> +---------------+
>
>
> Stefan
> WITH MEMBER [Store].[Other Cities] AS '([Store].[All Stores],
> Measures.CURRENTMEMBER) - SUM(TOPCOUNT([Store].[Store City].MEMBERS,
> 12, [Sales Count]), Measures.CURRENTMEMBER)' SELECT {Measures.[Sales
> Count], Measures.[Store Cost], Measures.[Store Sales], Measures.
> [Unit Sales], Measures.[Customer Count]} ON COLUMNS,
> {TOPCOUNT([Store].[Store City].MEMBERS, 12, [Sales Count]), [Store].
> [Other Cities]} ON ROWS FROM [Sales] [Time] [Product] [Store Size in
> SQFT] [Store Type] [Promotions] [Customers] [Education Level]
> [Marital Status] [Yearly Income] [Promotion Media] [Gender] [Time].
> [1997] [Product].[All Products] [Store Size in SQFT].[All Store Size
> in SQFTs] [Store Type].[All Store Types] [Promotions].[All
> Promotions] [Customers].[All Customers] [Education Level].[All
> Education Levels] [Marital Status].[All Marital Status] [Yearly
> Income].[All Yearly Incomes] [Promotion Media].[All Media] [Gender].
> [All Gender] [Measures] [Measures].[Sales Count] [Measures].[Store
> Cost] [Measures].[Store Sales] [Measures].[Unit Sales] [Measures].
> [Customer Count] [Store] [Store].[All Stores].[USA].[OR].[Salem]
> [Store].[All Stores].[USA].[WA].[Tacoma] [Store].[All Stores].[USA].
> [OR].[Portland] [Store].[All Stores].[USA].[CA].[Los Angeles]
> [Store].[All Stores].[USA].[CA].[San Diego] [Store].[All Stores].
> [USA].[WA].[Seattle] [Store].[All Stores].[USA].[WA].[Bremerton]
> [Store].[All Stores].[USA].[WA].[Spokane] [Store].[All Stores].[USA].
> [CA].[Beverly Hills] [Store].[All Stores].[USA].[WA].[Yakima]
> [Store].[All Stores].[USA].[WA].[Bellingham] [Store].[All Stores].
> [USA].[WA].[Walla Walla] [Store].[Other Cities] 13347.0 34823.5566
> 87218.28 41580.0 474.0 11184.0 29959.2813 74843.96
> 35257.0 278.0 8264.0 21948.944 55058.79 26079.0 563.0 8207.0
> 21771.536 54545.28 25663.0 1147.0 8095.0 21713.5328
> 54431.14 25635.0 962.0 7956.0 20956.8025 52644.07
> 25011.0 906.0 7876.0 21121.9631 52896.3 24576.0 179.0 7397.0
> 19795.491 49634.46 23591.0 84.0 6815.0 18266.4404 45750.24 21333.0
> 1059.0 3652.0 9713.813 24329.23 11491.0 95.0 1380.0
> 1896.6174 4739.23 2237.0 190.0 1339.0 1880.3396 4705.97 2203.0 96.0
> 1325.0 1778.9159 4441.18 2117.0 -452.0
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
More information about the Mondrian
mailing list