Uploaded image for project: 'Kylin'
  1. Kylin
  2. KYLIN-2806

Empty results from JDBC with Date filter in prepareStatement

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: v1.6.0, v2.0.0, v2.1.0
    • Fix Version/s: None
    • Component/s: Driver - JDBC, Query Engine
    • Labels:
    • Environment:
      kylin版本:apache-kylin-1.6.0-cdh5.7-bin
      jdbc版本:kylin-jdbc-1.6.0.jar
      java版本:java version "1.8.0_92"
    • Flags:
      Important
    • Docs Text:
      Hide
      code:
      代码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
      结果:

      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, 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]===============================
      Show
      code: 代码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 结果: 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, 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]===============================

      Description

      用kylin的JDBC查询,查询条件是date类型的,如果通过prepareStatement、setDate是查询不出数据的,然而直接用SQL可以查出数据

        Activity

        Hide
        wormholer Wang Cheng added a comment -

        This issue is caused by KYLIN-2671, it will only return the query results meta info if the query pattern contains "where xx=?". It will be fixed soon.

        Show
        wormholer Wang Cheng added a comment - This issue is caused by KYLIN-2671 , it will only return the query results meta info if the query pattern contains "where xx=?". It will be fixed soon.

          People

          • Assignee:
            wormholer Wang Cheng
            Reporter:
            visycheng chengwansheng
            Request participants:
            None
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: