[
https://issues.apache.org/jira/browse/PHOENIX-7492?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Istvan Toth updated PHOENIX-7492:
---------------------------------
Component/s: core
> 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
> Components: core
> Reporter: Abhradeep Kundu
> Assignee: Abhradeep Kundu
> Priority: Major
> Fix For: 5.3.0, 5.1.4, 5.2.2
>
>
> Steps to Reproduce
> 1) Create below tables
> {code:java}
> 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;
> {code}
>
> 2) Upsert values
> {code:java}
> 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');{code}
> 3) Run query
> {code:java}
> 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');{code}
> 4) Result
> {code:java}
> Caused by: java.lang.NullPointerException
> at
> org.apache.phoenix.cache.ServerCacheClient.removeServerCache(ServerCacheClient.java:380)
> at
> org.apache.phoenix.cache.ServerCacheClient.access$000(ServerCacheClient.java:89)
> at
> org.apache.phoenix.cache.ServerCacheClient$ServerCache.close(ServerCacheClient.java:211)
> at
> org.apache.phoenix.util.SQLCloseables.closeAllQuietly(SQLCloseables.java:59)
> at org.apache.phoenix.util.SQLCloseables.closeAll(SQLCloseables.java:46)
> at
> org.apache.phoenix.execute.BaseQueryPlan$1.close(BaseQueryPlan.java:229)
> at
> org.apache.phoenix.iterate.DelegateResultIterator.close(DelegateResultIterator.java:41)
> at
> org.apache.phoenix.iterate.LookAheadResultIterator$1.close(LookAheadResultIterator.java:50)
> at
> org.apache.phoenix.iterate.UnionResultIterators.close(UnionResultIterators.java:87)
>
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
