[ 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 '[EMAIL PROTECTED]' 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 '[EMAIL PROTECTED]' 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
