OK! https://issues.apache.org/jira/browse/KYLIN-2806
________________________________ 发件人: Billy Liu <[email protected]> 发送时间: 2017年8月22日 15:58 收件人: user 主题: Re: 答复: 答复: No result from JDBC with Date filter in prepareStatement I think it's a bug introduced by latest server side prepared statement refactor. Could you log a JIRA for this issue? THank you. 2017-08-22 11:17 GMT+08:00 程 万胜 <[email protected]<mailto:[email protected]>>: yes. I tested kylin2.0 and kylin2.1, that has some problem kylin.log 2017-08-22 11:12:11,925 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:113 : User ADMIN authorities : [ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER] 2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:57 : User cache [-108, 112, -63, -32, 41, -87, -81, 81, -32, 61, -35, -111, 7, 56, -29, -59] is removed due to EXPIRED 2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:126 : Authenticated user org.springframework.security.authentication.UsernamePasswordAuthenticationToken@c8fb3bba: Principal: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER; Credentials: [PROTECTED]; Authenticated: true; Details: org.springframework.security.web.authentication.WebAuthenticationDetails@0: RemoteIpAddress: 192.168.93.1; SessionId: null; Granted Authorities: ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER 2017-08-22 11:12:11,933 DEBUG [http-bio-7070-exec-4] controller.UserController:55 : User login: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER 2017-08-22 11:12:11,996 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:366 : Using project: learn_kylin 2017-08-22 11:12:11,997 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:367 : The original query: select * from KYLIN_SALES where part_dt = ? 2017-08-22 11:12:11,998 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] schema.OLAPSchemaFactory:116 : Schema json:{ "version": "1.0", "defaultSchema": "DEFAULT", "schemas": [ { "type": "custom", "name": "DEFAULT", "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory", "operand": { "project": "LEARN_KYLIN" }, "functions": [ { name: 'PERCENTILE', className: 'org.apache.kylin.measure.percentile.PercentileAggFunc' }, { name: 'INTERSECT_COUNT', className: 'org.apache.kylin.measure.bitmap.BitmapIntersectDistinctCountAggFunc' }, { name: 'MASSIN', className: 'org.apache.kylin.query.udf.MassInUDF' }, { name: 'CONCAT', className: 'org.apache.kylin.query.udf.ConcatUDF' }, { name: 'VERSION', className: 'org.apache.kylin.query.udf.VersionUDF' } ] } ] } 2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:868 : Processed rows for each storageContext: 2017-08-22 11:12:12,008 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:393 : Stats of SQL response: isException: false, duration: 11, total scan count 0 2017-08-22 11:12:12,009 DEBUG [Query cc783405-8d75-4f3c-9fdb-411427111448-92] util.CheckUtil:35 : query is too lightweight with duration: 11 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576) 2017-08-22 11:12:12,009 INFO [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:284 : ==========================[QUERY]=============================== Query Id: cc783405-8d75-4f3c-9fdb-411427111448 SQL: select * from KYLIN_SALES where part_dt = ? User: ADMIN Success: true Duration: 0.011 Project: learn_kylin Realization Names: [] Cuboid Ids: [] Total scan count: 0 Total scan bytes: 0 Result row count: 0 Accept Partial: false Is Partial Result: false Hit Exception Cache: false Storage cache used: false Is Query Push-Down: false Message: null ________________________________ 发件人: Billy Liu <[email protected]<mailto:[email protected]>> 发送时间: 2017年8月21日 17:04 收件人: user 主题: Re: 答复: No result from JDBC with Date filter in prepareStatement Could you try the latest Kylin 2.1? The Date parameter issue has been fixed during Kylin 2.0. 2017-08-21 13:45 GMT+08:00 程 万胜 <[email protected]<mailto:[email protected]>>: kylin.log 2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9] controller.UserController:64 : authentication.getPrincipal() is org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNo nExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER 2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:337 : Using project: learn_kylin 2017-08-21 13:40:58,679 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:338 : The original query: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by part_dt limit 100 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:56 : Find candidates by table TMP_KYLIN.KYLIN_SALES and project=LEARN_KYLIN : CUBE[name=kylin_sales_cube] 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveBlackoutRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sa les_cube(CUBE)] 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_s ales_cube(CUBE)] 2017-08-21 13:40:58,692 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] rules.RealizationSortRule:40 : CUBE[name=kylin_sales_cube] priority 1 cost 836. 2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51 : Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule, realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sales_cube(CU BE)] 2017-08-21 13:40:58,693 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:68 : The realizations remaining: [kylin_sales_cube(CUBE)] And the final chosen one is the first one 2017-08-21 13:40:58,714 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:528 : Setting current statement's max rows to 0 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:109 : query storage... 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:253 : Does not need storage aggregation 2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:345 : Storage limit push down is impossible because the query has order by 2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:392 : Aggregate partition results is not beneficial because no storage aggregation 2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:151 : Cuboid identified: cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT], filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown =2147483647<tel:021%204748%203647>, storageAggr=false 2017-08-21 13:40:58,719 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeSegmentScanner:56 : Init CubeSegmentScanner for segment 20120101000000_20170801000000 2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] dimension.DimensionEncodingFactory:57 : Encoding Name : date, args : [], version 1 2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeScanRangePlanner:213 : Pre-check partition col filter failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0, segment start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be gin null, range end \x0A\xFA\xAA 2017-08-21 13:40:58,721 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.ScannerWorker:44 : Segment kylin_sales_cube[20120101000000_20170801000000] will be skipped 2017-08-21 13:40:58,744 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.SequentialCubeTupleIterator:71 : Using Iterators.concat to merge segment results 2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:122 : return TupleIterator... 2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:598 : Processed rows for each storageContext: 0 2017-08-21 13:40:58,746 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:363 : Stats of SQL response: isException: false, duration: 67, total scan count 0 2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] util.CheckUtil:29 : query is too lightweight with duration: 67 (threshold 2000), scan count: 0 (threshold 10240), scan bytes: 0 (threshold 1048576) 2017-08-21 13:40:58,747 INFO [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:286 : ==========================[QUERY]=============================== Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22 SQL: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales where part_dt <= ? group by part_dt order by part_dt limit 100 User: ADMIN Success: true Duration: 0.067 Project: learn_kylin Realization Names: [CUBE[name=kylin_sales_cube]] Cuboid Ids: [16384] Total scan count: 0 Total scan bytes: 0 Result row count: 0 Accept Partial: false Is Partial Result: false Hit Exception Cache: false Storage cache used: false Message: null ==========================[QUERY]=============================== ________________________________ 发件人: Billy Liu <[email protected]<mailto:[email protected]>> 发送时间: 2017年8月21日 13:14 收件人: user 主题: Re: No result from JDBC with Date filter in prepareStatement THank you for reporting. Could you check the kylin.log first? 2017-08-21 11:15 GMT+08:00 程 万胜 <[email protected]<mailto:[email protected]>>: 大家好： 用kylin的JDBC查询，查询条件是date类型的，如果通过prepareStatement、setDate是查询不出数据的，然而直接用SQL可以查出数据 我用的是kylin版本：apache-kylin-1.6.0-cdh5.7-bin，jdbc版本：kylin-jdbc-1.6.0.jar，java版本：java version "1.8.0_92" 代码1 Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance(); Properties info = new Properties(); info.put("user", "xxx"); info.put("password", "xxx"); Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info); PreparedStatement state = conn.prepareStatement( "select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers " + "from kylin_sales where part_dt <= '2013-01-01' " + "group by part_dt " + "order by part_dt limit 100"); ResultSet resultSet = state.executeQuery(); System.out.print("结果："); while (resultSet.next()) { System.out.print(resultSet.getString(1) + "\n"); } 运行结果 SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. 结果：2011-12-31 2012-01-01 2012-01-02 2012-01-03 2012-01-04 2012-01-05 2012-01-06 2012-01-07 2012-01-08 2012-01-09 代码2 Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance(); Properties info = new Properties(); info.put("user", "xxx"); info.put("password", "xxx"); Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info); PreparedStatement state = conn.prepareStatement( "select " + "part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers " + "from kylin_sales where part_dt <= ? " + "group by part_dt " + "order by part_dt limit 100"); SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd"); java.util.Date passUtilDate = simpleTime.parse("2013-01-01"); java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.getTime()); System.out.print("条件是：" + passSqlDate + "\n"); state.setDate(1, passSqlDate); ResultSet resultSet = state.executeQuery(); System.out.print("结果："); while (resultSet.next()) { System.out.print(resultSet.getString(1) + "\n"); } 运行结果 SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. 条件是：2013-01-01 结果：
