[
https://issues.apache.org/jira/browse/PHOENIX-7492?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Abhradeep Kundu updated PHOENIX-7492:
-------------------------------------
Description:
Steps to Reproduce
1) Create below tables
CREATE TABLE ACCOUNT (
ACCOUNT_IDENTIFIER VARCHAR(100) NOT NULL,
CRN_NUMBER_TEXT VARCHAR(100),
ORIGINAL_CURRENCY_CODE VARCHAR(100),
OUTSTANDING_BALANCE_AMOUNT DECIMAL(20,4),
SOURCE_SYSTEM_CODE VARCHAR(6) NOT NULL,
ACCOUNT_TYPE_CODE VARCHAR(100)
CONSTRAINT pk PRIMARY KEY (ACCOUNT_IDENTIFIER, SOURCE_SYSTEM_CODE)
) SALT_BUCKET=4;
CREATE TABLE EXCHANGE_RATE (
CURRENCY_CODE VARCHAR(3) NOT NULL,
SPOT_RATE DECIMAL(15,9),
EXPANDED_SPOT_RATE DECIMAL(19,9),
RECORD_LAST_UPDATE_DATE TIMESTAMP
CONSTRAINT pk PRIMARY KEY (CURRENCY_CODE)
)SALT_BUCKETS=4;
CREATE TABLE CUSTOMER (
CUSTOMER_IDENTIFIER VARCHAR(100),
SOURCE_SYSTEM_CODE VARCHAR(100) NOT NULL,
CRN_NUMBER_TEXT VARCHAR(100),
CONSTRAINT pk PRIMARY KEY (CUSTOMER_IDENTIFIER, SOURCE_SYSTEM_CODE)
) SALT_BUCKETS=4;
2) Upsert values
UPSERT INTO ACCOUNT VALUES ('ACC_1', 'CRN_1', 'IDR', 999, 'SRC_1', 'ATC_1');
UPSERT INTO EXCHANGE_RATE VALUES ('IDR', 0.53233436, 0.198919644, '2024-07-03');
UPSERT INTO CUSTOMER VALUES ('CUST_1', 'SRC_1','CRN_1');
3) Run query
SELECT
ca1.ORIGINAL_CURRENCY_CODE AS ORIGINAL_CURRENCY_CODE
FROM
ACCOUNT ca1
LEFT JOIN EXCHANGE_RATE er1 ON
ca1.ORIGINAL_CURRENCY_CODE = er1.CURRENCY_CODE
WHERE
(ca1.ACCOUNT_IDENTIFIER,
ca1.SOURCE_SYSTEM_CODE) IN (
SELECT
ca.ACCOUNT_IDENTIFIER,
ca.SOURCE_SYSTEM_CODE
FROM
ACCOUNT ca
WHERE
ca.CRN_NUMBER_TEXT IN (
SELECT
CRN_NUMBER_TEXT
FROM
CUSTOMER
WHERE
CUSTOMER_IDENTIFIER = 'CUST_2'
AND SOURCE_SYSTEM_CODE='SRC_1'))
AND ca1.ACCOUNT_TYPE_CODE IN ('ATC_1')
UNION ALL
SELECT
ca1.ORIGINAL_CURRENCY_CODE AS ORIGINAL_CURRENCY_CODE
FROM
ACCOUNT ca1
WHERE
ca1.ACCOUNT_TYPE_CODE IN ('ATC_1');
was:
Steps to Reproduce
1) Create below tables
CREATE TABLE ACCOUNT (
ACCOUNT_IDENTIFIER VARCHAR(100) NOT NULL,
CRN_NUMBER_TEXT VARCHAR(100),
ORIGINAL_CURRENCY_CODE VARCHAR(100),
OUTSTANDING_BALANCE_AMOUNT DECIMAL(20,4),
SOURCE_SYSTEM_CODE VARCHAR(6) NOT NULL,
ACCOUNT_TYPE_CODE VARCHAR(100)
CONSTRAINT pk PRIMARY KEY (ACCOUNT_IDENTIFIER, SOURCE_SYSTEM_CODE)
) SALT_BUCKET=4;
CREATE TABLE EXCHANGE_RATE (
CURRENCY_CODE VARCHAR(3) NOT NULL,
SPOT_RATE DECIMAL(15,9),
EXPANDED_SPOT_RATE DECIMAL(19,9),
RECORD_LAST_UPDATE_DATE TIMESTAMP
CONSTRAINT pk PRIMARY KEY (CURRENCY_CODE)
)SALT_BUCKETS=4;
CREATE TABLE CUSTOMER (
CUSTOMER_IDENTIFIER VARCHAR(100),
SOURCE_SYSTEM_CODE VARCHAR(100) NOT NULL,
CRN_NUMBER_TEXT VARCHAR(100),
CONSTRAINT pk PRIMARY KEY (CUSTOMER_IDENTIFIER, SOURCE_SYSTEM_CODE)
) SALT_BUCKETS=4;
2) Upsert values
UPSERT INTO ACCOUNT VALUES ('ACC_1', 'CRN_1', 'IDR', 999, 'SRC_1', 'ATC_1');
UPSERT INTO EXCHANGE_RATE VALUES ('IDR', 0.53233436, 0.198919644, '2024-07-03');
UPSERT INTO CUSTOMER VALUES ('CUST_1', 'SRC_1','CRN_1');
> Subquery with join and union not working together when one side of union
> finds no result
> ----------------------------------------------------------------------------------------
>
> Key: PHOENIX-7492
> URL: https://issues.apache.org/jira/browse/PHOENIX-7492
> Project: Phoenix
> Issue Type: Bug
> Reporter: Abhradeep Kundu
> Assignee: Abhradeep Kundu
> Priority: Major
>
> Steps to Reproduce
> 1) Create below tables
> CREATE TABLE ACCOUNT (
> ACCOUNT_IDENTIFIER VARCHAR(100) NOT NULL,
> CRN_NUMBER_TEXT VARCHAR(100),
> ORIGINAL_CURRENCY_CODE VARCHAR(100),
> OUTSTANDING_BALANCE_AMOUNT DECIMAL(20,4),
> SOURCE_SYSTEM_CODE VARCHAR(6) NOT NULL,
> ACCOUNT_TYPE_CODE VARCHAR(100)
> CONSTRAINT pk PRIMARY KEY (ACCOUNT_IDENTIFIER, SOURCE_SYSTEM_CODE)
> ) SALT_BUCKET=4;
> CREATE TABLE EXCHANGE_RATE (
> CURRENCY_CODE VARCHAR(3) NOT NULL,
> SPOT_RATE DECIMAL(15,9),
> EXPANDED_SPOT_RATE DECIMAL(19,9),
> RECORD_LAST_UPDATE_DATE TIMESTAMP
> CONSTRAINT pk PRIMARY KEY (CURRENCY_CODE)
> )SALT_BUCKETS=4;
> CREATE TABLE CUSTOMER (
> CUSTOMER_IDENTIFIER VARCHAR(100),
> SOURCE_SYSTEM_CODE VARCHAR(100) NOT NULL,
> CRN_NUMBER_TEXT VARCHAR(100),
> CONSTRAINT pk PRIMARY KEY (CUSTOMER_IDENTIFIER, SOURCE_SYSTEM_CODE)
> ) SALT_BUCKETS=4;
> 2) Upsert values
> UPSERT INTO ACCOUNT VALUES ('ACC_1', 'CRN_1', 'IDR', 999, 'SRC_1', 'ATC_1');
> UPSERT INTO EXCHANGE_RATE VALUES ('IDR', 0.53233436, 0.198919644,
> '2024-07-03');
> UPSERT INTO CUSTOMER VALUES ('CUST_1', 'SRC_1','CRN_1');
> 3) Run query
> SELECT
> ca1.ORIGINAL_CURRENCY_CODE AS ORIGINAL_CURRENCY_CODE
> FROM
> ACCOUNT ca1
> LEFT JOIN EXCHANGE_RATE er1 ON
> ca1.ORIGINAL_CURRENCY_CODE = er1.CURRENCY_CODE
> WHERE
> (ca1.ACCOUNT_IDENTIFIER,
> ca1.SOURCE_SYSTEM_CODE) IN (
> SELECT
> ca.ACCOUNT_IDENTIFIER,
> ca.SOURCE_SYSTEM_CODE
> FROM
> ACCOUNT ca
> WHERE
> ca.CRN_NUMBER_TEXT IN (
> SELECT
> CRN_NUMBER_TEXT
> FROM
> CUSTOMER
> WHERE
> CUSTOMER_IDENTIFIER = 'CUST_2'
> AND SOURCE_SYSTEM_CODE='SRC_1'))
> AND ca1.ACCOUNT_TYPE_CODE IN ('ATC_1')
> UNION ALL
> SELECT
> ca1.ORIGINAL_CURRENCY_CODE AS ORIGINAL_CURRENCY_CODE
> FROM
> ACCOUNT ca1
> WHERE
> ca1.ACCOUNT_TYPE_CODE IN ('ATC_1');
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
