You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Peter Haighton (JIRA)" <de...@db.apache.org> on 2005/05/12 21:20:05 UTC
[jira] Created: (DERBY-277) Group By and TRIM do not cooperate with more than 1 'not like' statement
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
[jira] Commented: (DERBY-277) Group By and TRIM do not cooperate with more than 1 'not like' statement
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-277) Group By and TRIM do not cooperate with more than 1 'not like' statement
Posted by "Peter Haighton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-277?page=comments#action_12312794 ]
Peter Haighton commented on DERBY-277:
--------------------------------------
Hi,
Here is the Schema,
CREATE TABLE NA
(
NAID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) CONSTRAINT NA_PK PRIMARY KEY NOT NULL,
description VARCHAR (4096),
naType INT
);
CREATE TABLE NAE
(
NAEID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) CONSTRAINT NAE_PK PRIMARY KEY NOT NULL,
NAID BIGINT NOT NULL,
sid BIGINT NOT NULL,
fieldName VARCHAR(255),
fieldValue VARCHAR(4096),
creationTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX nae_sid_ndx ON nae(sid);
CREATE TABLE S
(
SID BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1) NOT NULL CONSTRAINT S_PK PRIMARY KEY,
emailaddress VARCHAR(4096) NOT NULL CONSTRAINT S_U UNIQUE
};
> 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
[jira] Updated: (DERBY-277) Group By and TRIM do not cooperate with
more than 1 'not like' statement
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-277?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-277:
-------------------------------------
Attachment: d277.sql
Repro attached, based on the SQL statements above.
On 10.1, the query has these (incorrect) results:
1 |2
--------------------------------------------------------------------------------------------------------------------------------------------
102_fieldname |1
103_fieldname |1
2 rows selected
On 10.2 and later, it has these (correct) results:
1 |2
--------------------------------------------------------------------------------------------------------------------------------------------
3_fieldname |1
4_fieldname |1
2 rows selected
So it seems like the bug has been fixed now.
> 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
> Attachments: d277.sql
>
>
> 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.
[jira] Commented: (DERBY-277) Group By and TRIM do not cooperate
with more than 1 'not like' statement
Posted by "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.
[jira] Resolved: (DERBY-277) Group By and TRIM do not cooperate
with more than 1 'not like' statement
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-277?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-277.
--------------------------------------
Resolution: Fixed
Fix Version/s: 10.2.1.6
This bug was fixed as a side-effect of the following commit:
------------------------------------------------------------------------
r437070 | djd | 2006-08-26 05:55:39 +0200 (Sat, 26 Aug 2006) | 2 lines
DERBY-883 Enhance GROUP BY clause to support expressions instead of just column references.
Patch contributed by Manish Khettry - manish_khettry@yahoo.com
------------------------------------------------------------------------
> 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
> Fix For: 10.2.1.6
>
> Attachments: d277.sql
>
>
> 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.