Strange negative results in select Queries

Problem

I lately ran into problems that a select returned an unexpected negative results und certain condions, e.g. I expected 1 but it returned -1.

select --
       1
  from dual;

Reason

It turns out that this is a behaviour seen only in clients using SQLPlus or the same libraries as SQLPlus or something of the sort. From https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/REMARK.html

A "–" at the end of a REMARK line is treated as a line continuation character.

To me, this is a flaw in the SQLPlus design as, to the best of my knowledge, the SQL standard does not inhibt empty line comments or defines a line continuation character (like \ in bash, if after \ there immediately follows line break). It is even worse as it does not work consistently this way. The following should, if - is line continuation character, throw a syntax error at both instances of the -- but works perfectly fine.

--
select 1
  from --
  dual;

Solution

Do not use empty line comments. Place at least one none whitespace character behind the --.

Why does the were clause "never" trigger the use of an index?

Problem

A DBA of mine recently told me that NULLs would not end up in indexes, that is if the index is a single column index. Likewise, an entry in compound indexes is omitted if all the columns are NULL. The implication seems to be that if your where clause asks for the presence of NULL, Oracle cannot use an index because the information whether an attribute is NULL or not is not known to the index.

That means that the record in the index is missing. The logic behind that it seems, that if we do not know the value we have no statement at all.

To me, this is quite bullshit.

• If I ask someone how much money is on his bank account and he answers he does not know it is a difference to if he does not answer at all. If the "We do not know" was a neglectable statement, I dare say, the SQL standard would not define expression like "IS NULL".
• To omit a record in the index if all NULL is not consistent to a tables getting a record if all columns are NULL.

Workaround

• Use only compound indexes with at least one not-nullable column, possibly as last column as inert weight.
• Use possibly function based indexes

Further reading at Ask Tom