Ah, IC... So you mean that the short-circuit operator behave differently in C# and SQL, as explained in here: http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx? Quoting from there:
*If C++/C#/VB has short-circuiting why can't SQL Server have it? * To truly answer this let's take a look at how both work with conditions. *C++/C#/VB all have short circuiting defined in the language specifications to speed up code execution. Why bother evaluating N OR conditions when the first one is already true or M AND conditions when the first one is already false. * *We as developers have to be aware that SQL Server works differently. It is a cost based system. To get the optimal execution plan for our query the query processor has to evaluate every where condition and assign it a cost. These costs are then evaluated as a whole to form a threshold that must be lower than the defined threshold SQL Server has for a good plan. *If the cost is lower than the defined threshold the plan is used, if not the whole process is repeated again with a different mix of condition costs. Cost here is either a scan or a seek or a merge join or a hash join etc... Because of this the short-circuiting as is available in C++/C#/VB simply isn't possible. You might think that forcing use of index on a column counts as short circuiting but it doesn't. It only forces the use of that index and with that shortens the list of possible execution plans. The system is still cost based. *As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.* Well, if NH had to have such knowledge about it (in the way how SQL Server execution engine decides it), then I think it would really complicate NH code base. I'm not aware if NH code base currently accomodates/handles something like that. But still despite of all these differences in nature, logical/conditional operator is different from bitwise one, whether in C#/VB or SQL. Still I think that the closest operator in C#/VB matches "SQL logical operator" is "C#/VB logical operator", although they're different in nature in how they perform short circuiting. If you use bitwise operator in C#/VB to map to logical operator in SQL, then what operator do you think that can be used in C#/VB to map to bitwise operator in SQL? Also, looking at your failed example queries in C#, how often do you think one would experience such problematic queries? I personally never face such situation, so I'm not sure how that would be an issue for me nor I'd be bothered by. I mean, is there any impact/consequence for doing so that you observe that would make a caution/warning or even a dangerous situation? I can see none as the situation can still be handled. > 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. > And as you said it, I think it's one fact must be understood that query semantics can be different among Linq to objects, Linq to database (Linq to SQL, EF, Linq to NH, etc.), and other Linq providers. Now back to the OP question, I still see that he has to use "OrElse" instead of "Or" in VB as he apparently uses "||" instead of "|" in C#. And I can't see how this short-circuiting issue would be relevant anymore, don't you think so? On Wed, Dec 7, 2011 at 4:23 PM, 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. > > > -- 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.
