This looks like a bug to me. Can you please provide:
1. The definition of the table NETWORK_ELEMENT.
2. The definition of the column NETWORK_SET.PRIORITY3_NETWORK_ELEMENT.
3. The exact version of TimesTen being used (the full output of the ttVersion command).
Here are the details you asked for:
[timesten@noas-timesten-vm ~]$ ttVersion
TimesTen Release 184.108.40.206.22 (64 bit Linux/x86_64) (tt1122:53396) 2017-06-20T21:06:23Z
Instance admin: timesten
Instance home directory: /opt/timesten/TimesTen/tt1122
Group owner: timesten
Daemon home directory: /opt/timesten/data
Please note that PRIORITY3_NETWORK_ELEMENT maps to PRIORITY3_ASBC in the below data model:
Table TEST.NETWORK_ELEMENT: Columns: *ID NUMBER (32) NOT NULL OSS_ID VARCHAR2 (32) INLINE NOT NULL TYPE NUMBER (16) NOT NULL NAME VARCHAR2 (32) INLINE NOT NULL IP VARCHAR2 (32) INLINE PORT VARCHAR2 (8) INLINE LAST_UPDATED_BY VARCHAR2 (32) INLINE LAST_UPDATED_DTIME DATE Table TEST.NETWORK_SET: Columns: *ID NUMBER (8) NOT NULL OSS_ID VARCHAR2 (32) INLINE NOT NULL PRIORITY1_SLEE NUMBER (8) NOT NULL PRIORITY2_SLEE NUMBER (8) PRIORITY1_ASBC NUMBER (8) NOT NULL PRIORITY2_ASBC NUMBER (8) PRIORITY3_ASBC NUMBER (8) PRIORITY4_ASBC NUMBER (8) LAST_UPDATED_BY VARCHAR2 (32) INLINE LAST_UPDATED_DTIME DATE
The reason why we added this query is that PRIORITYX_YYYY should be a foreign key mapped to NETWORK_ELEMENT.ID but the incompatible data types (NUMBER(8) vs NUMBER(32)) prevent the foreign key from being created. We plan on fixing this but there are a few constraints which prevent us from doing this immediately.
Coming back to the original queries, which result set is the expected result?
The correct result set is of course the one with 0 rows found. Rows where PRIORITY3_NETWORK_ELEMENT is NULl should not appear in the result set.
This is a serious bug which is still present in the most recent patch (220.127.116.11.35) but does not reproduce in the new 18.1 release.
I will raise a bug on this to get it addressed in 11.2.2 but in the meantime please use the 'is not null' workaround.
Sorry for the inconvenience.
Thank you for getting back to me so quickly and confirming the correct result. I will keep an eye out for any future patch releases.