Re: Re: Re: why data translate error when selecting from view? -- MIDRANGE-L

I think I may understand what is going on. I was able to recreate the
behavior you're seeing by putting an index over CPCSID.

The first thing to know is that SQL makes no guarantees about the order in
which predicates are evaluated. Even though intuition might suggest that
the view predicate should be evaluated first, this is not required.
Therefore, the optimizer may combine and re-order the predicates for
performance.

With the index, the optimizer chooses to evaluate the problematic
[CASE...END] = 1346100 predicate before it evaluates the
LENGTH(TRIM(CPCSID))=10 predicate. It does this because it splits the data
access into an index scan and a table probe. For performance, it pushes as
much selection into the index scan as it can. But because char fields are
stored in the index without trailing blanks, the optimizer has to wait to
evaluate the LENGTH of a char field until has the "real" data from the
table.

On the other hand, the TRANSLATE predicate can be evaluated in the index
scan node. There's no need to defer its evaluation until later. This
allows it to be evaluated (to FALSE) before the problematic [CASE...END] =
1346100 predicate. This gives you the behavior that you are seeing (and
expecting.)

Hope that helps,

Tim Clark
DB2 for IBM i

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 05/15/2019
02:50:41 PM:

From: "Timothy P Clark" <timclark@xxxxxxxxxx>
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 05/15/2019 02:50 PM
Subject: [EXTERNAL] Re: Re: why data translate error when selectingfrom

view?

Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

Hi Steve,

What release are you running? Does the error occur if you do a
select * from contrv2
or is it only when additional predicates are added?

I'm trying to reproduce this on 7.3 and am not having any luck. There
certainly appears to be a problem, but I can't put my finger on it. You
might have to open a PMR and let support grab traces.

Tim Clark
DB2 for IBM i

From: Steve Richter <stephenrichter@xxxxxxxxx>
To: Midrange Systems Technical Discussion

<midrange-l@xxxxxxxxxxxxxxxxxx>,

Date: 05/14/2019 04:00 PM
Subject: [EXTERNAL] Re: why data translate error when selecting from

view?

Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

was able to isolate the problem down to the WHERE clause. But it

still

makes no sense to me.

here is the view.
when I create the view with this WHERE clause I can select from it

without

an error:
WHERE TRANSLATE(RTRIM(A.CPCSID),' ',' 0123456789') = ' '

I change that WHERE clause to:
where length(trim(a.cpcsid)) = 10
I cannot select from the view. Get a translate error when translating
CPCSID from char to decimal.

create OR REPLACE view aplusb1fcc/contrv2 as
select A.CPCSID,
case when A.CPCSID <> ' '
then decimal(a.cpcsid,10,0)
else decimal(0,10,0) end csno,
case when a.cpexdt = 0 then date('2050-12-31')
else
DATE(digits(a.cpexcc) || substr(digits(a.cpexdt),1,2) ||
'-' || substr(digits(a.cpexdt),3,2) ||
'-' || substr(digits(a.cpexdt),5,2)) end expireDate
from CONTR a
-- WHERE TRANSLATE(RTRIM(A.CPCSID),' ',' 0123456789') = ' ' ;
where length(trim(a.cpcsid)) = 10 ;

here is the SQL select statement that gets the translate error:

select a.csno, a.expireDate
from contrv2 a
where a.csno = 1346100
and a.expiredate >= current date
Selection error involving field *N.

Message . . . . : Select or omit error on field
Cast(Translate(CONTR_1.CPCSID, *UNNAMED Table) AS member CONTRV2.
Cause . . . . . : A select or omit error occurred in record 3217,

here is an SQL that shows the value of CPCSID in record 3217 in the

table.

select a.cpcsid, length(trim(a.cpcsid)) lgth
from contr a
where rrn(a) = 3217

Customer LGTH
ID
110A 4

notice the value is 110A. and the length is 4. But the view is

selecting

only rows where the value in CPCSID has a length of 10.

the point is, there is no difference in the WHERE clause when I use
TRANSLATE or LENGTH to select rows where CPCSID contains decimal data.

Why

does the LENGTH(TRIM(a.CPCSID)) method fail?

On Tue, May 14, 2019 at 3:11 PM Timothy P Clark <timclark@xxxxxxxxxx>

wrote:

Hi Steve,

The data translate error is showing up in the view because of the
selection criteria. The query engine has to evaluate the "bad" data

order to process the selection criteria. But when you're selecting
everything directly from the table (without a WHERE clause) the

query

engine doesn't need to do anything with the bad data except pass it
through to the output buffer. For performance, the query engine

doesn't do

any more processing of the data than is absolutely necessary

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?

u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=P6MsFuZLI-3sffEVTbofnEj9YUoLEamZe8zbJcAZMPM&s=xRpZzBA2xpxvwLoNMHyeVEvUHbrft17dnjoIuf5Bma0&e=

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?

u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=P6MsFuZLI-3sffEVTbofnEj9YUoLEamZe8zbJcAZMPM&s=jmG3VZgcVMbKXoJNY_h-

Dxrj18wu1uWikmF0wCuSDos&e=.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link:

https://urldefense.proofpoint.com/v2/url?
u=https-3A__amazon.midrange.com&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=P6MsFuZLI-3sffEVTbofnEj9YUoLEamZe8zbJcAZMPM&s=7QcYtj6SSrw4GrN_yNzxJtOqVczTuOhFuXQKEwKBcW4&e=

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://urldefense.proofpoint.com/v2/url?

u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=qpNLR8kfHompgsSm9xuBjdQirjOfrTsa9_cn11oQ87o&s=i9-1vLn3XoHwdGNXSm1J4PLgCKfM618-

B6C-8Hmyimo&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://urldefense.proofpoint.com/v2/url?
u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=qpNLR8kfHompgsSm9xuBjdQirjOfrTsa9_cn11oQ87o&s=OU2in2ZHx6tOaWK0BoT6pwEm_haTa7gIGL5IGZMlrEA&e=

.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link:

https://urldefense.proofpoint.com/v2/url?
u=https-3A__amazon.midrange.com&d=DwICAg&c=jf_iaSHvJObTbx-

siA1ZOg&r=oCmVbiwufH6yM8U29GriOcB5LKDoEG8y1HZD-01RVtc&m=qpNLR8kfHompgsSm9xuBjdQirjOfrTsa9_cn11oQ87o&s=M2JEFmJkOOmQf8Cs-

oEQsknncWYdLBM_wOqXoqcCk3A&e=