[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