Runs successfully when I use the >= symbol alone ==========================[QUERY]=============================== Query Id: e831aa51-6c6b-42d8-b683-e0060398f43b SQL: SELECT "HYNAME" AS "HYNAME", SUM(QY_SUM)-SUM(QY_SUM_PRE) AS "FLOW_IN_ENTERPRISE" FROM (SELECT CAST(T.PAYMENT_DATE AS DATE) AS CAL_DATE, CONCAT(CAST(S.CALENDARYEAR AS CHAR(4)),'?') AS CAL_DATE_YEAR, CONCAT(CAST(S.CALENDARQUARTER AS CHAR(4)),'??') AS CAL_DATE_QUARTER, CASE WHEN S.CALENDARMONTH<10 THEN CONCAT(CONCAT('0',CAST(S.CALENDARMONTH AS CHAR(4))),'?') ELSE CONCAT(CAST(S.CALENDARMONTH AS CHAR(4)),'?') END AS CAL_DATE_MONTH, T.INDUSTRY_CATEGORY_NAME AS HYNAME, T.COMPANY_CNT AS QY_SUM, T.COMPANY_CNT_ROW AS QY_SUM_ROW, T.COMPANY_CNT_BEFORE AS QY_SUM_PRE, T.COMPANY_CNT_GROWTH_PERCENT AS QY_SUM_PERC, (T.COMPANY_CNT-T.COMPANY_CNT_BEFORE) AS QY_SUM_GROWTH, T.SUM_EMNUM AS YG_SUM, T.SUM_EMNUM_ROW AS YG_SUM_ROW, T.SUM_EMNUM_BEFORE AS YG_SUM_PRE, T.SUM_EMNUM_GROWTH_PERCENT AS YG_SUM_PERC, (T.SUM_EMNUM-T.SUM_EMNUM_BEFORE) AS YG_SUM_GROWTH, T.SUM_REGISTERED_CAPITAL AS RC_SUM, T.SUM_REGISTERED_CAPITAL_ROW AS RC_SUM_ROW, T.SUM_REGISTERED_CAPITAL_BEFORE AS RC_SUM_PRE, T.SUM_REGISTERED_CAPITAL_GROWTH_PERCENT AS RC_SUM_PERC, (T.SUM_REGISTERED_CAPITAL-T.SUM_REGISTERED_CAPITAL_BEFORE) AS RC_SUM_GROWTH, T.SUM_OPERATING_LIFE AS OL_SUM, T.SUM_OPERATING_LIFE_ROW AS OL_SUM_ROW FROM INDUSTRY_RANK T LEFT JOIN INDUSTRY_RANK_DATE S ON T.SYS_ID=S.SYS_ID) AS expr_qry WHERE "CAL_DATE" >= TO_DATE('2011-09-20 00:00:00', 'yyyy-MM-dd') GROUP BY "HYNAME" ORDER BY "FLOW_IN_ENTERPRISE" DESC LIMIT 10; User: ADMIN Success: true Duration: 0.216 Project: WEIHAI_DEMO Realization Names: [CUBE[name=Cube_INDUSTRY_RANK]] Cuboid Ids: [524287] Total scan count: 7709 Total scan bytes: 562757 Result row count: 10 Accept Partial: true Is Partial Result: false Hit Exception Cache: false Storage cache used: false Is Query Push-Down: false Is Prepare: false Trace URL: null Message: null ==========================[QUERY]=============================== Results are as follows: 其他未列明零售业 2086 建材批发 925 服装批发 774 建筑装饰业 715 机织服装制造 668 其他未列明商务服务业 552 其他专业咨询 459
------------------------------------------------------------------------------------------------------------------------------------------------ When I use <= alone, an unknown error occurs, the log is as follows: ==========================[QUERY]=============================== Query Id: 34c40b98-095a-4085-82f9-8f3c72848cdd SQL: SELECT "HYNAME" AS "HYNAME", SUM(QY_SUM)-SUM(QY_SUM_PRE) AS "FLOW_IN_ENTERPRISE" FROM (SELECT CAST(T.PAYMENT_DATE AS DATE) AS CAL_DATE, CONCAT(CAST(S.CALENDARYEAR AS CHAR(4)),'?') AS CAL_DATE_YEAR, CONCAT(CAST(S.CALENDARQUARTER AS CHAR(4)),'??') AS CAL_DATE_QUARTER, CASE WHEN S.CALENDARMONTH<10 THEN CONCAT(CONCAT('0',CAST(S.CALENDARMONTH AS CHAR(4))),'?') ELSE CONCAT(CAST(S.CALENDARMONTH AS CHAR(4)),'?') END AS CAL_DATE_MONTH, T.INDUSTRY_CATEGORY_NAME AS HYNAME, T.COMPANY_CNT AS QY_SUM, T.COMPANY_CNT_ROW AS QY_SUM_ROW, T.COMPANY_CNT_BEFORE AS QY_SUM_PRE, T.COMPANY_CNT_GROWTH_PERCENT AS QY_SUM_PERC, (T.COMPANY_CNT-T.COMPANY_CNT_BEFORE) AS QY_SUM_GROWTH, T.SUM_EMNUM AS YG_SUM, T.SUM_EMNUM_ROW AS YG_SUM_ROW, T.SUM_EMNUM_BEFORE AS YG_SUM_PRE, T.SUM_EMNUM_GROWTH_PERCENT AS YG_SUM_PERC, (T.SUM_EMNUM-T.SUM_EMNUM_BEFORE) AS YG_SUM_GROWTH, T.SUM_REGISTERED_CAPITAL AS RC_SUM, T.SUM_REGISTERED_CAPITAL_ROW AS RC_SUM_ROW, T.SUM_REGISTERED_CAPITAL_BEFORE AS RC_SUM_PRE, T.SUM_REGISTERED_CAPITAL_GROWTH_PERCENT AS RC_SUM_PERC, (T.SUM_REGISTERED_CAPITAL-T.SUM_REGISTERED_CAPITAL_BEFORE) AS RC_SUM_GROWTH, T.SUM_OPERATING_LIFE AS OL_SUM, T.SUM_OPERATING_LIFE_ROW AS OL_SUM_ROW FROM INDUSTRY_RANK T LEFT JOIN INDUSTRY_RANK_DATE S ON T.SYS_ID=S.SYS_ID) AS expr_qry WHERE "CAL_DATE" <= TO_DATE('2018-09-20 01:33:04', 'yyyy-MM-dd') GROUP BY "HYNAME" ORDER BY "FLOW_IN_ENTERPRISE" DESC LIMIT 10; User: ADMIN Success: true Duration: 0.276 Project: WEIHAI_DEMO Realization Names: [CUBE[name=Cube_INDUSTRY_RANK]] Cuboid Ids: [524287] Total scan count: 0 Total scan bytes: 0 Result row count: 0 Accept Partial: true Is Partial Result: false Hit Exception Cache: false Storage cache used: false Is Query Push-Down: false Is Prepare: false Trace URL: null Message: null ==========================[QUERY]=============================== 2018-09-20 10:15:36,174 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] hbase.HBaseConnection:180 : Using the working dir FS for HBase: hdfs://nameservice 2018-09-20 10:15:36,174 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] v2.CubeHBaseRPC:315 : hbase.rpc.timeout = 60000 ms, use 54000 ms as timeout for coprocessor 2018-09-20 10:15:36,175 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] v2.CubeHBaseEndpointRPC:152 : Serialized scanRequestBytes 2935 bytes, rawScanBytesString 101 bytes 2018-09-20 10:15:36,175 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] v2.CubeHBaseEndpointRPC:154 : The scan 7a7baa0d for segment Cube_INDUSTRY_RANK[FULL_BUILD] is as below with 1 separate raw scans, shard part of start/end key is set to 0 2018-09-20 10:15:36,177 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] v2.CubeHBaseRPC:288 : Visiting hbase table KYLIN_N102ZXDQ5U: cuboid require post aggregation, from 475136 to 524287 Start: \x00\x00\x00\x00\x00\x00\x00\x07\xFF\xFF\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00 (\x00\x00\x00\x00\x00\x00\x00\x07\xFF\xFF\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00) Stop: \x00\x00\x00\x00\x00\x00\x00\x07\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\x00 (\x00\x00\x00\x00\x00\x00\x00\x07\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\x00), No Fuzzy Key 2018-09-20 10:15:36,177 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] v2.CubeHBaseEndpointRPC:159 : Submitting rpc to 1 shards starting from shard 0, scan range count 1 2018-09-20 10:15:36,178 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] common.KylinConfig:332 : Loading kylin-defaults.properties from file:/opt/module/apache-kylin-2.4.1-bin/tomcat/webapps/kylin/WEB-INF/lib/kylin-core-common-2.4.1.jar!/kylin-defaults.properties 2018-09-20 10:15:36,186 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] common.KylinConfig:291 : KYLIN_CONF property was not set, will seek KYLIN_HOME env variable 2018-09-20 10:15:36,187 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] gtrecord.SequentialCubeTupleIterator:78 : Using Iterators.concat to merge segment results 2018-09-20 10:15:36,188 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] enumerator.OLAPEnumerator:117 : return TupleIterator... 2018-09-20 10:15:36,188 INFO [kylin-coproc--pool2-t16] v2.CubeHBaseEndpointRPC:217 : Query-c4dc6f83-8c9d-4fa3-8485-00bd6742136e: send request to the init region server credit-cdh06.30wish.com on table KYLIN_N102ZXDQ5U 2018-09-20 10:15:36,259 INFO [kylin-coproc--pool2-t16] v2.CubeHBaseEndpointRPC:284 : <sub-thread for Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e GTScanRequest 7a7baa0d>Endpoint RPC returned from HTable KYLIN_N102ZXDQ5U Shard \x4B\x59\x4C\x49\x4E\x5F\x4E\x31\x30\x32\x5A\x58\x44\x51\x35\x55\x2C\x2C\x31\x35\x33\x35\x33\x34\x35\x35\x30\x34\x35\x34\x37\x2E\x32\x63\x30\x33\x38\x30\x37\x63\x36\x38\x63\x39\x33\x31\x31\x64\x35\x30\x38\x66\x64\x64\x38\x30\x65\x64\x39\x38\x35\x31\x37\x64\x2E on host: credit-cdh06.30wish.com.Total scanned row: 7709. Total scanned bytes: 562757. Total filtered row: 0. Total aggred row: 3935. Time elapsed in EP: 67(ms). Server CPU usage: 0.12346024636058231, server physical mem left: 4.283203584E9, server swap mem left:0.0.Etc message: start latency: 120@1,agg done@66,compress done@67,server stats done@67, debugGitTag:b450cc52f976ddafba7c6625d2440670af94332b;.Normal Complete: true.Compressed row size: 13868 2018-09-20 10:15:36,260 DEBUG [kylin-coproc--pool2-t16] util.CompressionUtils:67 : Original: 13868 bytes. Decompressed: 26418 bytes. Time: 1 2018-09-20 10:15:36,260 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] gtrecord.SortMergedPartitionResultIterator:76 : Using SortMergedPartitionResultIterator to merge 1 partition results out of 1 partitions 2018-09-20 10:15:36,269 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] service.QueryService:956 : Processed rows for each storageContext: 3774 2018-09-20 10:15:36,270 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] service.QueryService:430 : Stats of SQL response: isException: false, duration: 313, total scan count 7709 2018-09-20 10:15:36,270 DEBUG [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] util.CheckUtil:35 : query is too lightweight with duration: 313 (threshold 2000), scan count: 7709 (threshold 10240), scan bytes: 562757 (threshold 1048576) 2018-09-20 10:15:36,270 INFO [Query c4dc6f83-8c9d-4fa3-8485-00bd6742136e-189] service.QueryService:319 : zhengyangju...@163.com From: zhengyangju...@163.com Date: 2018-09-20 10:04 To: dev Subject: Query failed When I use >=, there is data query, when I add <=, the query is wrong. ==========================[QUERY]=============================== Query Id: a2095b23-f09c-4cd7-9969-e0ad98746dc3 SQL: SELECT "HYNAME" AS "HYNAME", SUM(QY_SUM)-SUM(QY_SUM_PRE) AS "FLOW_IN_ENTERPRISE" FROM (SELECT CAST(T.PAYMENT_DATE AS DATE) AS CAL_DATE, CONCAT(CAST(S.CALENDARYEAR AS CHAR(4)),'?') AS CAL_DATE_YEAR, CONCAT(CAST(S.CALENDARQUARTER AS CHAR(4)),'??') AS CAL_DATE_QUARTER, CASE WHEN S.CALENDARMONTH<10 THEN CONCAT(CONCAT('0',CAST(S.CALENDARMONTH AS CHAR(4))),'?') ELSE CONCAT(CAST(S.CALENDARMONTH AS CHAR(4)),'?') END AS CAL_DATE_MONTH, T.INDUSTRY_CATEGORY_NAME AS HYNAME, T.COMPANY_CNT AS QY_SUM, T.COMPANY_CNT_ROW AS QY_SUM_ROW, T.COMPANY_CNT_BEFORE AS QY_SUM_PRE, T.COMPANY_CNT_GROWTH_PERCENT AS QY_SUM_PERC, (T.COMPANY_CNT-T.COMPANY_CNT_BEFORE) AS QY_SUM_GROWTH, T.SUM_EMNUM AS YG_SUM, T.SUM_EMNUM_ROW AS YG_SUM_ROW, T.SUM_EMNUM_BEFORE AS YG_SUM_PRE, T.SUM_EMNUM_GROWTH_PERCENT AS YG_SUM_PERC, (T.SUM_EMNUM-T.SUM_EMNUM_BEFORE) AS YG_SUM_GROWTH, T.SUM_REGISTERED_CAPITAL AS RC_SUM, T.SUM_REGISTERED_CAPITAL_ROW AS RC_SUM_ROW, T.SUM_REGISTERED_CAPITAL_BEFORE AS RC_SUM_PRE, T.SUM_REGISTERED_CAPITAL_GROWTH_PERCENT AS RC_SUM_PERC, (T.SUM_REGISTERED_CAPITAL-T.SUM_REGISTERED_CAPITAL_BEFORE) AS RC_SUM_GROWTH, T.SUM_OPERATING_LIFE AS OL_SUM, T.SUM_OPERATING_LIFE_ROW AS OL_SUM_ROW FROM INDUSTRY_RANK T LEFT JOIN INDUSTRY_RANK_DATE S ON T.SYS_ID=S.SYS_ID) AS expr_qry WHERE "CAL_DATE" >= TO_DATE('2011-09-20 00:00:00', 'yyyy-MM-dd') AND "CAL_DATE" <= TO_DATE('2018-09-20 01:33:04', 'yyyy-MM-dd') GROUP BY "HYNAME" ORDER BY "FLOW_IN_ENTERPRISE" DESC LIMIT 10; User: ADMIN Success: true Duration: 0.226 Project: WEIHAI_DEMO Realization Names: [CUBE[name=Cube_INDUSTRY_RANK]] Cuboid Ids: [524287] Total scan count: 0 Total scan bytes: 0 Result row count: 0 Accept Partial: true Is Partial Result: false Hit Exception Cache: false Storage cache used: false Is Query Push-Down: false Is Prepare: false Trace URL: null Message: null ==========================[QUERY]=============================== zhengyangju...@163.com