Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <446002462.1115925605920.JavaMail.jira@ajax.apache.org>
Date: Thu, 12 May 2005 21:20:05 +0200 (CEST)
From: "Peter Haighton (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Created: (DERBY-277) Group By and TRIM do not cooperate with
 more than 1 'not like' statement
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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