[
https://issues.apache.org/jira/browse/TRAFODION-1158?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-1158.
---------------------------------------
Resolution: Fixed
> LP Bug: 1442944 - An ORDER BY query returns wrong result
> --------------------------------------------------------
>
> Key: TRAFODION-1158
> URL: https://issues.apache.org/jira/browse/TRAFODION-1158
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-exe
> Reporter: Weishiun Tsai
> Assignee: Hans Zeller
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> The following order by query returns wrong result in the v1.1.0rc0 (v0410)
> build. This query has been returning the correct result ever since the
> Trafodion early days all the way until the v0407 build. It is a regression
> introduced between the v0407 build and the r1.1.0rc0 (v0410) build.
> Bellow you can find the execution results in v0407 (correct) and in r1.1.0rc0
> (incorrect). Notice that this is a query ordered by 2, 3, 4, 1:
> set schema trafodion.g_tpcds1x;
> prepare xx from
> select [first 200]
> i_item_id,
> ca_country,
> ca_state,
> ca_county,
> ......
> order by 2,3,4,1;
> since 2 (ca_country) is the same value ‘United Stats’ for the first 200 rows,
> the query essentially should see the rows in the order of 3 (ca_state). In
> the v0407 execution output, the rows are ordered correctly with ‘IN’ followed
> by ‘CO’. But in the v1.1.0rc0 execution output, ‘CO’ and ‘IN’ started to
> mixed together after certain rows.
> ----------------------------------------------------------
> Here is the script to reproduce it. (It requires the QA g_tpch1x tables.)
> set schema trafodion.g_tpcds1x;
> prepare xx from
> select [first 200]
> i_item_id,
> ca_country,
> ca_state,
> ca_county,
> agg1,
> agg2,
> agg3,
> agg4,
> agg5,
> agg6,
> agg7
> from
> (
> select i_item_id,
> ca_country,
> ca_state,
> ca_county,
> avg(cs_quantity) agg1,
> avg(cs_list_price) agg2,
> avg(cs_coupon_amt) agg3,
> avg(cs_sales_price) agg4,
> avg(cs_net_profit) agg5,
> avg(c_birth_year) agg6,
> avg(cd1.cd_dep_count) agg7
> from catalog_sales, customer_demographics cd1,
> customer_demographics cd2, customer,
> customer_address, date_dim, item
> where cs_sold_date_sk = d_date_sk and
> cs_item_sk = i_item_sk and
> cs_bill_cdemo_sk = cd1.cd_demo_sk and
> cs_bill_customer_sk = c_customer_sk and
> cd1.cd_gender = 'F' and
> cd1.cd_education_status = 'Primary' and
> c_current_cdemo_sk = cd2.cd_demo_sk and
> c_current_addr_sk = ca_address_sk and
> c_birth_month in (9,4,3,11,1,12) and
> d_year = 1999 and
> ca_state in ('VA','NE','MI','WV','WV','CO','IN')
> group by 1,2,3,4
> union all
> select i_item_id,
> cast(null as char),
> ca_state,
> ca_county,
> avg(cs_quantity) agg1,
> avg(cs_list_price) agg2,
> avg(cs_coupon_amt) agg3,
> avg(cs_sales_price) agg4,
> avg(cs_net_profit) agg5,
> avg(c_birth_year) agg6,
> avg(cd1.cd_dep_count) agg7
> from catalog_sales, customer_demographics cd1,
> customer_demographics cd2, customer,
> customer_address, date_dim, item
> where cs_sold_date_sk = d_date_sk and
> cs_item_sk = i_item_sk and
> cs_bill_cdemo_sk = cd1.cd_demo_sk and
> cs_bill_customer_sk = c_customer_sk and
> cd1.cd_gender = 'F' and
> cd1.cd_education_status = 'Primary' and
> c_current_cdemo_sk = cd2.cd_demo_sk and
> c_current_addr_sk = ca_address_sk and
> c_birth_month in (9,4,3,11,1,12) and
> d_year = 1999 and
> ca_state in ('VA','NE','MI','WV','WV','CO','IN')
> group by 1,2,3,4
> union all
> select i_item_id,
> cast(null as char),
> cast(null as char),
> ca_county,
> avg(cs_quantity) agg1,
> avg(cs_list_price) agg2,
> avg(cs_coupon_amt) agg3,
> avg(cs_sales_price) agg4,
> avg(cs_net_profit) agg5,
> avg(c_birth_year) agg6,
> avg(cd1.cd_dep_count) agg7
> from catalog_sales, customer_demographics cd1,
> customer_demographics cd2, customer,
> customer_address, date_dim, item
> where cs_sold_date_sk = d_date_sk and
> cs_item_sk = i_item_sk and
> cs_bill_cdemo_sk = cd1.cd_demo_sk and
> cs_bill_customer_sk = c_customer_sk and
> cd1.cd_gender = 'F' and
> cd1.cd_education_status = 'Primary' and
> c_current_cdemo_sk = cd2.cd_demo_sk and
> c_current_addr_sk = ca_address_sk and
> c_birth_month in (9,4,3,11,1,12) and
> d_year = 1999 and
> ca_state in ('VA','NE','MI','WV','WV','CO','IN')
> group by 1,2,3,4
> union all
> select i_item_id,
> cast(null as char),
> cast(null as char),
> cast(null as char),
> avg(cs_quantity) agg1,
> avg(cs_list_price) agg2,
> avg(cs_coupon_amt) agg3,
> avg(cs_sales_price) agg4,
> avg(cs_net_profit) agg5,
> avg(c_birth_year) agg6,
> avg(cd1.cd_dep_count) agg7
> from catalog_sales, customer_demographics cd1,
> customer_demographics cd2, customer,
> customer_address, date_dim, item
> where cs_sold_date_sk = d_date_sk and
> cs_item_sk = i_item_sk and
> cs_bill_cdemo_sk = cd1.cd_demo_sk and
> cs_bill_customer_sk = c_customer_sk and
> cd1.cd_gender = 'F' and
> cd1.cd_education_status = 'Primary' and
> c_current_cdemo_sk = cd2.cd_demo_sk and
> c_current_addr_sk = ca_address_sk and
> c_birth_month in (9,4,3,11,1,12) and
> d_year = 1999 and
> ca_state in ('VA','NE','MI','WV','WV','CO','IN')
> group by 1,2,3,4
> union all
> select cast(null as char),
> cast(null as char),
> cast(null as char),
> cast(null as char),
> avg(cs_quantity) agg1,
> avg(cs_list_price) agg2,
> avg(cs_coupon_amt) agg3,
> avg(cs_sales_price) agg4,
> avg(cs_net_profit) agg5,
> avg(c_birth_year) agg6,
> avg(cd1.cd_dep_count) agg7
> from catalog_sales, customer_demographics cd1,
> customer_demographics cd2, customer,
> customer_address, date_dim, item
> where cs_sold_date_sk = d_date_sk and
> cs_item_sk = i_item_sk and
> cs_bill_cdemo_sk = cd1.cd_demo_sk and
> cs_bill_customer_sk = c_customer_sk and
> cd1.cd_gender = 'F' and
> cd1.cd_education_status = 'Primary' and
> c_current_cdemo_sk = cd2.cd_demo_sk and
> c_current_addr_sk = ca_address_sk and
> c_birth_month in (9,4,3,11,1,12) and
> d_year = 1999 and
> ca_state in ('VA','NE','MI','WV','WV','CO','IN')
> group by 1,2,3,4
> ) v1
> order by 2,3,4,1;
> explain options 'f' xx;
> execute xx;
> ----------------------------------------------------------
> Launchpad prevents adding long text in the description here. See attached
> file output.txt for the incorrect result from the r1.1.0rc0 (v0410) build and
> the correct result from the v0407 build.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
