This discussion is archived
5 Replies Latest reply: May 9, 2012 10:32 PM by 933177 RSS

confusion in "< all"

vp_ora Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points