Details

    • Type: Sub-task Sub-task
    • Status: In Progress
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
      None

      Description

      TPC-H Queries: 1.sql

      Original Query:
      – $ID$
      – TPC-H/TPC-R Pricing Summary Report Query (Q1)
      – Functional Query Definition
      – Approved February 1998
      :x
      :o
      select
      l_returnflag,
      l_linestatus,
      sum(l_quantity) as sum_qty,
      sum(l_extendedprice) as sum_base_price,
      sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
      sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
      avg(l_quantity) as avg_qty,
      avg(l_extendedprice) as avg_price,
      avg(l_discount) as avg_disc,
      count as count_order
      from
      lineitem
      where
      l_shipdate <= date '1998-12-01' - interval ':1' day (3)
      group by
      l_returnflag,
      l_linestatus
      order by
      l_returnflag,
      l_linestatus;
      :n -1

      In java:
      private static final String sql_1 =
      "select "
      + "returnFlag, lineStatus,"
      + "sum(quantity) as sum_qty,"
      + "sum(extendedPrice) as sum_base_price,"
      + "sum(extendedPrice * (1 - discount)) as sum_disc_price,"
      + "sum(extendedPrice * (1 - discount) * (1 + tax)) as sum_charge,"
      + "avg(quantity) as avg_qty,"
      + "avg(extendedPrice) as avg_price,"
      + "avg(discount) as avg_disc,"
      + "count as count_order "
      + "from "
      + "lineitem "
      + "where "
      + "shippingDate <= date '1998-12-01' - interval '" + (60 * Math.random() + 60) + "' day (3) "
      + "group by "
      + "returnFlag,"
      + "lineStatus "
      + "order by "
      + "returnflag,"
      + "lineStatus";

      Sample record in json: (first line in lineitem.tbl.1)

      { "orderKey":"1", "partKey":"155190", "supplierKey":"7706", "lineNumber":"1", "quantity":"17", "extendedPrice":"21168.23", "discount":"0.04", "tax":"0.02", "returnFlag":"N", "lineStatus":"O", "shippingDate":"1996-03-13", "committedDate":"1996-02-12", "receivedDate":"1996-03-22", "shippingInstructions":"DELIVER IN PERSON", "shippingMode":"TRUCK", "comment":"egular courts above the" }
      1. LineItemTest.java
        2 kB
        Sree Vaddi
      2. lineitem.tbl.1.json
        35 kB
        Sree Vaddi

        Activity

        Hide
        Sree Vaddi added a comment -

        org.eigenbase.sql.validate.ListScope.findQualifyingTableName() method.

        loop at #112, returning count as 0.

        switch at #119,
        org.eigenbase.sql.validate.EmptyScope.findQualifyingTableName() = always throws exception.

        in simple terms, RETURNFLAG is not translating to LINEITEM.RETURNFLAG by QualifyingTableName().

        Show
        Sree Vaddi added a comment - org.eigenbase.sql.validate.ListScope.findQualifyingTableName() method. loop at #112, returning count as 0. switch at #119, org.eigenbase.sql.validate.EmptyScope.findQualifyingTableName() = always throws exception. in simple terms, RETURNFLAG is not translating to LINEITEM.RETURNFLAG by QualifyingTableName().
        Hide
        Sree Vaddi added a comment -

        Attached LineItemTest.java and lineitem.tbl.1.json

        Show
        Sree Vaddi added a comment - Attached LineItemTest.java and lineitem.tbl.1.json
        Hide
        Sree Vaddi added a comment -

        First run of sqlparser:org.apache.drill.jdbc.test.LineItemTest

        Jul 29, 2013 10:59:48 PM org.eigenbase.sql.validate.SqlValidatorException <init>
        SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Column 'RETURNFLAG' not found in any table
        Jul 29, 2013 10:59:48 PM org.eigenbase.util.EigenbaseException <init>
        SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column 417 to line 1, column 426

        Show
        Sree Vaddi added a comment - First run of sqlparser:org.apache.drill.jdbc.test.LineItemTest Jul 29, 2013 10:59:48 PM org.eigenbase.sql.validate.SqlValidatorException <init> SEVERE: org.eigenbase.sql.validate.SqlValidatorException: Column 'RETURNFLAG' not found in any table Jul 29, 2013 10:59:48 PM org.eigenbase.util.EigenbaseException <init> SEVERE: org.eigenbase.util.EigenbaseContextException: From line 1, column 417 to line 1, column 426

          People

          • Assignee:
            Sree Vaddi
            Reporter:
            Sree Vaddi
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development