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
