[
https://issues.apache.org/jira/browse/KYLIN-5393?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiale He resolved KYLIN-5393.
-----------------------------
Resolution: Fixed
> Query SQL statement contains single quotes in the fuzzy match field value and
> fails to parse
> --------------------------------------------------------------------------------------------
>
> Key: KYLIN-5393
> URL: https://issues.apache.org/jira/browse/KYLIN-5393
> Project: Kylin
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 5.0-alpha
> Reporter: Jiale He
> Assignee: Jiale He
> Priority: Major
> Fix For: 5.0-alpha
>
> Attachments: KYLIN-5393 Design & Test.pdf,
> image-2023-01-10-10-04-20-520.png, image-2023-01-10-10-06-13-576.png
>
>
> {code:java}
> SELECT
> COUNT(*)
> FROM
> ADVSEARCHIADSECURE_2.vw_exp_commission
> WHERE
> BUSINESS_ID = '15030'
> AND (AMOUNT_UNIT_TYPE_ID = '3449880796')
> AND (AS_OF_PERIOD_ID = '3320923194')
> AND (PERIOD_ID = '3320923194')
> AND (EARNING_GROUP_NAME = 'Events')
> AND (EMPLOYEE_ID = '0053p000007bEzjAAE')
> AND (
> (lower(CUSTOMER_NAME) like '%abb4y000000ggzmcaq%')
> OR (lower(PRODUCT_NAME) like'%abb4y000000ggzmcaq%')
> OR (
> lower(GEOGRAPHY_NAME) like'%abb4y000000ggzmcaq%'
> )
> OR (lower(ORDER_CODE) like'%abb4y000000ggzmcaq%')
> OR (
> lower(ITEM_CODE) like'%abb' 4y000000ggzmcaq % ')
> ){code}
> 上述 fifter 条件中 {{{}lower(ITEM_CODE) like '%abb'4y000000ggzmcaq%'{}}}，模糊匹配值
> \{{abb'4y000000ggzmcaq}}中含有单引号，导致在解析阶段就报错
> 另外也根据手册提示尝试加单引号进行转译，但是引号在字段中间的情况 kylin5 无法处理
>
> 本地验证，发现如果加上单引号转义后查询用模型来回答查询结果是正常的，但若是 HIVE 下压查询，下压的查询结果不正确：
> !image-2023-01-10-10-04-20-520.png|width=758,height=464!
> !image-2023-01-10-10-06-13-576.png|width=743,height=463!
>
> Hive 中不是用引号转义的，所以结果处理有问题
>
> h1. Root Cause{*}{*}
> calcite不支持单个'字符出现在常量的中间，因此ke手册中标注是用'进行转义。
> 在查询索引的时候''等价于'，但是下压的时候spark不这么认为，因此查询结果不一致。
>
> h1. 期望
> 能转义查询 SQL 语句中的单引号 针对单引号查询，希望 kylin5 查询结果和 Hive 下压查询结果一致
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
