OR predicate causes a query to return error 8421 with NOT NULL column
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.
showddl g_tpcds1x.STORE;
showddl g_tpcds1x.
showddl g_tpcds1x.
showddl g_tpcds1x.DATE_DIM;
select count(*) from g_tpcds1x.
select count(*) from g_tpcds1x.STORE;
select count(*) from g_tpcds1x.
select count(*) from g_tpcds1x.
select count(*) from g_tpcds1x.DATE_DIM;
select count(*) from g_tpcds1x.
select count(*) from g_tpcds1x.
select count(*) from g_tpcds1x.
prepare x1 from
select count(*)
from g_tpcds1x.
g_tpcds1x.STORE,
g_tpcds1x.
g_tpcds1x.
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.
g_tpcds1x.STORE,
g_tpcds1x.
g_tpcds1x.
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.
CREATE TABLE TRAFODION.
(
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_
, 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.
(
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.
CREATE TABLE TRAFODION.
(
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_
, CD_CREDIT_RATING CHAR(10) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL
, CD_DEP_COUNT INT DEFAULT NULL
, CD_DEP_
, CD_DEP_
, PRIMARY KEY (CD_DEMO_SK ASC)
)
SALT USING 8 PARTITIONS
;
--- SQL operation complete.
>>showddl g_tpcds1x.
CREATE TABLE TRAFODION.
(
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.
(
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.
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.STORE;
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.DATE_DIM;
(EXPR)
-------
--- 1 row(s) selected.
>>
>>select count(*) from g_tpcds1x.
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.
(EXPR)
-------
--- 1 row(s) selected.
>>select count(*) from g_tpcds1x.
(EXPR)
-------
--- 1 row(s) selected.
>>
>>prepare x1 from
+>select count(*)
+>from g_tpcds1x.
+> g_tpcds1x.STORE,
+> g_tpcds1x.
+> g_tpcds1x.
+> 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_
17 . 18 esp_exchange 1:4(hash2) 1.00E+000
16 . 17 sort_partial_
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_
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)
-------
--- 1 row(s) selected.
>>
>>prepare x2 from
+>select count(*)
+>from g_tpcds1x.
+> g_tpcds1x.STORE,
+> g_tpcds1x.
+> g_tpcds1x.
+> 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_
17 . 18 esp_exchange 1:4(hash2) 1.00E+000
16 . 17 sort_partial_
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_
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.
Changed in trafodion: | |
assignee: | nobody → James Capps (james-capps) |
Changed in trafodion: | |
status: | Confirmed → In Progress |
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.