It might be a bug in the implicit character-to-date conversion. If you change
AND ("REP_DATE_FOR_WEEK_INFO_DIMTA"."REP_D" IN ('2016-08-01', '2016-08-15',
'2016-08-18')))
to
AND ("REP_DATE_FOR_WEEK_INFO_DIMTA"."REP_D" IN (DATE '2016-08-01', DATE
'2016-08-15', DATE '2016-08-18')))
does it work?
Julian
> On Sep 27, 2016, at 1:32 AM, 胡志华(万里通科技及数据中心商务智能团队数据分析组)
> <[email protected]> wrote:
>
> Hi all,
>
> I am using kylin1.5.3, I found kylin doesn’t support keywords “IN”
> quite well. When I replaced “in” with “=”, the sql executed successfully
>
> I paste my sql as below:
>
> SELECT "REP_DATE_FOR_WEEK_INFO_DIMTA"."REP_D" AS "REP_D",
> SUM("TXN_SUB_ORDER_INFO_FTA"."PAY_POINTS") AS
> "TEMP_Calculation_EIBAJCCBAFIFGDIJ__BGDGAHDFFE__A_", '-小计-' AS "X______",
> COUNT(DISTINCT "TXN_SUB_ORDER_INFO_FTA"."DOC_ID") AS "ctd_DOC_ID_ok",
> SUM("TXN_SUB_ORDER_INFO_FTA"."DISCOUNT") AS "sum_DISCOUNT_ok",
> SUM("TXN_SUB_ORDER_INFO_FTA"."PAY_CASH") AS "sum_PAY_CASH_ok" FROM
> "WLT_IDL"."TXN_SUB_ORDER_INFO_FT0" "TXN_SUB_ORDER_INFO_FTA" INNER JOIN
> "WLT_PUB"."REP_DATE_FOR_WEEK_INFO_DIMT0" "REP_DATE_FOR_WEEK_INFO_DIMTA" ON
> ("TXN_SUB_ORDER_INFO_FTA"."PT_PROCESS_D" =
> "REP_DATE_FOR_WEEK_INFO_DIMTA"."PROCESS_D") INNER JOIN
> "WLT_PUB"."PUB_TXN_TYPE_DIMT0" "PUB_TXN_TYPE_DIMTA" ON
> (("TXN_SUB_ORDER_INFO_FTA"."GAIN_PAY_IND" =
> "PUB_TXN_TYPE_DIMTA"."GAIN_PAY_IND") AND
> ("TXN_SUB_ORDER_INFO_FTA"."PATHWAY_IND" = "PUB_TXN_TYPE_DIMTA"."PATHWAY_IND")
> AND ("TXN_SUB_ORDER_INFO_FTA"."TXN_TYPE_IND" = "P
> UB_TXN_TYPE_DIMTA"."TXN_TYPE_IND") AND
> ("TXN_SUB_ORDER_INFO_FTA"."TXN_SUB_TYPE_IND" =
> "PUB_TXN_TYPE_DIMTA"."TXN_SUB_TYPE_IND")) WHERE
> (("PUB_TXN_TYPE_DIMTA"."IS_ACTIVITY" = 1) AND
> ("REP_DATE_FOR_WEEK_INFO_DIMTA"."REP_D" IN ('2016-08-01', '2016-08-15',
> '2016-08-18'))) GROUP BY "REP_DATE_FOR_WEEK_INFO_DIMTA"."REP_D"
>
>
> The error info :
> Error in coprocessor
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
>
> 5839548,2-9 99%
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
> at
> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:364)
> at
> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:276)
> at
> org.apache.kylin.rest.service.QueryService.query(QueryService.java:121)
> at
> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
> at
> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$a52cb381.query(<generated>)
> at
> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
> at
> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>
>
>
>
>
>
> **************************************
> 胡志华
> 壹钱包业务运营中心数据分析部
> (：021-20667416/18019788229
> *：上海徐汇区凯滨路206号平安大厦A座15楼
> **************************************
>
>
>
> ********************************************************************************************************************************
> The information in this email is confidential and may be legally privileged.
> If you have received this email in error or are not the intended recipient,
> please immediately notify the sender and delete this message from your
> computer. Any use, distribution, or copying of this email other than by the
> intended recipient is strictly prohibited. All messages sent to and from us
> may be monitored to ensure compliance with internal policies and to protect
> our business.
> Emails are not secure and cannot be guaranteed to be error free as they can
> be intercepted, amended, lost or destroyed, or contain viruses. Anyone who
> communicates with us by email is taken to accept these risks.
>
> 收发邮件者请注意：
> 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> ********************************************************************************************************************************
>
