Hi,

I have two problem issues with FieldCompareSetPredicate.

First

I want to do a query like :

select * from a where not exists (select null from b where b.id = a.id)

The documentation say that the two next query are equivalent.

Field IN (SELECT OtherField FROM OtherTable WHERE Foo=2)
EXISTS (SELECT * FROM OtherTable)

In Oracle and Mysql this assertion is not true. You need to have relation between the master query and the query in the subquery (like my query).

Second

As the FieldCompareSetPredicate doesn't do the job with exists I decided to use In operator.

I wanted to do a query like :

select * from a where a.id not in (select b.id from b)

I choose In operator of FieldCompareSetPredicate and set the Negate property to true.

The query executed by LLBLGEN with this setting is

select * from a where not a.id in (select b.id from b)

The problem is this two query are not the same

select * from a where a.id not in (select b.id from b)
select * from a where not a.id in (select b.id from b)

How can I do a NOT IN query?

I post this here because I tried out this with the version 2. Is this issues occur with version 1.0.2005.1?

WHat's the code you tried for the EXISTS query? As this predicate works exactly the same as in 1.0.2005.1. You could also use the NotEqualAny operator.

and: select * from a where a.id not in (select b.id from b) select * from a where not a.id in (select b.id from b)

ARE the same queries as in, they result in the same resultset, namely when a.id isn't part of the set select b.id from b. I also don't recall that a NOT IN is valid SQL, but maybe I'm mistaken.

Here the code for the exists. How can I join the subquery to the parent query to have a query like :

select * from role where not exists (select null from user_role where user_role.id = role.id and user_role.id = 1)

Dim compare As ORMSupportClasses.FieldCompareSetPredicate

compare = New ORMSupportClasses.FieldCompareSetPredicate
                                             (HelperClasses.RoleFields.RoleId, Nothing, _
                                              HelperClasses.RoleUtilisateurFields.RoleId, Nothing,          
                                              ORMSupportClasses.SetOperator.Exist, Nothing)

compare.Negate = True
compare.SetFilter = HelperClasses.RoleUtilisateurFields.UtilisateurId = utilisateurId

I add compare to a RelationPredicateBucket here.

For the IN I didn't test it with oracle but with MYSQL this two queries don't return same resultset

select * from a where a.id not in (select b.id from b) select * from a where not a.id in (select b.id from b)

In the oracle sql reference the not is between the expression and the subquery.

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/conditions5a.htm#1013449

(For your information, NOT IN operator is not equal to <> ANY but is equal to <> ALL)

matlaf wrote:

Here the code for the exists. How can I join the subquery to the parent query to have a query like :

select * from role where not exists (select null from user_role where user_role.id = role.id and user_role.id = 1)

Dim compare As ORMSupportClasses.FieldCompareSetPredicate

compare = New ORMSupportClasses.FieldCompareSetPredicate
                                             (HelperClasses.RoleFields.RoleId, Nothing, _
                                              HelperClasses.RoleUtilisateurFields.RoleId, Nothing,          
                                              ORMSupportClasses.SetOperator.Exist, Nothing)

compare.Negate = True
compare.SetFilter = HelperClasses.RoleUtilisateurFields.UtilisateurId = utilisateurId

I add compare to a RelationPredicateBucket here.

You've to set the objectalias of the set field in the set predicate, so you create a co-related subquery as it is called.

For the IN I didn't test it with oracle but with MYSQL this two queries don't return same resultset

select * from a where a.id not in (select b.id from b) select * from a where not a.id in (select b.id from b)

Odd, as semantically, they represent the same thing. On sqlserver they give the same resultset.

Also earlier you said the documentation was wrong about EXISTS, it's not, as both require a co-related subquery if you want the subquery to work in that context.

In the oracle sql reference the not is between the expression and the subquery.

http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/conditions5a.htm#1013449

Ok, though it's the same thing: A isn't part of subset B vs. NOT (A = part of subset B).

(For your information, NOT IN operator is not equal to <> ANY but is equal to <> ALL)

NOT equal ANY means not equal all. IN (...) is the same as equal any(...). NOT IN (...) means the same as NOT EQUAL ANY(...), as that expression is true if there's no pair (x, y), where x is part of the subset and Y is the value compared.

Or I miss something/am not thinking straight.

Either way: set the objectalias of the set field to alias the table in the subquery, and then add a predicate to the subquery which produces the filter to bind the subquery to the main query, so something like:

SELECT * FROM Foo WHERE Foo.A IN (SELECT A From Bar B where B.X = Foo.X)

this then can be written as: SELECT * FROM Foo WHERE EXISTS (SELECT * FROM Bar B where B.X = Foo.X)

I also would like to add: select * from a where a.id not in (select b.id from b) select * from a where not a.id in (select b.id from b) they give the same resultset if the subquery is the same. So if I do: SELECT * FROM Orders WHERE CustomerID NOT IN (select customerid from customers where country='Germany') then I get the same resultset (and execution plan) as when I do: SELECT * FROM Orders WHERE NOT CustomerID IN (select customerid from customers where country='Germany')

namely all orders of customers who don't live in germany.

I didn't add a NOT IN operator, as it would be redundant due to this reason. NOT field IN (...) is a perfectly correct expression, it's true when field IN (...) is false. which is the same as when field NOT IN (...) is true.

You answer my question for the exists. I just have to create a corelated query with field alias. I also understand that the doc is ok because Exists doesn't need to be corelated query.

For the IN operator :

Will you correct it to have "Expr not in (subquery)" and not "not Expr in (subquery)"?

And for the in, any and all relation the mysql reference say :

However, NOT IN is not an alias for <> ANY, but for <> ALL. See Section 13.2.8.4, “Subqueries with ALL”.

http://dev.mysql.com/doc/refman/4.1/en/any-in-some-subqueries.html

See also Oracle sql reference : http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/conditions5a.htm#1013449

I test it on sqlserver, I get the same results. If mysql gets different results, then that's up to mysql. Mysql sometimes gives odd results, I can't solve that problem.

I can add an operator NotIn, though taht doesn't solve a thing, as it results in the same set logic, as I described above. NOT Field IN (...) is a legal expression and gives the valid results, simply because it states that the whole expression is true if field isn't in the subquery, correct? So that's the same as field NOT IN (...).

For 'ANY' I referred to the booksonline of sqlserver.

I test it on sqlserver, I get the same results. If mysql gets different results, then that's up to mysql. Mysql sometimes gives odd results, I can't solve that problem.

Yes it's mysql problem. I test it with Oracle and get same results as SQLServer.

Can you give to us some warning in the doc or in the code that Mysql doesn't support "not expr in (subquery)" and propose to use NotEqualAll?

Haha, ok I'll add the warning. But I'll better add a warning like "Using MySql can seriously hurt your project".

Seriously: MySql is capable of doing 'transaction's though also is it possible to have a transaction roll back but still leave committed results in the tables. And more sillyness. But indeed, having a bit of a warning there might be in order. I'll add the remark

Thanks,