Details

    • Type: Sub-task Sub-task
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: 0.4.0
    • Component/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. lineitem.tbl.1.json
        35 kB
        Sree Vaddi
      2. LineItemTest.java
        2 kB
        Sree Vaddi

        Activity

        Sree Vaddi created issue -
        Sree Vaddi made changes -
        Field Original Value New Value
        Status Open [ 1 ] In Progress [ 3 ]
        Sree Vaddi made changes -
        Description TPC-H Queries: 1.sql
        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"
        }
        Sree Vaddi made changes -
        Attachment LineItemTest.java [ 12594883 ]
        Attachment lineitem.tbl.1.json [ 12594884 ]
        Jake Farrell made changes -
        Workflow jira [ 12807164 ] no-reopen-closed, patch-avail [ 12807516 ]
        Jake Farrell made changes -
        Workflow no-reopen-closed, patch-avail [ 12807516 ] no-reopen-closed, patch-avail, testing [ 12860467 ]
        Jacques Nadeau made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Resolution Won't Fix [ 2 ]
        Jacques Nadeau made changes -
        Fix Version/s 0.4.0 [ 12324963 ]
        Tony Stevenson made changes -
        Workflow no-reopen-closed, patch-avail, testing [ 12860467 ] Drill workflow [ 12934611 ]

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development