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]>: > 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.UsernamePasswordAuthentication > Token@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]> > *发送时间:* 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]>: > >> 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 <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]> >> *发送时间:* 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]>: >> >>> >>> 大家好： >>> >>> 用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.kyli >>> n.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.kyli >>> n.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.get >>> Time()); >>> 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 >>> 结果： >>> >>> >>> >>> >>> >> >
