Phoenix select query issue in case of Null values/Non-Null values

Question

Whenever I'm performing select query on some columns (few columns are null and others are not null) I'm getting empty table in response in Phoenix

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;

I've checked the table and records are there(should be returning 6 records) but getting empty table.

I've tried indexing on columns, reordering of columns in select statement

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)
);

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)
);

Answer 1

There is no builtin to_timestamp(). Is that a Stored Function? Anyway you probably don't need to convert a datetime to get a timestamp.

These may help with performance:

t:  INDEX(TRANSACTION_DATE, ID,  PARTY_KEY)
tt:  INDEX(ALERT_ID, TRANSACTION_KEY,  TRIGGERED_TYPOLOGY)

Please provide SHOW CREATE TABLE for the two tables.

Please show us some of the rows that should have been returned.

What version of MySQL? Unless it is 8.0, I recommend changing to

order by  t.TRANSACTION_DATE desc, t.ID desc