> On Oct 18, 2017, at 11:48 PM, 张赵中 <[email protected]> wrote:
>
> hi, all: there is a operate + of strings in q84.sql of TPC-DS.
No, there isn’t. Here is query84.tpl from TPC-DS spec (omitting the headers)
[1]:
define CITY = dist(cities, 1, large);
define INCOME = random(0, 70000, uniform);
define _LIMIT=100;
[_LIMITA] select [_LIMITB] c_customer_id as customer_id
,c_last_name || ', ' || c_first_name as customername
from customer
,customer_address
,customer_demographics
,household_demographics
,income_band
,store_returns
where ca_city = '[CITY]'
and c_current_addr_sk = ca_address_sk
and ib_lower_bound >= [INCOME]
and ib_upper_bound <= [INCOME] + 50000
and ib_income_band_sk = hd_income_band_sk
and cd_demo_sk = c_current_cdemo_sk
and hd_demo_sk = c_current_hdemo_sk
and sr_cdemo_sk = cd_demo_sk
order by c_customer_id
[_LIMITC];
It uses ‘||’ for string concatenation, per the SQL standard.
You are probably looking at a version of the query that someone has hand-edited
for a particular database.
> To run this case, program throws exeception as
> follows:org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply
> '+' to arguments of type '<VARCHAR(2)> + <CHAR(2)>'. Supported form(s):
> '<NUMERIC> + <NUMERIC>''<DATETIME_INTERVAL> + <DATETIME_INTERVAL>''<DATETIME>
> + <DATETIME_INTERVAL>''<DATETIME_INTERVAL> + <DATETIME>'.is calcite can
> support this operate(+) apply on strings?
There is already a JIRA case for this:
https://issues.apache.org/jira/browse/CALCITE-1924
<https://issues.apache.org/jira/browse/CALCITE-1924>. It would be great if
someone fixed it.
Julian
[1]
https://github.com/julianhyde/tpcds/tree/master/src/main/resources/query_templates
<https://github.com/julianhyde/tpcds/tree/master/src/main/resources/query_templates>
