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