I've again ran into an inconsistency with the values returned by a virtual column in a SELECT statement. The first time I revised the SQL statement to get around the issue. This time we've found an easier SQL statement to test with. I tried setting up a new set of tables for this to reproduce the issue, but the limited data I added to the table does not reproduce the issue. We can open a ticket with Oracle support, but I thought I'd throw the question out here first to see if anyone has seen this before.
First, we are running on
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Here are two queries, where the virtual column works correctly the first time and incorrectly the second time.
SQL> SELECT stat_cd, statute, OFFNS_TTL
2 from parent_code_table sc
3 WHERE exists (select NULL
4 from child_code_table srx
5 where sc.STAT_CD = srx.STAT_CD)
6 AND stat_cd IN ('1064', '1765', '1065')
7 -- AND stat_cd IN ('1064', '1765', '1065', '1069')
8 ;
STAT STATUTE OFFNS_TTL
---- --------------- ----------
1064 45-5-505 45
1065 45-5-507 45
1765 45-5-504(3)(a) 45
SQL> SELECT stat_cd, statute, OFFNS_TTL
2 from parent_code_table sc
3 WHERE exists (select NULL
4 from child_code_table srx
5 where sc.STAT_CD = srx.STAT_CD)
6 -- AND stat_cd IN ('1064', '1765', '1065')
7 AND stat_cd IN ('1064', '1765', '1065', '1069')
8 ;
STAT STATUTE OFFNS_TTL
---- --------------- ------------------------
1064 Not Found 45
1065 Not Found 45
1069 Not Found 45
1765 Not Found 45
The only difference between those two statements are the number of values in the IN clause. The virtual column is defined as
COALESCE("OFFNS_TTL"||CASE WHEN ("OFFNS_CHPTR" IS NOT NULL) THEN ('-'||"OFFNS_CHPTR") END ||CASE WHEN ("OFFNS_SCTN" IS NOT NULL) THEN ('-'||"OFFNS_SCTN") END ||"OFFNS_SUBSECTION"||"OFFNS_PGPH_NBR"||"OFFNS_SUB_PGPH_NBR"||"OFFNS_SNTNCE",'Not Found')
In short, it formats and concatenates 7 columns together and if no data is present in all 7 columns, it should return "Not Found"
As you can see by the second second query, it is returning Not Found, even though data exists in the OFFNS_TTL column. I can pick any three rows for that table and they return the right result as seen by the first query. I add one more entry into the IN clause and suddenly the wrong result is returned.
Does anyone have any insight into what is going on here?
I forgot to add in that if I remove the EXISTS check, then it returns the right results when the IN clause has 4 values in it. There is some odd interaction going on here it appears.