5 Replies Latest reply: May 10, 2012 12:32 AM by 933177 RSS

    confusion in "< all"

    vai
      this is my table data.

      EID ENAME SAL MANAGER_ID COMM
      ---------------------- ------------------------------ ---------------------- ---------------------- ----------------------
      1 abc 2000 0
      2 def 6000 0
      3 lmn 24000 0
      4 pqr 4000 0
      5 xyz 10000 0
      8 vai 0.1
      7 gates 4000 0
      6 eff 20800 0

      8 rows selected

      why does the following query returns different outputs for the same condition?

      VT>select * from emps where sal < all (null,3000,5000);

      EID ENAME SAL MANAGER_ID COMM
      ---------------------- ------------------------------ ---------------------- ---------------------- ----------------------
      1 abc 2000 0

      1 rows selected



      VT>select * from emps where sal < all (3000,null,5000);
      EID ENAME SAL MANAGER_ID COMM
      ---------------------- ------------------------------ ---------------------- ---------------------- ----------------------
      1 abc 2000 0
      4 pqr 4000 0
      7 gates 4000 0

      3 rows selected



      VT>select * from emps where sal < all (3000,5000,null);

      EID ENAME SAL MANAGER_ID COMM
      ---------------------- ------------------------------ ---------------------- ---------------------- ----------------------

      0 rows selected

      Edited by: vaibhav on May 8, 2012 7:35 AM
        • 1. Re: confusion in "< all"
          Solomon Yakobson
          vaibhav wrote:
          why does the following query returns different outputs for the same condition?
          If it does return data (which I doubted), then it is a bug. Please post:

          a) exact version by issuing SELECT * FROM V$VERSION
          b) a snippet of SQL*Plus screen showing your query and returned row.

          Anyway ALL means condition is TRUE when applied to all items in a list. Therefor something < ALL (null,...) by definition can't return TRUE. It will always return UNKNOWN.

          SY.
          • 2. Re: confusion in "< all"
            878451
            Yes, looks like a issue for me.

            But from my points of view, the sentence comes after the null value.

            Example:

            select * from emps where sal < all (null,3000,5000);
            R: 2000 [minor than 3000, and minor than 500]

            select * from emps where sal < all (3000,null,5000);
            R: 4000, 4000, 2000[minor than 5000 only]

            VT>select * from emps where sal < all (3000,5000,null);
            R: [minor than nothing]

            See ? looks like the 'ALL' check values after the null only.

            I don´t know if this helps, but i see this standart in the issue.

            Edited by: BrunoSales on 08/05/2012 13:44
            • 3. Re: confusion in "< all"
              Frank Kulash
              Hi,

              There seems to be a bug in Oracle 10.2.0.3.0 and 11.1.0.6.0
              SELECT  sal
              FROM    scott.emp
              WHERE   sal   < ALL (1000, null, 1500)
              ;
              returns rows in those versions, as if you had said ANY instead of ALL.
              SAL
              ----
               800
              1250
              1250
              1100
               950
              1300
              In Oracle 10.1.0.2.0 and 11.2.0.1.0, it produces no rows.
              • 4. Re: confusion in "< all"
                Solomon Yakobson
                Again, no version. So I did some testing and yes, on my:
                SQL> select * from v$version
                  2  /
                
                BANNER
                ----------------------------------------------------------------
                Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
                PL/SQL Release 10.2.0.4.0 - Production
                CORE    10.2.0.4.0      Production
                TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
                NLSRTL Version 10.2.0.4.0 - Production
                
                5 rows selected.
                
                SQL> select * from emp where sal < all (null,3000,5000);
                
                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
                      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
                      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                
                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                
                11 rows selected.
                
                SQL> 
                This has to be a bug - query shouldn't return any rows. And it looks like it is fixed:
                SQL> connect scott@orcl
                Enter password: *****
                Connected.
                SQL> select * from v$version
                  2  /
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE    11.2.0.1.0      Production
                TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production
                
                5 rows selected.
                
                SQL> select * from emp where sal < all (null,3000,5000);
                
                no rows selected
                
                SQL> 
                However, this is still questionable:
                SQL> select * from emp where not sal < all (null,3000,5000);
                
                     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
                      7839 KING       PRESIDENT            17-NOV-81       5000                    10
                      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                
                3 rows selected.
                
                SQL> 
                I'd say it still should return NULL. But optimizer does the following:
                SQL> explain plan for
                  2  select * from emp where not sal < all (null,3000,5000);
                
                Explained.
                
                SQL> @?\rdbms\admin\utlxpls
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------------------------------------------
                Plan hash value: 3854330697
                
                ----------------------------------------------------------------------------------------
                | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                ----------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT             |         |    14 |   532 |     3   (0)| 00:00:01 |
                |   1 |  NESTED LOOPS                |         |     1 |    30 |     1   (0)| 00:00:01 |
                |*  2 |   TABLE ACCESS BY INDEX ROWID| USER$   |     1 |    17 |     1   (0)| 00:00:01 |
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------------------------------------------
                |*  3 |    INDEX UNIQUE SCAN         | I_USER1 |     1 |       |     0   (0)| 00:00:01 |
                |*  4 |   FIXED TABLE FULL           | X$KZSRO |     1 |    13 |     0   (0)| 00:00:01 |
                |*  5 |  VIEW                        | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
                |   6 |   TABLE ACCESS FULL          | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
                ----------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------------------------------------------
                
                   2 - filter("U"."USER#"<>USERENV('SCHEMAID') AND "U"."USER#"<>1)
                   3 - access("U"."NAME"='NO_SAL_ACCESS')
                   4 - filter("KZSROROL"<>1 AND "KZSROROL"<>USERENV('SCHEMAID') AND
                              "U"."USER#"="KZSROROL")
                   5 - filter("SAL">=3000)
                
                22 rows selected.
                
                SQL> 
                Look at:
                5 - filter("SAL">=3000)
                Optimizer calculated LEAST(3000,5000) = 3000 and converted:
                not sal < all (null,3000,5000)
                to
                sal >= null OR sal >=3000
                So question here is on condition presedence, but ... there is no precedence defined for ALL/ANY in SQL Reference manual.

                SY.
                • 5. Re: confusion in "< all"
                  933177
                  hey,
                  now according to me ths solution is

                  when a bracket open in case of all() it EVALUATES from right to left and as soon the null apperas in the statement then it stops evaluating further.


                  @nkit> select sal from emp where sal <all(3000,null,5000);

                  SAL
                  ----------
                  800
                  1600
                  1250
                  2975
                  1250
                  2850
                  2450
                  3000
                  1500
                  1100
                  950
                  3000
                  1300

                  13 rows selected.(IN THIS CASE ALL THE VALUES ARE SMAALER THAN 5000 BECAUSE AFTER 5000 IT FOUND NULL)

                  @nkit> select sal from emp where sal <all(null,3000,5000);

                  SAL
                  ----------
                  800
                  1600
                  1250
                  2975
                  1250
                  2850
                  2450
                  1500
                  1100
                  950
                  1300

                  11 rows selected.(IN THIS CASE NULL IS FOUND AT THE END HENCE EXECUTING BOTH 5000 AND 3000)


                  @nkit> select empno from emp where sal <all(3000,5000,null);

                  no rows selected
                  (IN THIS CASE NULL IS EXECUTED AT THE BEGINING HENCE NO RESULT)