|
substr functions fails with an aggregate function or with GROUP BY cluase with set oracle sql_mode
note: not observed with dev v1.1.7-1
set sql_mode=oracle and run the queries
MariaDB [(none)]> select @@sql_mode;
----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
----------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)
# mcsmysql -u root -p1 tpcds_1<<rr
|
> select ca_zip
|
> ,sum(cs_sales_price)
|
> from catalog_sales
|
> ,customer
|
> ,customer_address
|
> ,date_dim
|
> where cs_bill_customer_sk = c_customer_sk
|
> and c_current_addr_sk = ca_address_sk
|
> and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
|
> '85392', '85460', '80348', '81792')
|
> or ca_state in ('CA','WA','GA')
|
> or cs_sales_price > 500)
|
> and cs_sold_date_sk = d_date_sk
|
> and d_qoy = 2 and d_year = 2001
|
> group by ca_zip
|
> order by ca_zip
|
> limit 100;
|
> rr
|
ERROR 1815 (HY000) at line 1: Internal error: IDB-1001: Function 'substr_oracle' can only be used in the outermost select or order by clause and cannot be used in conjunction with an aggregate function.
|
[root@columnstore validate_mdb_query_templates]#
|
|
select
|
substr(w_warehouse_name,1,20)
|
,sm_type
|
,cc_name
|
,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days"
|
,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and
|
(cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days"
|
,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and
|
(cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days"
|
,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and
|
(cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days"
|
,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 120) then 1 else 0 end) as ">120 days"
|
from
|
catalog_sales
|
,warehouse
|
,ship_mode
|
,call_center
|
,date_dim
|
where
|
d_month_seq between 1200 and 1200 + 11
|
and cs_ship_date_sk = d_date_sk
|
and cs_warehouse_sk = w_warehouse_sk
|
and cs_ship_mode_sk = sm_ship_mode_sk
|
and cs_call_center_sk = cc_call_center_sk
|
group by
|
substr(w_warehouse_name,1,20)
|
,sm_type
|
,cc_name
|
order by substr(w_warehouse_name,1,20)
|
,sm_type
|
,binary cc_name
|
limit 100;
|
|
|
ERROR 1178 (42000) at line 2: The storage engine for the table doesn't support IDB-2016: Non supported item 'substr(w_warehouse_name,1,20)' on the GROUP BY list.
|
|
|
|