(educating) Quiz for today

Question

4.50/5 (2 votes)

See more:

Most likely this post is because of too much spare time :) But if you're interested in SQL you may find this compelling. I find this issue amusing (and a bit confusing if you don't know about pre-defined conditions or condition cost).

If you think about the evaluation order of conditions combined with OR in SQL. What is the correct evaluation order for this statement, does it fail?

SQL

SELECT 1
WHERE 1/0=42 OR 1=1

Feel free to change the order and test the results (in your favorite DBMS).

What about this one (written for Sql Server):

SQL

SELECT 1
WHERE 1/(SELECT COUNT(*) FROM sysobjects WHERE type = 'NotPossible')=2 OR 2<5

The behavior will be a bit different among different databases.

Posted 8-Apr-11 9:58am

Wendelius

Add a Solution

Comments

Sergey Alexandrovich Kryukov 8-Apr-11 16:12pm

How come you can ever get too much spare time? I want it, too :-)
--SA

Wendelius 8-Apr-11 16:18pm

You can lease it, if we can agree on the payment :)

Sergey Alexandrovich Kryukov 8-Apr-11 19:48pm

Thank you, your offer is being considered... :-)
--SA

DaveAuld 8-Apr-11 16:52pm

Are both results = 1 ?

Wendelius 8-Apr-11 17:19pm

Well to be exact the other one is dívision by zero, but if you run it against for example SQL Server it's not going to fail, no matter in which order you put the conditions... The funny part is that they always produce a result. Thinking about programming languages like C# it doesn't make sense :)

GlobX 11-Apr-11 0:39am

Hmmm... but thinking about it logically it shouldn't fail - the OR specifies that I only wanted "one of" the conditions to evaluate to true. In this case, one is invalid (throws an exception) and the other returns true. Therefore, one is true, ergo it should succeed. Logically, that is...

Whether or not it should is another question entirely :)

Wendelius 11-Apr-11 9:49am

Exactly, and the funny thing is that it doesn't matter which way you put the condition, it always succeeds :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Meech · Accepted Answer · 2011-04-12T03:14:00

I just ran it on Oracle 9.2.0.5 and it always returns 1. :)

SQL> select 1 from dual where 1/0=42 or 1=1;
         1
----------
         1
SQL> edit
Wrote file afiedt.buf
  1  select 1 from dual
  2*  where 1=1 or 1/0=42
  3  ;
         1
----------
         1
SQL> edit
Wrote file afiedt.buf
  1  select 1 from dual
  2*  where 1=1 or 1/(select count(*) from all_tables where table_name = 'NOTHING')=42
  3  ;
         1
----------
         1
SQL> edit
Wrote file afiedt.buf
  1  select 1 from dual
  2*  where 1/(select count(*) from all_tables where table_name = 'NOTHING')=42 or 1=1
  3  ;
         1
----------
         1
SQL>

I hope this doesn't mean that Oracle is exceptional or rather exceptionless. :)