Small correction: the C# program must be (operands switched for the
last select):
class Program {
static void Main(string[] args) {
List<int> table1 = new List<int> {1};
List<int> tablewithmorethanoneentry = new List<int> {1,
2};
try {
var result = from i in table1
where i == tablewithmorethanoneentry.Single()
select i;
// throws Exception
result.ToList();
Console.WriteLine("OK");
} catch (Exception e) {
Console.WriteLine("Failed: " + e.ToString());
}
try {
var result = from i in table1
where i ==
tablewithmorethanoneentry.Single() || true
select i;
// throws because the problematic condition is on lhs
result.ToList();
Console.WriteLine("OK");
} catch (Exception e) {
Console.WriteLine("Failed: " + e.ToString());
}
try {
var result = from i in table1
where true || i ==
tablewithmorethanoneentry.Single()
select i;
// works because the problematic condition is on rhs
result.ToList();
Console.WriteLine("OK");
} catch (Exception e) {
Console.WriteLine("Failed: " + e.ToString());
}
Console.ReadLine();
}
}
On 7 Dez., 10:23, CSharper <[email protected]> wrote:
> It's just that there are no exactly equivalent operators like the C#
> short circuit && and || operators in SQL.
> Developers should be aware of the fact that there are subtle semantic
> differences in Linq queries depending on the target system.
>
> In SQL, only the first Select of the following sequence throws an
> exception (at least if the query optimizer tries to avoid
> "unnecessary" work):
> create table table1(id int)
> insert into table1 values (1)
>
> create table tablewithmorethanoneentry(id int)
> insert into tablewithmorethanoneentry values (1)
> insert into tablewithmorethanoneentry values (2)
>
> -- throws Exception
> select *
> from table1
> where id = (select id from tablewithmorethanoneentry)
> -- works although the problematic condition is on lhs
> select *
> from table1
> where id = (select id from tablewithmorethanoneentry) or 1=1
> -- works
> select *
> from table1
> where 1=1 or id = (select id from tablewithmorethanoneentry)
>
> In C#, the first two Selects (they seem to do the same as the SQL
> queries above) throw exception because the operator has a defined
> execution order for the operands:
> class Program {
> static void Main(string[] args) {
> List<int> table1 = new List<int> {1};
> List<int> tablewithmorethanoneentry = new List<int> {1,
> 2};
>
> try {
> var result = from i in table1
> where i == tablewithmorethanoneentry.Single()
> select i;
> // throws Exception
> result.ToList();
> Console.WriteLine("OK");
> } catch (Exception e) {
> Console.WriteLine("Failed: " + e.ToString());
> }
>
> try {
> var result = from i in table1
> where i ==
> tablewithmorethanoneentry.Single() || true
> select i;
> // throws because the problematic condition is on lhs
> result.ToList();
> Console.WriteLine("OK");
> } catch (Exception e) {
> Console.WriteLine("Failed: " + e.ToString());
> }
>
> try {
> var result = from i in table1
> where i ==
> tablewithmorethanoneentry.Single() || true
> select i;
> // works because the problematic condition is on rhs
> result.ToList();
> Console.WriteLine("OK");
> } catch (Exception e) {
> Console.WriteLine("Failed: " + e.ToString());
> }
>
> Console.ReadLine();
> }
> }
>
> On 6 Dez., 09:45, Maximilian Raditya <[email protected]> wrote:
>
>
>
> > On Tue, Dec 6, 2011 at 2:52 PM, CSharper <[email protected]>wrote:
>
> > > ...
> > > The following Linq query will throw an exception:
> > > list1.Where(a => a.ID == listWithMoreThanOneEntry.Select(b =>
> > > b.ID).Single() || true)
>
> > > Only a select like ...
> > > select *
> > > from Table1
> > > where ID = (select ID from TableWithMoreThanOneEntry) or 1=0
> > > ... will cause an exception because the execution engine is forced do
> > > evaluate the complex condition.
> > > ...
>
> > I don't quite understand what you meant. I just try a similar query using
> > Linq and it works just fine without exception. The semantic is preserved,
> > including the RHS operand (1=0). The generated SQL includes everything
> > being instructed.
>
> > On Tue, Dec 6, 2011 at 2:52 PM, CSharper <[email protected]>wrote:
>
> > > I did not question the difference between bitwise and logical Boolean
> > > operators in programming languages.
>
> > > I just wanted to spot that there is no equivalent in SQL of the well-
> > > defined short-circuit operators in C# where you can e.g. first check
> > > if a value is not null and then evaluate some properties of the value
> > > because the execution order is defined. If you have a subselect
> > > returnung multiple results like ...
> > > select *
> > > from Table1
> > > where ID = (select ID from TableWithMoreThanOneEntry) or 1=1
> > > ... you are most probably lucky because optimizer chooses to first
> > > check the simple condition and returns successfully if the result of
> > > the whole operator can be deduced. It's no matter if you have the
> > > simple condition on the left or right hand side.
>
> > > The following Linq query will throw an exception:
> > > list1.Where(a => a.ID == listWithMoreThanOneEntry.Select(b =>
> > > b.ID).Single() || true)
>
> > > Only a select like ...
> > > select *
> > > from Table1
> > > where ID = (select ID from TableWithMoreThanOneEntry) or 1=0
> > > ... will cause an exception because the execution engine is forced do
> > > evaluate the complex condition.
>
> > > But I must admit that there is a whole bunch of semantic differences
> > > between C# Linq to objects and the way an SQL engine treats a query
> > > that is at a first glance equivalent to the Linq query.
>
> > > The whole point is: I don't say somebody must change this in NHib but
> > > it's worth discussing about such semantic details and differences
> > > because it strengthens the awareness that e.g. Linq to X is different
> > > than Linq to Y. There are always trade-offs because of the diverse
> > > target systems interpreting the Linq expression trees.
>
> > > On 5 Dez., 12:52, David Schmitt <[email protected]> wrote:
> > > > On 05.12.2011 11:51, CSharper wrote:
>
> > > > > Yes, but they are meant for integral data types where they actually
> > > > > make a difference. The documentation link you have in there says it
> > > > > works with two bits in 2008 R2 also but I tried with 2008 and it did
> > > > > not work with bits there.
> > > > > But as I wrote: an SQL AND or OR operator is no AndElse or OrElse
> > > > > operator because the order of evaluation is not determined by the
> > > > > operator but by the execution engine. And I don't know if the engine
> > > > > really guarantees the execution of both paths with the bitwise | or&
> > > > > operators shown in your links if both arguments are of bit type.
>
> > > > > So for Boolean I would say the right thing to do is support both
> > > > > variants of .NET operators all with using the logical operators in
> > > > > SQL.
>
> > > > As long as you're using only side-effect-free functions in your boolean
> > > > clauses (which is, like, everything in standard SQL), short-circuiting
> > > > does not change the outcome of the result and is therefore not relevant
> > > > to the discussion.
>
> > > > The differences between bitwise and boolean operators on the other side
> > > > are big, well-defined and mathematically sound. Nothing to discuss here
> > > > either.
>
> > > > The mapping from the boolean/bitwise concepts to And/AndElse in VB is
> > > > pretty much written in stone, since Microsoft made that decision and
> > > > masses of VB programmers learnt which is which.
>
> > > > So, if you cannot be convinced of the rightness of using the proper
> > > > operator for the operation you want, you are free to change NHibernate's
> > > > source to your liking, but do not expect agreement from the rest of the
> > > > world, or pity if you get a publication onhttp://thedailywtf.com/
>
> > > > Best Regards, David
>
> > > > > On 5 Dez., 10:19, Maximilian Raditya<[email protected]> wrote:
> > > > >> On Mon, Dec 5, 2011 at 3:44 PM, CSharper<[email protected]
> > > >wrote:
>
> > > > >>> Maybe that's the way it's currently working with NHibernate but for
> > > > >>> Boolean operands, both operators should work in a query language
> > > > >>> targeting SQL in the background.
>
> > > > >> Doesn't SQL have both operators: logical (
> > >http://msdn.microsoft.com/en-us/library/ms189773.aspx) and bitwise (
> > >http://msdn.microsoft.com/en-us/library/ms176122.aspx)?
>
> > > > >>> The operator behaviour of SQL is neither exactly like the short
> > > > >>> circuit&& (or ||) nor the bitwise Boolean& (or |) operators. SQL
> > > > >>> engines might do lazy evaluation like the short circuit operators
> > > > >>> but
> > > > >>> the order of evaluation is chosen by the query optimizer. In
> > > > >>> contrast
> > > > >>> to the&& operator where the left hand side is guaranteed to be
> > > > >>> evaluated first and the right hand side is only evaluated if
> > > required,
> > > > >>> the query execution might do it the other way round depending on the
> > > > >>> complexity and usable indexes available in the operands.
>
> > > > >> I don't know how query optimizer works internally, but I do know that
> > > they
> > > > >> are different and have different semantics.
>
> > > > >>> Maybe it would be better to not force the VB programmers to always
> > > add
> > > > >>> this dumb "Else" word in the queries and allow the Boolean operator
> > > of
> > > > >>> the C# programmer's choice.
>
> > > > >> I'm not sure who force to do so, but all I can say is that they have
> > > > >> different semantics and need to be differentiated. I presume your
> > > concern
> > > > >> comes from the confusion of "AND" in SQL is a logical operator, while
> > > "AND"
> > > > >> in VB is a bitwise one, and it becomes unnatural to you.
> > > > >> If you're curious to know why they design it that way, you should
> > > probably
> > > > >> ask MS team directly :D.
>
> > > > >> --
> > > > >> Regards,
>
> > > > >> Maximilian Haru Raditya
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > > "nhusers" group.
> > > To post to this group, send email to [email protected].
> > > To unsubscribe from this group, send email to
> > > [email protected].
> > > For more options, visit this group at
> > >http://groups.google.com/group/nhusers?hl=en.
>
> > --
> > Regards,
>
> > Maximilian Haru Raditya
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
