Re: Difference between SYSDATE and TO_DATE

Comment in-line:
Tine Zorko <tine.zorko_at_email.si> wrote in news:bnpcse$r21$1_at_planja.arnes.si:

> You should take a second look at your code.
> If BETWEEN evaluation operator has left AND right arguments then:
>
> 1st SELECT:
> left argument < right argument (LESS)
> (28-oct-03) < (29-oct-03)
>
> 2nd SELECT
> left argument > right argument (GREATER)
> sysdate > sysdate - 1
>
> There is AND logical operator between both arguments.
> As with most recent programming languages Oracle also
> evaluates truth of 1st argument and if not true it is
> logical, 2nd argument makes no sense:
>
> Arg1 AND Arg2 Outcome
> 1 1 TRUE, 2nd argument will be considered in evaluation
> 1 0 FALSE, 2nd argument will be considered in evaluation
> 0 0 FALSE, only 1st argument is evaluated
> 0 1 FALSE, only 1st argument is evaluated <- YOUR CASE!!
>
> BETWEEN can be interpreted like:
>
> operand > arg1 AND operand < arg2
>

This is total rubbish. The order of evaluation of the predicates is indeterminate. SQL is not a procedural language. With it you specify what your resultset should be, but the relational database determines how to get it, and that HOW can change from execution to execution. If you were right, the following could not happen:

Create table p_order (
SSN VARCHAR2(10),
BIRTH_date DATE,
ZIP VARCHAR2(10));

insert into p_order values('8328486572', '31-OCT-03', '27722');
insert into p_order values('8397333761', '31-OCT-03', 'A1180');
insert into p_order values('8358002539', '31-OCT-03', '81225');
insert into p_order values('8353962080', '31-OCT-03', '43933');

SQL> select * from p_order;

SSN BIRTH_DAT ZIP

---------- --------- ----------
8328486572 31-OCT-03 27722
8397333761 31-OCT-03 A1180
8358002539 31-OCT-03 81225
8353962080 31-OCT-03 43933

4 rows selected.

SQL> select * from p_order
  2 where ssn like '835%'
  3 and birth_date = '31-OCT-03';

SSN BIRTH_DAT ZIP

---------- --------- ----------
8358002539 31-OCT-03 81225
8353962080 31-OCT-03 43933

SQL> select * from p_order
  2 where ssn like '835%'
  3 and birth_date = '31-OCT-03'
  4 and zip = 43933;
 and zip = 43933

     *
ERROR at line 4:
ORA-01722: invalid number

If Oracle did evaluate the predicates in the order specified, by the time it gets to evaluate zip = 43933 (which requires a to_number conversion) the zips ARE numbers and the conversion would succeed. However, it obviously evaluates "zip = " before "ssn like".

Unless you specifically request it with the ORDERED_PREDICATES hint:

SQL> select /*+ ORDERED_PREDICATES */ * from p_order   2 where ssn like '835%'
  3 and birth_date = '31-OCT-03'
  4 and zip = 43933;

SSN BIRTH_DAT ZIP
---------- --------- ----------
8353962080 31-OCT-03 43933 1 row selected.

-- 
What lies behind us and what lies before us are small matters when 
compared to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP