Question about and/or conditions in where clause

Database

Question about and/or conditions in where clause

Hi all,

 

I have a 1-row table with the following values:

 

create volatile table test 

(yrdx char(4),

dodval bigint,

mon256 char(1),

mon257 char (1))

on commit preserve rows;

 

insert into test values ('2011',96108,'3','2');

 

I want to apply the following conditions to the where clause:

 

select *

from test 

where

(

(yrdx='2010' AND (mon256='3' OR dodval LT 256) AND (mon257='3' OR dodval LT 257))

OR 

(yrdx='2011' AND (mon256='3' OR dodval LT 256) AND (mon257='3' OR dodval LT 257))

);

 

I expected the query to return 0 rows. By themselves, each statement on either side of the OR clause evaluates as false.  When I string them together with the OR clause, however, it returns the single row of the table, and I can't determine why.  I know the query can be rewritten, but I'm curious how this returns a row.  Looking at the explain shows the following as what the optimizer generates:

with a condition of ("(test.dodval <=255) OR (test.mon256 = '3')")

 

Just curious/confused how the optimizer generated that, and if it's an issue of parentheses in the wrong place?

 

Any input appreciated.  Using TD15.10. Thanks!

4 REPLIES
Supporter

Re: Question about and/or conditions in where clause

Hi,

 

I run 15.00.01.01 and don't get any rows from the sql

 

select *
from vt_test
where
(
(yrdx='2010' AND (mon256='3' OR dodval LT 256) AND (mon257='3' OR \
dodval LT 257))
OR
(yrdx='2011' AND (mon256='3' OR dodval LT 256) AND (mon257='3' OR \
dodval LT 257))
)

 

I renamed the table to to naming conficts...

 

Supporter

Re: Question about and/or conditions in where clause

And here is my explain

 

Explanation
  1) First, we do a single-AMP RETRIEVE step from all partitions of
     WWW.vt_test by way of the primary index "WWW.vt_test.yrdx = '2010'"
     with a residual condition of ("((WWW.vt_test.mon257 = '3') OR
     (WWW.vt_test.dodval <= 256 )) AND ((WWW.vt_test.mon256 = '3') OR
     (WWW.vt_test.dodval <= 255 ))") into Spool 1 (group_amps), which
     is built locally on that AMP.  The size of Spool 1 is estimated
     with low confidence to be 1 row (41 bytes).  The estimated time
     for this step is 0.01 seconds.
  2) Next, we do a single-AMP RETRIEVE step from all partitions of
     WWW.vt_test by way of the primary index "WWW.vt_test.yrdx = '2011'"
     with a residual condition of ("((WWW.vt_test.mon257 = '3') OR
     (WWW.vt_test.dodval <= 256 )) AND ((WWW.vt_test.mon256 = '3') OR
     (WWW.vt_test.dodval <= 255 ))") into Spool 1 (group_amps), which
     is built locally on that AMP.  The size of Spool 1 is estimated
     with low confidence to be 2 rows (82 bytes).  The estimated time
     for this step is 0.01 seconds.
  3) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.01 seconds.

Teradata Employee

Re: Question about and/or conditions in where clause

Unfortunately you have located a fault in the TD15.10 optimizer.

 

DR185067 has been opened to track a fix for this problem.

 

I suggest that your site open and incident with Teradata support and reference this DR. It is up to your site to request a fix for your release.

Re: Question about and/or conditions in where clause

Thanks Todd for confirmation.