Phoenix查询含空值/非空值列时返回空表问题排查

问题详情 #

在Phoenix中执行包含空值与非空值列的SELECT查询时，返回结果为空表，但已确认表中存在6条符合条件的记录。尝试过为相关列创建索引、调整SELECT语句中的列顺序等操作，问题仍未解决。

执行的查询语句 #

select  t.PARTY_KEY as PK_on_t, t.ID as I1_on_t, tt.ALERT_ID as AI_on_tt,
        tt.TRIGGERED_TYPOLOGY as TT_on_tt, tt.TRANSACTION_KEY as TK_on_tt
    from  TRANSACTIONS t
    inner join  TRIGGERED_TRANSACTIONS tt  ON t.ID = tt.TRANSACTION_KEY
    where  t.TRANSACTION_DATE >= to_timestamp('2012-03-01 00:00:00.0')
      and  t.TRANSACTION_DATE <= to_timestamp('2023-04-06 23:59:59.999')
      and  tt.ALERT_ID = 'RI0634AAAEF'
    order by  t.TRANSACTION_DATE desc, t.ID
    limit  10 offset 0;

相关表结构 #

TRANSACTIONS表 #

CREATE TABLE IF NOT EXISTS TRANSACTIONS (
ID VARCHAR NOT NULL,
PARTY_KEY VARCHAR,
TRANSACTION_DATE TIMESTAMP NOT NULL,
PRODUCT_CODE VARCHAR,
ACCOUNT_CURRENCY VARCHAR,
AMOUNT DOUBLE,
TRANSACTION_CURRENCY VARCHAR,
TRANSACTION_CURRENCY_AMOUNT DOUBLE,
LOCAL_CURRENCY_EQUIVALENT DOUBLE,
IS_CREDIT BOOLEAN,
ACCOUNT_ID VARCHAR,
TRANSACTION_TYPE_CODE VARCHAR,
TRANSACTION_TYPE_DESCRIPTION VARCHAR,
BENEFICIARY VARCHAR,
ORIGINATOR_ADDRESS VARCHAR,
ORIGINATOR_BANK_COUNTRY_CODE VARCHAR,
BENEFICIARY_ADDRESS VARCHAR,
BENEFICIARY_BANK_COUNTRY_CODE VARCHAR,
REMITTANCE_COUNTRY VARCHAR,
REMITTANCE_PAYMENT_DETAILS VARCHAR,
CHEQUE_NO VARCHAR,
TELLER_ID VARCHAR,
YOUR_REFERENCE VARCHAR,
OUR_REFERENCE VARCHAR,
BANK_INFO VARCHAR,
COUNTRY_OF_TRANSACTION VARCHAR,
MCC VARCHAR,
MERCHANT_NAME VARCHAR,
ORIGINATOR VARCHAR,
ATM_ID VARCHAR,
ATM_LOCATION VARCHAR,
COUNTER_PARTY VARCHAR,
SWIFT_MSG_TYPE VARCHAR,
SWIFT_MSG_INFO VARCHAR,
TD_NUMBER VARCHAR,
TRANCHE_NO VARCHAR,
CREDIT_CARD_NUMBER VARCHAR,
HIGH_RISK_COUNTRY_FLAG BOOLEAN,
DESTINATION_RISK_FLAG BOOLEAN,
SOURCE_RISK_FLAG BOOLEAN,
DESTINATION_OF_FUNDS VARCHAR,
SOURCE_OF_FUNDS VARCHAR,
INSTRUMENT VARCHAR,
BENEFICIARY_TYPE VARCHAR,
C2C_LINKAGE_FLAG BOOLEAN,
BENEFICIARY_ADVERSE_FLAG BOOLEAN,
BENEFICIAL_BANK_NAME VARCHAR,
ORIGINATOR_BANK_NAME VARCHAR,
OPP_ACCOUNT_NUMBER VARCHAR,
BANK_BRANCH_CODE VARCHAR,
CONSTRAINT TRANSACTIONS_PK PRIMARY KEY (ID, TRANSACTION_DATE)
);

TRIGGERED_TRANSACTIONS表 #

CREATE TABLE IF NOT EXISTS TRIGGERED_TRANSACTIONS (
ALERT_ID VARCHAR NOT NULL,
TRIGGERED_TYPOLOGY BIGINT NOT NULL,
TRANSACTION_KEY VARCHAR NOT NULL,
CONSTRAINT TRIGGERED_TRANSACTIONS_PK PRIMARY KEY (ALERT_ID, TRIGGERED_TYPOLOGY, TRANSACTION_KEY)
);

内容的提问来源于stack exchange，提问作者Sukanta Mondal