Re: Q: WHERE 1=1 AND 1=0 AND 1=1, how many rows returned?
Brent Jackson writes:
*
* kerry@kcbbs.gen.nz (Kerry Sainsbury) wrote:
*
* >Encountered a *great* bug today:
*
* >SELECT tabname FROM systables
* > WHERE 1=1 AND 1=0 AND 1=1
*
* >Q: What will the above query do?
* >A: return all the rows in systables!
*
* >This is SE 5.02.something on our Unisys SYSV box.
*
* I get the same for SCO Unix with OnLine 5.00.UD5 and 5.02.UC6
*
* I prefer the where clause of :
*
* WHERE 1=1 AND NOT 1=1 AND 1=1
*
* It's a goodie !
*
* Cheers,
* Brent Jackson.
*
* brent@hypercom.co.nz Phone : 64-9-3603593 Fax : 64-9-3602840
*
If you set explain on you'll see why:
QUERY:
------
select count(*) from systables
where 1=1 and not 1=1 and 1=1
(Returned: 0 rows)
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) rob.ud_master: SEQUENTIAL SCAN
Filters: (1 = 1 AND (1 != 1 AND 1 = 1 ) )
QUERY:
------
select count(*) from ud_master
where 1=1 or 1=0 and 1=1
(Returned: 109 rows)
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) rob.ud_master: SEQUENTIAL SCAN
Filters: (1 = 1 OR (1 = 0 AND 1 = 1 ) )
The OR groups everything after it.
Robert Minter Data Systems Support \\\\\\_///
Senior Software Engineer A Client Technologies Company ( _ _ )
E-Mail: rob@dssmktg.com Tel: 714.771.0454 (| ^ |)
#include <disclaimer.h> Fax: 714.771.3028 \\`-'/
De Colores - Emmaus OC-13 SURF'S UP \\_/