db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-277) Group By and TRIM do not cooperate with more than 1 'not like' statement
Date Fri, 20 May 2005 18:36:53 GMT
     [ http://issues.apache.org/jira/browse/DERBY-277?page=comments#action_65875 ]
     
Satheesh Bandaram commented on DERBY-277:
-----------------------------------------

I tried to reproduce the problem by guessing what the tables here would look like with my
own data. Couldn't reproduce the problem. Would it be possible to add a complete test case,
with table schemas, data to be inserted before running the queries?

> Group By and TRIM do not cooperate with more than 1 'not like' statement
> ------------------------------------------------------------------------
>
>          Key: DERBY-277
>          URL: http://issues.apache.org/jira/browse/DERBY-277
>      Project: Derby
>         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.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message