OR predicate causes a query to return error 8421 with NOT NULL column

Bug #1425745 reported by Weishiun Tsai
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Trafodion
Fix Released
High
Hans Zeller

Bug Description

As shown in the following example, 2 queries x1 and x2 are supposed to be identical. The only difference is that a part of the predicate in x1:

(
cd_demo_sk = ss_cdemo_sk
and
ss_sales_price between 100.00 and 200.00
)

is replaced in x2 by a logically equivalent OR predicate:

(
cd_demo_sk = ss_cdemo_sk
and
ss_sales_price between 100.00 and 150.00
)
or
(
cd_demo_sk = ss_cdemo_sk
and
ss_sales_price between 150.00 and 200.00
)

x1 runs fine in Trafodion, but x2 returns ERROR[8421] 'NULL cannot be assigned to a NOT NULL column'. There are 3 columns used in this part of the predicate: ss_cdemo_sk and ss_sales_price contain null values, while cd_demo_sk is a ‘NOT NULL’ column.

This is seen on the v0210 daily build installed on a workstation. To reproduce this problem requires the QA g_tpcds1x tables. But the 5 tables in question are small enough to be loaded to a workstation instance. The scripts to load only these 5 tables are available upon request.

------------------------------------------------------------------------------------------------

Here is the entire script to reproduce this problem:

log mytest.log clear;

showddl g_tpcds1x.STORE_SALES;
showddl g_tpcds1x.STORE;
showddl g_tpcds1x.CUSTOMER_DEMOGRAPHICS;
showddl g_tpcds1x.CUSTOMER_ADDRESS;
showddl g_tpcds1x.DATE_DIM;

select count(*) from g_tpcds1x.STORE_SALES;
select count(*) from g_tpcds1x.STORE;
select count(*) from g_tpcds1x.CUSTOMER_DEMOGRAPHICS;
select count(*) from g_tpcds1x.CUSTOMER_ADDRESS;
select count(*) from g_tpcds1x.DATE_DIM;

select count(*) from g_tpcds1x.CUSTOMER_DEMOGRAPHICS where cd_demo_sk is null;
select count(*) from g_tpcds1x.STORE_SALES where ss_cdemo_sk is null;
select count(*) from g_tpcds1x.STORE_SALES where ss_sales_price is null;

prepare x1 from
select count(*)
from g_tpcds1x.STORE_SALES,
 g_tpcds1x.STORE,
 g_tpcds1x.CUSTOMER_DEMOGRAPHICS,
 g_tpcds1x.CUSTOMER_ADDRESS,
 g_tpcds1x.DATE_DIM
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk
and
(
(
cd_demo_sk = ss_cdemo_sk
and
ss_sales_price between 100.00 and 200.00
)
-- (
-- cd_demo_sk = ss_cdemo_sk
-- and
-- ss_sales_price between 100.00 and 150.00
-- )
-- or
-- (
-- cd_demo_sk = ss_cdemo_sk
-- and
-- ss_sales_price between 150.00 and 200.00
-- )
)
and
(
(
ss_addr_sk = ca_address_sk
and
ss_net_profit between 100 and 200
)
);

explain options 'f' x1;
execute x1;

prepare x2 from
select count(*)
from g_tpcds1x.STORE_SALES,
 g_tpcds1x.STORE,
 g_tpcds1x.CUSTOMER_DEMOGRAPHICS,
 g_tpcds1x.CUSTOMER_ADDRESS,
 g_tpcds1x.DATE_DIM
where s_store_sk = ss_store_sk
and ss_sold_date_sk = d_date_sk
and
(
-- (
-- cd_demo_sk = ss_cdemo_sk
-- and
-- ss_sales_price between 100.00 and 200.00
-- )
(
cd_demo_sk = ss_cdemo_sk
and
ss_sales_price between 100.00 and 150.00
)
or
(
cd_demo_sk = ss_cdemo_sk
and
ss_sales_price between 150.00 and 200.00
)
)
and
(
(
ss_addr_sk = ca_address_sk
and
ss_net_profit between 100 and 200
)
);

explain options 'f' x2;
execute x2;

------------------------------------------------------------------------------------------------

Here is the execution output:

>>showddl g_tpcds1x.STORE_SALES;

CREATE TABLE TRAFODION.G_TPCDS1X.STORE_SALES
  (
    SS_SOLD_DATE_SK INT DEFAULT NULL
  , SS_SOLD_TIME_SK INT DEFAULT NULL
  , SS_ITEM_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SS_CUSTOMER_SK INT DEFAULT NULL
  , SS_CDEMO_SK INT DEFAULT NULL
  , SS_HDEMO_SK INT DEFAULT NULL
  , SS_ADDR_SK INT DEFAULT NULL
  , SS_STORE_SK INT DEFAULT NULL
  , SS_PROMO_SK INT DEFAULT NULL
  , SS_TICKET_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SS_QUANTITY INT DEFAULT NULL
  , SS_WHOLESALE_COST DECIMAL(7, 2) DEFAULT NULL
  , SS_LIST_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_SALES_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_DISCOUNT_AMT DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_SALES_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_WHOLESALE_COST DECIMAL(7, 2) DEFAULT NULL
, SS_EXT_LIST_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_TAX DECIMAL(7, 2) DEFAULT NULL
  , SS_COUPON_AMT DECIMAL(7, 2) DEFAULT NULL
  , SS_NET_PAID DECIMAL(7, 2) DEFAULT NULL
  , SS_NET_PAID_INC_TAX DECIMAL(7, 2) DEFAULT NULL
  , SS_NET_PROFIT DECIMAL(7, 2) DEFAULT NULL
  , PRIMARY KEY (SS_ITEM_SK ASC, SS_TICKET_NUMBER ASC)
  )
  SALT USING 8 PARTITIONS
;

--- SQL operation complete.
>>showddl g_tpcds1x.STORE;

CREATE TABLE TRAFODION.G_TPCDS1X.STORE
  (
    S_STORE_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , S_STORE_ID CHAR(16) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , S_REC_START_DATE DATE DEFAULT NULL
  , S_REC_END_DATE DATE DEFAULT NULL
  , S_CLOSED_DATE_SK INT DEFAULT NULL
  , S_STORE_NAME VARCHAR(50) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_NUMBER_EMPLOYEES INT DEFAULT NULL
  , S_FLOOR_SPACE INT DEFAULT NULL
  , S_HOURS CHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_MANAGER VARCHAR(40) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_MARKET_ID INT DEFAULT NULL
  , S_GEOGRAPHY_CLASS VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , S_MARKET_DESC VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , S_MARKET_MANAGER VARCHAR(40) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_DIVISION_ID INT DEFAULT NULL
  , S_DIVISION_NAME VARCHAR(50) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_COMPANY_ID INT DEFAULT NULL
  , S_COMPANY_NAME VARCHAR(50) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_STREET_NUMBER VARCHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_STREET_NAME VARCHAR(60) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_STREET_TYPE CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_SUITE_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_CITY VARCHAR(60) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_COUNTY VARCHAR(30) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_STATE CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_ZIP CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_COUNTRY VARCHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_GMT_OFFSET DECIMAL(5, 2) DEFAULT NULL
  , S_TAX_PRECENTAGE DECIMAL(5, 2) DEFAULT NULL
  , PRIMARY KEY (S_STORE_SK ASC)
  )
  SALT USING 8 PARTITIONS
;

--- SQL operation complete.
>>showddl g_tpcds1x.CUSTOMER_DEMOGRAPHICS;

CREATE TABLE TRAFODION.G_TPCDS1X.CUSTOMER_DEMOGRAPHICS
  (
    CD_DEMO_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , CD_GENDER CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CD_MARITAL_STATUS CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CD_EDUCATION_STATUS CHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CD_PURCHASE_ESTIMATE INT DEFAULT NULL
  , CD_CREDIT_RATING CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CD_DEP_COUNT INT DEFAULT NULL
  , CD_DEP_EMPLOYED_COUNT INT DEFAULT NULL
  , CD_DEP_COLLEGE_COUNT INT DEFAULT NULL
  , PRIMARY KEY (CD_DEMO_SK ASC)
  )
  SALT USING 8 PARTITIONS
;

--- SQL operation complete.
>>showddl g_tpcds1x.CUSTOMER_ADDRESS;

CREATE TABLE TRAFODION.G_TPCDS1X.CUSTOMER_ADDRESS
  (
    CA_ADDRESS_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , CA_ADDRESS_ID CHAR(16) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , CA_STREET_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_STREET_NAME VARCHAR(60) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_STREET_TYPE CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_SUITE_NUMBER CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_CITY VARCHAR(60) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_COUNTY VARCHAR(30) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_STATE CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_ZIP CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
, CA_COUNTRY VARCHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , CA_GMT_OFFSET DECIMAL(5, 2) DEFAULT NULL
  , CA_LOCATION_TYPE CHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (CA_ADDRESS_SK ASC)
  )
  SALT USING 8 PARTITIONS
;

--- SQL operation complete.
>>showddl g_tpcds1x.DATE_DIM;

CREATE TABLE TRAFODION.G_TPCDS1X.DATE_DIM
  (
    D_DATE_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , D_DATE_ID CHAR(16) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , D_DATE DATE DEFAULT NULL
  , D_MONTH_SEQ INT DEFAULT NULL
  , D_WEEK_SEQ INT DEFAULT NULL
  , D_QUARTER_SEQ INT DEFAULT NULL
  , D_YEAR INT DEFAULT NULL
, D_DOW INT DEFAULT NULL
  , D_MOY INT DEFAULT NULL
  , D_DOM INT DEFAULT NULL
  , D_QOY INT DEFAULT NULL
  , D_FY_YEAR INT DEFAULT NULL
  , D_FY_QUARTER_SEQ INT DEFAULT NULL
  , D_FY_WEEK_SEQ INT DEFAULT NULL
  , D_DAY_NAME CHAR(9) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_QUARTER_NAME CHAR(6) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_HOLIDAY CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_WEEKEND CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_FOLLOWING_HOLIDAY CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_FIRST_DOM INT DEFAULT NULL
  , D_LAST_DOM INT DEFAULT NULL
  , D_SAME_DAY_LY INT DEFAULT NULL
  , D_SAME_DAY_LQ INT DEFAULT NULL
  , D_CURRENT_DAY CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
, D_CURRENT_WEEK CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_CURRENT_MONTH CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_CURRENT_QUARTER CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , D_CURRENT_YEAR CHAR(1) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (D_DATE_SK ASC)
  )
  SALT USING 8 PARTITIONS
;

--- SQL operation complete.
>>
>>select count(*) from g_tpcds1x.STORE_SALES;

(EXPR)
--------------------

             2880143

--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.STORE;

(EXPR)
--------------------

                  12

--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.CUSTOMER_DEMOGRAPHICS;

(EXPR)
--------------------

             1920800

--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.CUSTOMER_ADDRESS;

(EXPR)
--------------------

               50000

--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.DATE_DIM;

(EXPR)
--------------------

               73049

--- 1 row(s) selected.
>>
>>select count(*) from g_tpcds1x.CUSTOMER_DEMOGRAPHICS where cd_demo_sk is null;

(EXPR)
--------------------

                   0

--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.STORE_SALES where ss_cdemo_sk is null;

(EXPR)
--------------------

              129679

--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.STORE_SALES where ss_sales_price is null;

(EXPR)
--------------------

              129249

--- 1 row(s) selected.
>>
>>prepare x1 from
+>select count(*)
+>from g_tpcds1x.STORE_SALES,
+> g_tpcds1x.STORE,
+> g_tpcds1x.CUSTOMER_DEMOGRAPHICS,
+> g_tpcds1x.CUSTOMER_ADDRESS,
+> g_tpcds1x.DATE_DIM
+>where s_store_sk = ss_store_sk
+>and ss_sold_date_sk = d_date_sk
+>and
+>(
+>(
+>cd_demo_sk = ss_cdemo_sk
+>and
+>ss_sales_price between 100.00 and 200.00
+>)
+>-- (
+>-- cd_demo_sk = ss_cdemo_sk
+>-- and
+>-- ss_sales_price between 100.00 and 150.00
+>-- )
+>-- or
+>-- (
+>-- cd_demo_sk = ss_cdemo_sk
+>-- and
+>-- ss_sales_price between 150.00 and 200.00
+>-- )
+>)
+>and
+>(
+>(
+>ss_addr_sk = ca_address_sk
+>and
+>ss_net_profit between 100 and 200
+>)
+>);

--- SQL command prepared.
>>
>>explain options 'f' x1;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

19 . 20 root 1.00E+000
18 . 19 sort_partial_aggr_ro 1.00E+000
17 . 18 esp_exchange 1:4(hash2) 1.00E+000
16 . 17 sort_partial_aggr_le 1.00E+000
13 15 16 nested_join 5.67E+003
14 . 15 probe_cache 1.00E+000
. . 14 trafodion_scan DATE_DIM 1.00E+000
12 . 13 esp_exchange 4(hash2):4(hash2) 5.67E+003
9 11 12 nested_join 5.67E+003
10 . 11 probe_cache 1.00E+000
. . 10 trafodion_scan CUSTOMER_DEMOGRAPHIC 1.00E+000
8 2 9 hybrid_hash_join 5.67E+003
7 . 8 esp_exchange 4(hash2):4(hash2) 5.67E+003
4 6 7 nested_join 5.67E+003
5 . 6 probe_cache 1.00E+000
. . 5 trafodion_scan CUSTOMER_ADDRESS 1.00E+000
3 . 4 esp_exchange 4(hash2):4(hash2) 5.67E+003
. . 3 trafodion_scan STORE_SALES 5.67E+003
1 . 2 esp_exchange 4(rep-b):4(hash2) 1.20E+001
. . 1 trafodion_scan STORE 1.20E+001

--- SQL operation complete.
>>execute x1;

(EXPR)
--------------------

                6492

--- 1 row(s) selected.
>>
>>prepare x2 from
+>select count(*)
+>from g_tpcds1x.STORE_SALES,
+> g_tpcds1x.STORE,
+> g_tpcds1x.CUSTOMER_DEMOGRAPHICS,
+> g_tpcds1x.CUSTOMER_ADDRESS,
+> g_tpcds1x.DATE_DIM
+>where s_store_sk = ss_store_sk
+>and ss_sold_date_sk = d_date_sk
+>and
+>(
+>-- (
+>-- cd_demo_sk = ss_cdemo_sk
+>-- and
+>-- ss_sales_price between 100.00 and 200.00
+>-- )
+>(
+>cd_demo_sk = ss_cdemo_sk
+>and
+>ss_sales_price between 100.00 and 150.00
+>)
+>or
+>(
+>cd_demo_sk = ss_cdemo_sk
+>and
+>ss_sales_price between 150.00 and 200.00
+>)
+>)
+>and
+>(
+>(
+>ss_addr_sk = ca_address_sk
+>and
+>ss_net_profit between 100 and 200
+>)
+>);

--- SQL command prepared.
>>
>>explain options 'f' x2;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

19 . 20 root 1.00E+000
18 . 19 sort_partial_aggr_ro 1.00E+000
17 . 18 esp_exchange 1:4(hash2) 1.00E+000
16 . 17 sort_partial_aggr_le 1.00E+000
13 15 16 nested_join 5.67E+003
14 . 15 probe_cache 1.00E+000
. . 14 trafodion_scan DATE_DIM 1.00E+000
12 . 13 esp_exchange 4(hash2):4(hash2) 5.67E+003
9 11 12 nested_join 5.67E+003
10 . 11 probe_cache 1.00E+000
. . 10 trafodion_scan CUSTOMER_DEMOGRAPHIC 1.00E+000
8 2 9 hybrid_hash_join 5.67E+003
7 . 8 esp_exchange 4(hash2):4(hash2) 5.67E+003
4 6 7 nested_join 5.67E+003
5 . 6 probe_cache 1.00E+000
. . 5 trafodion_scan CUSTOMER_ADDRESS 1.00E+000
3 . 4 esp_exchange 4(hash2):4(hash2) 5.67E+003
. . 3 trafodion_scan STORE_SALES 5.67E+003
1 . 2 esp_exchange 4(rep-b):4(hash2) 1.20E+001
. . 1 trafodion_scan STORE 1.20E+001

--- SQL operation complete.
>>execute x2;

*** ERROR[8421] NULL cannot be assigned to a NOT NULL column.

--- 0 row(s) selected.

Tags: sql-exe
Changed in trafodion:
assignee: nobody → James Capps (james-capps)
Revision history for this message
Hans Zeller (hans-zeller) wrote :

Jim investigated this problem and found it to be a compiler problem.

The key expression for the “_SALT_” column of the inner table looks like this: HASH2PARTFUNC(cast(ss_cdemo_sk as int not null) …). This cast seems to trigger the error. Instead of a cast, we need to use a NARROW operator here and if we encounter a NULL value, then we need to ignore the row instead of giving an error.

Changed in trafodion:
assignee: James Capps (james-capps) → Hans Zeller (hans-zeller)
status: New → Confirmed
Revision history for this message
Hans Zeller (hans-zeller) wrote :

My script to reproduce the problem:

drop table STORE_SALES;
drop table CUSTOMER_DEMOGRAPHICS;

CREATE TABLE STORE_SALES
  (
    SS_ITEM_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SS_CDEMO_SK INT DEFAULT NULL
  , SS_NET_PROFIT DECIMAL(7, 2) DEFAULT NULL
  , PRIMARY KEY (SS_ITEM_SK ASC)
  )
  SALT USING 8 PARTITIONS
;

CREATE TABLE CUSTOMER_DEMOGRAPHICS
  (
    CD_DEMO_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
 , PRIMARY KEY (CD_DEMO_SK ASC)
  )
  SALT USING 8 PARTITIONS
;

insert into store_sales
  (SS_ITEM_SK, SS_CDEMO_SK, SS_NET_PROFIT)
values (1,1,180.84), (2,null,130.80);
insert into customer_demographics (CD_DEMO_SK)
values (1), (2);

--cqd pcode_opt_level 'off';
--cqd rangespec_transformation 'off';
--cqd attempt_esp_parallelism 'off';

control query shape groupby(nested_join(cut,cut));

prepare x2 from
-- display
select count(*) from STORE_SALES, CUSTOMER_DEMOGRAPHICS
WHERE
     ( cd_demo_sk = ss_cdemo_sk and ss_net_profit = 180.84 )
  OR ( cd_demo_sk = ss_cdemo_sk and ss_net_profit = 130.80 )
;

explain options 'f' x2;
execute x2;

control query shape cut;

Changed in trafodion:
status: Confirmed → In Progress
Revision history for this message
Hans Zeller (hans-zeller) wrote :

Fix was committed on 4/8/2015 with https://review.trafodion.org/1442

Changed in trafodion:
status: In Progress → Fix Committed
Revision history for this message
Weishiun Tsai (wei-shiun-tsai) wrote :
Download full text (15.6 KiB)

Verified on the v1.1.0rc0 (v0410) build, this problem has been fixed:

>>showddl g_tpcds1x.STORE_SALES;

CREATE TABLE TRAFODION.G_TPCDS1X.STORE_SALES
  (
    SS_SOLD_DATE_SK INT DEFAULT NULL
  , SS_SOLD_TIME_SK INT DEFAULT NULL
  , SS_ITEM_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SS_CUSTOMER_SK INT DEFAULT NULL
  , SS_CDEMO_SK INT DEFAULT NULL
  , SS_HDEMO_SK INT DEFAULT NULL
  , SS_ADDR_SK INT DEFAULT NULL
  , SS_STORE_SK INT DEFAULT NULL
  , SS_PROMO_SK INT DEFAULT NULL
  , SS_TICKET_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SS_QUANTITY INT DEFAULT NULL
  , SS_WHOLESALE_COST DECIMAL(7, 2) DEFAULT NULL
  , SS_LIST_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_SALES_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_DISCOUNT_AMT DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_SALES_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_WHOLESALE_COST DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_LIST_PRICE DECIMAL(7, 2) DEFAULT NULL
  , SS_EXT_TAX DECIMAL(7, 2) DEFAULT NULL
  , SS_COUPON_AMT DECIMAL(7, 2) DEFAULT NULL
  , SS_NET_PAID DECIMAL(7, 2) DEFAULT NULL
  , SS_NET_PAID_INC_TAX DECIMAL(7, 2) DEFAULT NULL
  , SS_NET_PROFIT DECIMAL(7, 2) DEFAULT NULL
  , PRIMARY KEY (SS_ITEM_SK ASC, SS_TICKET_NUMBER ASC)
  )
  SALT USING 8 PARTITIONS
;

-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION.G_TPCDS1X.STORE
_SALES TO QAUSER_USER WITH GRANT OPTION;

--- SQL operation complete.
>>showddl g_tpcds1x.STORE;

CREATE TABLE TRAFODION.G_TPCDS1X.STORE
  (
    S_STORE_SK INT NO DEFAULT NOT NULL NOT DROPPABLE
  , S_STORE_ID CHAR(16) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , S_REC_START_DATE DATE DEFAULT NULL
  , S_REC_END_DATE DATE DEFAULT NULL
  , S_CLOSED_DATE_SK INT DEFAULT NULL
  , S_STORE_NAME VARCHAR(50) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_NUMBER_EMPLOYEES INT DEFAULT NULL
  , S_FLOOR_SPACE INT DEFAULT NULL
  , S_HOURS CHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_MANAGER VARCHAR(40) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_MARKET_ID INT DEFAULT NULL
  , S_GEOGRAPHY_CLASS VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , S_MARKET_DESC VARCHAR(100) CHARACTER SET ISO88591
      COLLATE DEFAULT DEFAULT NULL
  , S_MARKET_MANAGER VARCHAR(40) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , S_DIVISION_ID INT DEFAULT NULL
  , S_DIVISION_NAME VARCHAR(50) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , ...

Changed in trafodion:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.