Hi, Its version 19C of Oracle.
A varchar2 column in the table was holding mixed set of values( like '1000',''MATCH','FINE','10-mar-2023' etc) and surprisingly a query having a predicate with to_date function on this column(like to_date(col1)>trunc(sysdate)) was working fine , but suddenly started giving the below error(Ora-01861) .
Upon investigation we found that it was simply because of the query plan change which looks to be a bit odd initially. But then we found, the query was having other tables in the JOIN condition and the optimizer was putting the JOIN order in such a way that this table was being queried last in the Join order. And when it was coming to this table the filtering was already done on other columns of this table , such that only valid date values (like '10-mar-2023') were passed to the to_date(col1) function. So it was working fine.
However , with sudden change in plan when this table is used by the optimizer as a driving table it tries to evaluate to_date(col1)>trunc(sysdate) first making it fail with "ora-01861 literal does not match format string". My question is , how should we handle such a scenario?