the LIKE operator behavior different between ASE1253 and ASE1503

[mark87544]

All,

when i use the like operator in the WHERE statement rows that do NOT
contain the pattern i requested are being included in the result set
causing the subtring function to fail The TSQL (below) works in ASE125
but has errors in ASE150.

Below you will find:
1) ASE versions i have installed (host is AIX)
2) schema, with data insert
3) TSQL. two SELECTs. 2nd SELECT does not return any rows 9as i would
expect). The DELETE fails on row i would not have expected to be in
the result set
4) result of executing the TSQL

if you need more details please let me know.

thanks,
Mark

ASE125 version:
Adaptive Server Enterprise/12.5.3/EBF 12332 ESD#1/P/RS6000/AIX 4.3.3/
ase1253/1900/32-bit/FBO/Tue Jan 25 02:28:04 2005

ASE150 version:
Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX 5.3/
ase1503/2681/64-bit/FBO/Mon Mar 9 01:35:28 2009

>>> test table

TABLE name astring:
CREATE TABLE astring (
atinyint tinyint not null
,varchar64 varchar(64) not null
)
GO
GRANT ALL ON astring TO PUBLIC
GO
INSERT astring SELECT 1,"string does not contain the pipe character"
GO

>>> TSQL
:
DECLARE @l_rowcount int
,@l_reject int

PRINT "NO WHERE CONDITIONS"
SELECT CHARINDEX("|",varchar64) "indexOFpipe"
,"["+varchar64+"]"
FROM astring

PRINT "WHERE varchar64 LIKE %%|%%"
SELECT CHARINDEX("|",varchar64) "indexOFpipe"
,"["+varchar64+"]"
FROM astring
WHERE varchar64 LIKE "%|%"

BEGIN TRAN

DELETE astring
FROM astring ASTR
WHERE ASTR.varchar64 LIKE "%|%"
AND LTRIM(SUBSTRING(ASTR.varchar64,1,
((CHARINDEX("|",ASTR.varchar64)-1)))) =
LTRIM(SUBSTRING(ASTR.varchar64,
((CHARINDEX("|",ASTR.varchar64)+1))
,((CHARINDEX("|",ASTR.varchar64)-1))))
SELECT @l_reject = @@error,
@l_rowcount = @@rowcount
IF @l_reject != 0
BEGIN
PRINT "DELETE astring FAILED[%1!]",@l_reject
PRINT "ROLLBACK TRAN"
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT "DELETE astring SUCCEEDED rowcount[%1!]",@l_rowcount
END

IF @@trancount>0
BEGIN
PRINT "ROLLBACK TRAN"
ROLLBACK TRAN
END

>>>> RESULTS on ASE15 (there are NO errors when executed on ASE125, the DELETE completes with zero rows affected)

NO WHERE CONDITIONS

indexOFpipe
-----------
------------------------------------------------------------------
0 [string does not contain the pipe
character]
WHERE varchar64 LIKE %|%

(1 row affected)

indexOFpipe
-----------
------------------------------------------------------------------

(0 rows affected)

Msg 536, Level 16, State 3
Server 'test15', Line 17
Invalid length parameter with value of -1 passed to the substring
function.
Command has been aborted.

(0 rows affected)

(1 row affected)
DELETE astring FAILED[536]
ROLLBACK TRAN

[mpeppler@peppler.org [Team Sybase]]

Actually, LIKE doesn't behave differently. It's the order in which the
operators are evaluated in your query that is different, and given the
changes in the way the optimizer works in ASE 15 I'm not at all
surprised to see this behavior.

You will have to add some logic to test for valid data prior to
executing the substring() call in the case where no pipe is found.

Michael

[mark87544]

On Feb 12, 11:23 am, "mpepp...@peppler.org [Team Sybase]"

Michael,

thanks for the response.

I guess i don't understand the logic behind the WHERE statement

So in ASE125 rows that did not meet the criteria from the first part
of the WHERE clause 'only appeared' to NOT be included in subsequent
parts of the WHERE clause; otherwise, the substring function would
have failed.

But in ASE150 its a 'hit or miss' in that i have no control of the
precedence of what part of the WHERE clauses is executed first.

Basicly, I assumed that since the first condition in my WHERE clause
was to only include rows WHERE a pipe symbol is present, the
subsequent AND clause with the substring would not be executed if the
row DID NOT contain the pipe character.

So the bottom line is my assumption on the behavior of the WHERE
clause is flawed?

Mark

[mpeppler@peppler.org [Team Sybase]]

On Feb 16, 10:57 pm, mark87544 <north35west...@gmail.com> wrote:

>
> I guess i don't understand the logic behind the WHERE statement
>
> So in  ASE125 rows that did not meet the criteria from the first part
> of the WHERE clause 'only appeared' to NOT be included in subsequent
> parts of the WHERE clause; otherwise, the substring function would
> have failed.
>
> But in ASE150 its a 'hit or miss' in that i have no control of the
> precedence of what part of the WHERE clauses is executed first.
>
> Basicly, I assumed that since the first condition in my WHERE clause
> was to only include rows WHERE a pipe symbol is present, the
> subsequent AND clause with the substring would not be executed if the
> row DID NOT contain the pipe character.
>
> So the bottom line is my assumption on the behavior of the WHERE
> clause is flawed?

Well - you are relying on the order in which operators are evaluated.
In ASE 12.5 (and earlier) this was usually fairly predictable, but in
15 the behavior has changed, and the conditions in the WHERE clause
can be evaluated in a different order. I don't think the order of
evaluation is documented anywhere, so you can't really fault Sybase,
although I agree that it would seem logical to first check the "hard"
condition before executing the other one.

Michael