[jira] Commented: (DERBY-277) Group By and TRIM do not cooperate with more than 1 'not like' statement

["Kathey Marsden (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-277?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12566265#action_12566265 ] 

Kathey Marsden commented on DERBY-277:
--------------------------------------

Peter, I realize it has been a long time but I think we have seen so little activity on this issue because we don't have a complete test case.  Could you add the data needed to reproduce this problem. Otherwise I think we should close CannotReproduce.






> Group By and TRIM do not cooperate with more than 1 'not like' statement
> ------------------------------------------------------------------------
>
>                 Key: DERBY-277
>                 URL: https://issues.apache.org/jira/browse/DERBY-277
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>         Environment: java version "1.3.1_13"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_13-b03)
> Java HotSpot(TM) Client VM (build 1.3.1_13-b03, mixed mode)
> Running on Windows XP
>            Reporter: Peter Haighton
>            Priority: Minor
>
> Performing a select that appends BIGINT and VARCHARs together returns some strange results when mixed in with more than 1 not 'not like' statement.
> An example is as follows:
> SELECT RTRIM(CHAR(na.id))||'_'||fieldname, count(*) FROM na, nae, s WHERE na.type=3 and nae.naid=na.id AND fieldvalue='checked' and s.id=nae.sid and s.emailaddress not like '%@abc.com' group by na.id,fieldname
> na.id is a primary key BIG INT
> fieldname is a varchar
> returns something like
> 32_challenge_100_0 38 
> 32_challenge_100_1 42 
> 32_challenge_100_2 38 
> 32_challenge_100_3 42 
> 32_challenge_100_6 1 
> 32_challenge_101_0 25 
> 32_challenge_101_1 35 
> 32_challenge_101_2 30 
> 32_challenge_101_3 18
> but adding an extra "and userid not like" statement in such as
> SELECT RTRIM(CHAR(na.id))||'_'||fieldname, count(*) FROM na, nae, s WHERE na.type=3 and nae.naid=na.id AND fieldvalue='checked' and s.id=nae.sid and s.emailaddress not like '%@abc.com' and s.emailaddress not like '%def.com' group by na.id,fieldname
> I get:
> 32645_challenge_100_0 38 
> 32645_challenge_100_1 42 
> 33399_challenge_100_2 38 
> 33399_challenge_100_3 42 
> 18199_challenge_100_6 1 
> 32645_challenge_101_0 25 
> 32645_challenge_101_1 35 
> 32645_challenge_101_2 30 
> 32645_challenge_101_3 18
> ...
> Notice the number 32 has been changed to a somewhat appearing random number. The problem only occurs when I add the trim at the beginning

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.