12 Replies Latest reply: Jan 8, 2013 9:23 AM by 983397 RSS

    subquery returns 0 rows-----HELP

    983397
      I need to understand something.

      please follow my points:-

      1-If we try to make a NOT IN condition, and one value is NULL, the main query return no result, because the NOT IN condition evaluates to FALSE "NULL" "UNKNOWN".
      example:-
      SELECT 'True' FROM employees
      WHERE department_id NOT IN (10, 20, NULL);
      /*
      this query returns no rows, simply because the condition is parsed like this
      department_id != 10 AND department_id != 20 AND department_id != null
      */

      ^^^I have no question regarding this point and it is quite obvious.

      2-If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL.


      example:-
      create table subq_null_test
      (
      num number(4),
      val varchar2(7)
      );

      insert into subq_null_test
      values (1,'one');
      insert into subq_null_test
      values (2, (select 'two' from dual where 2=1));
      insert into subq_null_test
      values (3, 'three');
      commit;

      and by
      select * from subq_null_test;
      we would see a NULL inserted as value for number 2
      NUM VAL
      1     one
      2     (null)
      3     three

      /*
      so far so good, indeed the 0 row subquery returned NULL
      */


      ^^^Also I CAN'T have a point here.
      ============================================
      but lets look at these 3 queries

      -------->FIRST
      select department_id, last_name
      from employees
      where department_id not in (10,20,null)
      ;
      /*no rows selected*/

      --------->SECOND
      select department_id, last_name
      from employees
      where department_id not in (10,20,(select 10 from dual where 2=1))
      ;
      /*no rows selected*/

      -------->THIRD
      select department_id, last_name
      from employees
      where department_id not in (select 10 from dual where 2=1)
      ;
      /*ROWS returned*/


      my question is:-
      WHY FIRST and SECOND queries behaved as expected, while the THIRD didn't ???

      -I had a look at the execution plan, and it didn't helped me "am a beginner anyways"
      -I know its something related to the process or parsing the conditions, but am totally unable to locate it...

      Any help would be so much appreciated,
      Thanks for all.
      Ghazal.
        • 1. Re: subquery returns 0 rows-----HELP
          Frank Kulash
          Hi, Ghazal,

          Welcome to the forum!
          980394 wrote:
          I need to understand something.

          please follow my points:-

          1-If we try to make a NOT IN condition, and one value is NULL, the main query return no result, because the NOT IN condition evaluates to FALSE "NULL" "UNKNOWN".
          FALSE is not the same as UNKNOWN.
          If any of the rows in the NOT IN subquery contain NULL, then the NOT IN operator will return UNKNOWN.
          example:-
          SELECT 'True' FROM employees
          WHERE department_id NOT IN (10, 20, NULL);
          /*
          this query returns no rows, simply because the condition is parsed like this
          department_id != 10 AND department_id != 20 AND department_id != null
          */

          ^^^I have no question regarding this point and it is quite obvious.
          It may be quite obvious to you, but many other people have a hard time understanding that.
          2-If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL.


          example:-
          create table subq_null_test
          (
          num number(4),
          val varchar2(7)
          );

          insert into subq_null_test
          values (1,'one');
          insert into subq_null_test
          values (2, (select 'two' from dual where 2=1));
          insert into subq_null_test
          values (3, 'three');
          commit;

          and by
          select * from subq_null_test;
          we would see a NULL inserted as value for number 2
          NUM VAL
          1     one
          2     (null)
          3     three

          /*
          so far so good, indeed the 0 row subquery returned NULL
          */
          That's right; if a scalar sub-query finds no rows, then the "value" of the entire scalar sub-query expression is NULL.
          >
          ^^^Also I CAN'T have a point here.
          ============================================
          but lets look at these 3 queries

          -------->FIRST
          select department_id, last_name
          from employees
          where department_id not in (10,20,null)
          ;
          /*no rows selected*/

          --------->SECOND
          select department_id, last_name
          from employees
          where department_id not in (10,20,(select 10 from dual where 2=1))
          Okay; you're using a scalar sub-query here, and the scalar sub-query returns NULL.
          ;
          /*no rows selected*/

          -------->THIRD
          select department_id, last_name
          from employees
          where department_id not in (select 10 from dual where 2=1)
          ;
          /*ROWS returned*/


          my question is:-
          WHY FIRST and SECOND queries behaved as expected, while the THIRD didn't ???
          NOT IN evaluates to UNKNOWN if the right operand contains any NULLs. In the 3rd query, the right operand (which is a regular sub-query, not a scalar sub-query) does not contain any NULLs. In fact, it does not contain anything at all.
          A query that returns 0 rows is not the same as a query that returns 1 row, even if that 1 row contains NULL.
          -I had a look at the execution plan, and it didn't helped me "am a beginner anyways"
          -I know its something related to the process or parsing the conditions, but am totally unable to locate it...
          No, it has nothing to do with parsing, just how the NOT IN operator was designed to work.
          If the right operand to NOT IN is a sub-query, and that sub-query produces no rows, then NOT IN evaluates to TRUE, regardless of what the left operand is, even NULL.
          • 2. Re: subquery returns 0 rows-----HELP
            NSK2KSN
            select department_id, last_name
            from employees
            where department_id not in (select 10 from dual where 2=1)

            the query here returns rows for those departments which are not in the subquery result set.

            (12,34,56,66) not in (34, 56) -> meaning this will return the records for departments 12, 66

            (12, 34, 56, 66) not in (<no rows returned from the subquery result set>) -> meaning this will return for all the rows.

            select 10 from dual where 2 =1 -> returns 0 rows and yes department_id's which are there in employees table which are not there in subquery result set are returned
            • 3. Re: subquery returns 0 rows-----HELP
              Solomon Yakobson
              Think about the dirrerence between empty list and list containing a null element.

              SY.
              • 4. Re: subquery returns 0 rows-----HELP
                983397
                Hello Frank Kulash, NSK2KSN.
                I really appreciate the efforts you are giving to make me understand :), but let me discuss this with you as in spite of your great work I still didn't got it :(


                Mr.NSK2KSN
                /***
                (12,34,56,66) not in (34, 56) -> meaning this will return the records for departments 12, 66

                (12, 34, 56, 66) not in (<no rows returned from the subquery result set>) -> meaning this will return for all the rows.

                ***/
                -i appreciate the try to make me understand it logically
                -but still
                (12, 34, 56, 66) not in (<no rows returned from the subquery result set>)
                is NOT what i am asking about....
                my question according to your logic IS
                (12, 34, 56, 66) not in (<NULL returned from the subquery result set>)
                which is
                (12, 34, 56, 66) not in (<UNNOWN>)
                which supposed to be unknown answer.


                Mr.Frank Kulash
                /***
                NOT IN evaluates to UNKNOWN if the right operand contains any NULLs. In the 3rd query, the right operand (which is a regular sub-query, not a sclar sub-query)not contain any NULLs. In fact, it doesn't contain anything at all.
                A query that returns 0 rows is not the same as a query that returns 1 row, even if that 1 row contains NULL.
                ***/

                but sir, it is a scalar subquery indeed "at least in my eyes"
                -select 10 from dual where 2=1 >it is even the same query used in THIRD as well as SECOND <
                -its a query selecting one column one cell one field...and it returned nothing...it did return a NULL in previous examples as we saw.

                /***
                No, it has nothing to do with parsing, just how the NOT IN operator was designed to work.
                If the right operand to NOT IN is a sub-query, and that sub-query produces no rows, then NOT IN evaluates to TRUE, regardless of what the left operand is, even NULL.
                ***/
                QUITE enough answer to my question, i like absolute rules...
                but let me wounder here,,,,did ORACLE tried to solve a bug, by ENGINEERING their own bug?!
                I believe its the absolute rule "not a matter for argue" that when we ask the system to give us a value not in a result set containing a null value "unknown value", the simple answer is also null "unknown"...
                this should be the rule, the absolute rule we all should stick to.

                as you said
                /***
                It may be quite obvious to you, but many other people have a hard time understanding that.
                ***/
                well, its just logic, and so it is obvious.


                =========
                please guys, do help me and see the execution plan by your own and explain to me back :)...
                FIRST
                >>> DEPARTMENT_ID<>TO_NUMBER(NULL)
                >>> DEPARTMENT_ID<>10
                >>> DEPARTMENT_ID<>20

                SECOND
                >>> DEPARTMENT_ID<>TO_NUMBER(NULL)
                THIRD
                >>>NULL IS NOT NULL
                >>>LNVL(:B<>10)




                this LNVL(:B<>10)
                is indeed the key....it is making me agree with Mr. Frank Kulash theory (NOT IN) is designed to work just like this...
                but me myself would see it as an engineered bug...


                also guys, would someone please guides me to one of those papers which is showing how IN condition is actually parsed into EXISTS condition (i remember i read somewhere it was designed like this in oracle 10g and later to overcome an earlier bug).

                Thank you gents.

                Ghazal.
                • 5. Re: subquery returns 0 rows-----HELP
                  Frank Kulash
                  Hi,
                  980394 wrote:
                  ...
                  /***
                  NOT IN evaluates to UNKNOWN if the right operand contains any NULLs. In the 3rd query, the right operand (which is a regular sub-query, not a sclar sub-query)not contain any NULLs. In fact, it doesn't contain anything at all.
                  A query that returns 0 rows is not the same as a query that returns 1 row, even if that 1 row contains NULL.
                  ***/

                  but sir, it is a scalar subquery indeed "at least in my eyes"
                  -select 10 from dual where 2=1 >it is even the same query used in THIRD as well as SECOND <
                  -its a query selecting one column one cell one field...and it returned nothing...it did return a NULL in previous examples as we saw.
                  Not all queries that produce (at most) 1 row and (exactly) one column are scalar sub-queries. Where it occurs in context is important.
                  For example, this produces 1 row and 1 column, but it is not a scalar sub-query:
                  SELECT  *
                  FROM    dual;
                  The following does not contain a scalar sub-query:
                  SELECT  COUNT (*)
                  FROM    (
                              SELECT  empno
                              FROM    scott.emp
                              WHERE   ename = 'ALLEN'
                          )
                  ;
                  >
                  /***
                  No, it has nothing to do with parsing, just how the NOT IN operator was designed to work.
                  If the right operand to NOT IN is a sub-query, and that sub-query produces no rows, then NOT IN evaluates to TRUE, regardless of what the left operand is, even NULL.
                  ***/
                  QUITE enough answer to my question, i like absolute rules...
                  but let me wounder here,,,,did ORACLE tried to solve a bug, by ENGINEERING their own bug?!
                  I believe its the absolute rule "not a matter for argue" that when we ask the system to give us a value not in a result set containing a null value "unknown value", the simple answer is also null "unknown"...
                  this should be the rule, the absolute rule we all should stick to.
                  That seems inconsistent to me, too. I would find it much more logical if IN (and NOT IN) alwyas evaluated to UNKNOWN when the left operand was NULL, regardless of what the right operand was. Empirically, that is not how Oracle behaves.
                  I don't know why they implemented it that way.
                  • 6. Re: subquery returns 0 rows-----HELP
                    983397
                    Yes guys I believe its as I said and its all about this LNNVL...

                    -As a newbie, i didn't knew about this LNNVL function
                    its mechanism is explained here
                    http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions035.htm
                    http://www.techonthenet.com/oracle/functions/lnnvl.php
                    obviously it is being used here..
                    And obviously Mr. Frank Kulash is right, its just designed to work like this.
                    And here comes the fun when we say that Mr. NSK2KSN is ALSO right regarding his logic
                    /*(12, 34, 56, 66) not in (<no rows returned from the subquery result set>)*/...simple, fair, and understandable logic
                    but I cant buy it!! :(..

                    Still, as a stubborn..
                    -this is double standard
                    how could we treat a FACT as rule somewhere.
                    but somewhere else we makes it an exception.
                    I may look funny to everybody but i would still say, when we talk about logic we supposed to be talking about ABSOLUTE RULES "CAN NOT be divided".

                    I see this as handmade bug:::
                    -ORACLE developers are not end users, they should be able to completely understand their code capabilities, and one simple thing is EXCLUSIVELY include their NVL >or any NULL handling mechanism< in their code whenever they are using NOT IN condition

                    -ORACLE including of the LNNVL function in the process of parsing NOT IN condition while right-hand operand is a subquery, is doing no good at all, it is a try to fix a "so called problem" but in fact it is not a problem at all as its simply one logic rule, taking in consideration that in other cases when the right-hand operand is a value of NULL the rule is applied.


                    Sounds ridiculous a newcomer is giving an advice to ORACLE but...
                    That is all what I can say, as far as I understood how it is working. If there is still something I am missing please clarify.

                    Ghazal.
                    • 7. Re: subquery returns 0 rows-----HELP
                      983397
                      Hello again Mr. Frank Kulash
                      /*
                      That seems inconsistent to me, too. I would find it much more logical if IN (and NOT IN) alwyas evaluated to UNKNOWN when the left operand was NULL, regardless of what the right operand was. Empirically, that is not how Oracle behaves.
                      I don't know why they implemented it that way.
                      */

                      -Regarding the the main question behind this thread, indeed its inconsistent.
                      and if it is all about LNNVL function during parsing our NOT IN condition with a query as our right-hand operand, it is bad idea, and I report it to ORACLE as a bug and it should be deprecated.

                      -Regarding your point
                      "I would find it much more logical if IN (and NOT IN) alwyas evaluated to UNKNOWN when the left operand was NULL"
                      No Sir.
                      simply because
                      1-IN condition is being evaluated as OR
                      2-NOT IN condition is being evaluated as AND
                      for example, when we say:-
                      -1.1
                      select last_name from employees where department_id in (10,20,null);
                      our condition is parsed like this
                      (department_id = 10 OR department_id = 20 OR department_id =null)
                      so it evaluates to
                      (true OR true OR null)
                      which have final result of TRUE
                      BUT<
                      -2.1
                      select last_name from employees where department_id not in (10,20,null);
                      our condition is parsed like this
                      (department_id != 10 AND department_id != 20 AND department_id !=null)
                      so it evaluates to
                      (true AND true AND null)
                      which have final result of NULL

                      this is if we take in consideration the 3 state logic
                      FALSE and NULL = false
                      TRUE and NULL = null
                      FALSE or NULL = null
                      TRUE or NULL = true
                      FALSE and TRUE= false
                      FALSE or TRUE = true


                      So actually, ORACLE did great job in totally ignoring the nulls when we talk about IN condition "by parsing it into exists" , they did the great job of letting the logic goes just as it is and that's all

                      and this is what I am asking for the point of this thread...let logic goes just as it is.."ask me does 1,2,3 NOT IN NULL "i don't know what" and i would say I do NOT know so its a null" regardless this null comes from absolute value of null or from a query or call it as whatever you want.

                      Thanks, I am enjoying the topic and looking for more explanations and discussions.
                      Ghazal.

                      Edited by: 980394 on Jan 7, 2013 7:54 PM

                      Edited by: 980394 on Jan 7, 2013 7:55 PM

                      Edited by: 980394 on Jan 7, 2013 7:55 PM

                      Edited by: 980394 on Jan 7, 2013 7:58 PM
                      • 8. Re: subquery returns 0 rows-----HELP
                        Frank Kulash
                        Hi, Ghazal,
                        980394 wrote:
                        ... -Regarding your point
                        "I would find it much more logical if IN (and NOT IN) alwyas evaluated to UNKNOWN when the left operand was NULL"
                        No Sir.
                        simply because
                        1-IN condition is being evaluated as OR
                        2-NOT IN condition is being evaluated as AND
                        for example, when we say:-
                        -1.1
                        select last_name from employees where department_id in (10,20,null);
                        our condition is parsed like this
                        (department_id = 10 OR department_id = 20 OR department_id =null)
                        We're talking about what happens when the left operand (department_id in this case) is NULL, right?
                        so it evaluates to
                        (true OR true OR null)
                        No, it evaluates to (UNKNOWN OR UNKNOWN OR UNKNOWN), which is UNKNOWN.
                        NULL = x is UNKNOWN, no matter what x is.
                        which have final result of TRUE
                        BUT<
                        -2.1
                        select last_name from employees where department_id not in (10,20,null);
                        our condition is parsed like this
                        (department_id != 10 AND department_id != 20 AND department_id !=null)
                        so it evaluates to
                        (true AND true AND null)
                        No. Again, if department_id is NULL, then it evaluates to (UNKNOWN AND UNKNOWN AND UNKNOWN), which is UNKNOWN.
                        NULL != x is UNKNOWN, no matter what x is.
                        Also, (UNKNOWN AND TRUE) is UNKNOWN. See the <a=href"http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions004.htm#sthref2854">truth tables</a> in the SQL Language manual.
                        which have final result of NULL

                        this is if we take in consideration the 3 state logic
                        FALSE and NULL = null
                        TRUE and NULL = null
                        FALSE or NULL = false
                        TRUE or NULL = true
                        FALSE and TRUE= false
                        FALSE or TRUE = true
                        When you say NULL in this section, do you mean UNKNOWN?
                        • 9. Re: subquery returns 0 rows-----HELP
                          983397
                          Mr. Frank Kulash please forgive my hasty reply in the last reply i sent. you was talking about the left-hand operand but my answer was discussing the right-hand operand.

                          regarding your point.
                          /*
                          That seems inconsistent to me, too. I would find it much more logical if IN (and NOT IN) alwyas evaluated to UNKNOWN when the left operand was NULL, regardless of what the right operand was. Empirically, that is not how Oracle behaves.
                          I don't know why they implemented it that way.
                          */
                          this is EXACTLY what ORACLE is doing....
                          take a look at these two examples please
                          select 'BANG' from dual where (1,null) not in ((1,2),(2,3));
                          select 'BANG' from dual where (1,null) not in ((1,2),(2,3),(1,null));
                          just as you wanted "IN (and NOT IN) alwyas evaluated to UNKNOWN when the left operand was NULL, regardless of what the right operand was", and ORACLE does it.

                          Edited by: 980394 on Jan 7, 2013 8:11 PM
                          • 10. Re: subquery returns 0 rows-----HELP
                            983397
                            Hello Frank Kulash
                            We're talking about what happens when the left operand (department_id in this case) is NULL, right?
                            I believe I corrected this in my last reply, My apologies once again :)
                            No, it evaluates to (UNKNOWN OR UNKNOWN OR UNKNOWN), which is UNKNOWN.
                            No Sir.
                            if we look at that example
                            select last_name from employees where department_id in (10,20,null);
                            which will be parsed into
                            (department_id = 10 OR department_id = 20 OR department_id =null)
                            1-and lets say that our first row in the employees table if for the employee who works in department_id = 10
                            SO, our condition is evaluates like this
                            (true OR FALSE OR null "UNKNOWN")
                            which is TURE as final result
                            2-and lets say that our second row in the employees table if for the employee who works in department_id = 20
                            SO, our condition is evaluates like this
                            (FALSE OR TRUE OR null "UNKNOWN")
                            which is TURE as final result
                            3-and lets say that our third row in the employees table if for the employee who works in department_id = 30
                            SO, our condition is evaluates like this
                            (FALSE OR FALSE OR null "UNKNOWN")
                            which is FALSE as final result
                            SO this query will indeed succeed in fetching employees who works in departments with department_id 10 and 20
                            No. Again, if department_id is NULL, then it evaluates to (UNKNOWN AND UNKNOWN AND UNKNOWN), which is UNKNOWN.
                            I believe our misunderstanding regarding left-side and right-side operand is clear now.
                            When you say NULL in this section, do you mean UNKNOWN?
                            yes Sir. i mean and i see NULL = UNKNOWN = I DON'T KNOW.

                            Edited by: 980394 on Jan 7, 2013 8:23 PM

                            Edited by: 980394 on Jan 7, 2013 8:24 PM

                            Edited by: 980394 on Jan 7, 2013 8:28 PM

                            Edited by: 980394 on Jan 7, 2013 8:32 PM

                            Edited by: 980394 on Jan 7, 2013 8:32 PM
                            • 11. Re: subquery returns 0 rows-----HELP
                              Frank Kulash
                              Hi,
                              Ghazal-OCA wrote:
                              Hello Frank Kulash
                              We're talking about what happens when the left operand (department_id in this case) is NULL, right?
                              I believe I corrected this in my last reply, My apologies once again :)
                              Yes; while I was writing my reply, you corrected it.
                              Now it seems like you're making the same mistake again.
                              No, it evaluates to (UNKNOWN OR UNKNOWN OR UNKNOWN), which is UNKNOWN.
                              No Sir.
                              if we look at that example
                              select last_name from employees where department_id in (10,20,null);
                              which will be parsed into
                              (department_id = 10 OR department_id = 20 OR department_id =null)
                              1-and lets say that our first row in the employees table if for the employee who works in department_id = 10
                              SO, our condition is evaluates like this
                              (true OR FALSE OR null "UNKNOWN")
                              Once again, I was only talking about the situation where department_id is NULL.
                              Of course
                              "x IN y" can be TRUE, FALSE or UNKNOWN, depending of what x and y are. All I was saying is that
                              "NULL IN y" can be FALSE (depending on y), even though
                              "NULL = q" is always UNKNOWN, regardless of what q is, and I find that a little inconsistent.

                              Edited by: Frank Kulash on Jan 8, 2013 7:09 AM
                              More precisely, what I said was that
                              "NULL NOT IN y" can be TRUE (depending on y).
                              • 12. Re: subquery returns 0 rows-----HELP
                                983397
                                Hi again
                                Yes; while I was writing my reply, you corrected it.Now it seems like you're making the same mistake again.
                                I believe I had clarified it enough.
                                Once again, I was only talking about the situation where department_id is NULL.Of course "x IN y" can be TRUE, FALSE or UNKNOWN, depending of what x and y are. All I was saying is that "NULL IN y" can be FALSE (depending on y), even though "NULL = q" is always UNKNOWN, regardless of what q is, and I find that a little inconsistent.
                                -now we are on the same side "I hope :)...jk"...well yea we are talking about the department_id "left-hand" operand is NULL.

                                -Regarding YOUR point, I can't agree with you Sir.
                                I see it pretty much consistent that when the left-hand operand is NULL,,,it is always UNKNOWN even if the right-side operand is a null.

                                I would even go further and say if there is a using of word "regardless", it wold be like this
                                if the left-hand operand is NULL the condition evaluates to UNKNOWN no matter what is in the right-hand operand REGARDLESS what condition we are using IN or NOT IN.

                                so
                                "x IN y" can be TRUE, FALSE or UNKNOWN, depending of what x and y are. All I was saying is that
                                so far so good
                                "NULL IN y" can be FALSE (depending on y), even though
                                No, it is ALWAYS UNKNOWN 'you can call is FALSE'.
                                "NULL = q" is always UNKNOWN, regardless of what q is, and I find that a little inconsistent.
                                I find it okay.

                                -"NULL = q" is always UNKNOWN regardless of what q is, is perfect and consistent indeed.
                                -"NULL in (q, p, d)" is always UNKNOWN.
                                -"NULL NOT in (q, p, d)" is always UNKNOWN.

                                I would again dare and go further and say
                                -whenever we have NULL on the left-hand operand
                                result of the condition is only 2 cases and can never be 3
                                1-UNKNOWN :- if we use "=, !=, IN, NOT IN, or anything may exists"
                                2-TRUE :- if we use "IS"
                                -this case of NULL as our left-hand operand can never have a FALSE case...
                                ORACLE does do this for us by her own, and thanks ORACLE for that...alot.
                                select 'BANG' from dual where null is null;
                                select 'BANG' from dual where (select department_id from employees where last_name = 'kokolala') in (10,20, null);
                                select 'BANG' from dual where (select department_id from employees where last_name = 'kokolala') is null ;
                                This all is perfect, consistent, logical and cute.

                                Solomon Yakobson : Think about the dirrerence between empty list and list containing a null element.
                                This is cool, correct. this is the actual solution to the matter of having a null in our right-hand operand "BY JUST IGNORING THE NULLS, and is the list have only nulls it becomes an empty list", but in our case we do NOT have an EMPTY list, we have a list with a NULL indeed.
                                -ORACLE just under the hood did us a favor of giving us an EMPTY list by ignoring the nulls using "LNNVL", which is doing no good..let him experience it, let him taste it, let him avoid it, him him him design it. and do not give me an inconsistent mechanism. it is good to sell that ORACLE is treating results as lists but that shouldn't intervene with our system-level language, am boooring guy, if its designed to work like this, no am sorry it should be stopped now.


                                My point is
                                -This theory is great regarding the matter when we are talking about dealing with our result as a LIST of elements, we would take in consideration our NULL values, which what SHOULD be implemented by the coder.

                                -BUT the issue of oracle implements "LNNVL" while parsing our codes INCASE and ONLY in the case of subquery as our right-hand operand, I am not buying it and i see it inconsistent.
                                They should implements it in all cases, or not at all "not even in one case as an exception", this is logic and it cant be divided or argued.
                                Am still hoping someone have an explanation to the matter, I wouldn't like it at all to know that am 100% correct in my analysis of this case, because if I am I would like to see them changing it.

                                Edited by: Ghazal-OCA on Jan 8, 2013 7:04 AM

                                Edited by: Ghazal-OCA on Jan 8, 2013 7:10 AM

                                Edited by: Ghazal-OCA on Jan 8, 2013 7:19 AM

                                Edited by: Ghazal-OCA on Jan 8, 2013 7:20 AM

                                Edited by: Ghazal-OCA on Jan 8, 2013 7:23 AM