Re: Need some sleep, 18c XE

Looks good. The condition returns TRUE when the right hand side subquery returns no rows:

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Comparison-Conditions.html#GUID-828576BF-E606-4EA6-B94B-BFF48B67F927

[image: изображение.png]

(select f0.id from foo f0 where f0.id > f1.id) returns no rows for f1.id=3. Therefore, f1.id > all(empty rowset) = TRUE.

We can even verify that NULL can be greater/less/equal/non-equal than all values from an empty rowset simultaneously (reminds Russell's paradox) :)

select * from dual where null^=all(select * from dual where null=null);

D
-
X

On Thu, 10 Dec 2020 at 18:19, Michael D O'Shea/Woodward Informatics Ltd < woodwardinformatics_at_strychnine.co.uk> wrote:

> Chaps, it has been a long day and I have been chasing a bug in ORM
> generated SQL all afternoon. I demo it minimally below using some contrived
> code.
>
> Do I just need some sleep? 3, really?
>
> Mike
> Woodward Informatics Ltd
>
>
> SQL>
> SQL>
> SQL> desc foo
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ID NUMBER
>
> SQL> select * from foo;
>
> ID
> ----------
> 1
> 2
> 3
>
>
> SQL> select f1.id from foo f1 where f1.id > all (select f0.id from foo f0
> where f0.id > f1.id);
>
> ID
> ----------
> 3
>
> SQL>
> SQL>
> SQL>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l





image/png attachment: ______________________.png