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 "Manjula Kutty (JIRA)" <de...@db.apache.org> on 2006/01/27 23:46:32 UTC
[jira] Created: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
----------------------------------------------------------------------------------------------------------------------------
Key: DERBY-887
URL: http://issues.apache.org/jira/browse/DERBY-887
Project: Derby
Type: Bug
Components: SQL
Versions: 10.2.0.0
Environment: Java : java version "1.5.0_02"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
OS: Windows XP Professional
Reporter: Manjula Kutty
Fix For: 10.2.0.0
I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
ij> select id,date from inbox where id = 0<3;
ID |DATE
-----------------------------------------------
25 |2006-01-26 14:35:46.584
34 |2006-01-26 14:36:16.588
21 |2006-01-26 14:34:46.455
22 |2006-01-26 14:34:47.176
27 |2006-01-26 14:35:47.054
24 |2006-01-26 14:35:16.58
28 |2006-01-26 14:35:47.305
35 |2006-01-26 14:36:18.771
31 |2006-01-26 14:35:48.496
32 |2006-01-26 14:35:48.887
33 |2006-01-26 14:35:49.308
11 rows selected
ij> select id,date from inbox where id = true;
ID |DATE
-----------------------------------------------
21 |2006-01-26 14:34:46.455
1 row selected
Both queries should return same number of rows
Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
ij> delete from inbox where id=21;
1 row inserted/updated/deleted
Then again run
ij> select id ,date from inbox where id=true;
ID |DATE
-----------------------------------------------
22 |2006-01-26 14:35:16.58
Which indicates that the select returns only the first row and then is getting closed
--
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
Re: [jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by Daniel John Debrunner <dj...@apache.org>.
Andrew McIntyre wrote:
> On 7/21/06, Rick Hillegas (JIRA) <de...@db.apache.org> wrote:
>
>> Detaching this issue from 10.3. Fixing it seems to require changing
>> the non-ANSI behavior of implicit and explicit casts. Changing this
>> behavior probably will affect existing applications and it seems to be
>> the sort of fix which we need to defer to a major release. That is, this
>> is appropriate for 11.0 but not 10.3.
>
>
> Would it be useful to have an 11.0 in JIRA to track these sorts of changes?
Not sure, in this case I'm not convinced an 11.0 release would be
required to support BOOLEAN, even with the fixed casts. I don't think
any real analysis was done of the potential impact to real applications.
It maybe also that the changes in the trunk drive the version number,
not the other way around. If someone contributes some feature that the
community believes requires the version change should happen, then the
trunk would be bumped to 11.0. Adding 11.0 now is just going to raise
questions of when is the 11.0 release compared to the 10.3 release, and
how do I develop for the 11.0 release if the next release is 10.3?
Dan.
Re: [jira] Updated: (DERBY-887) Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
Posted by Andrew McIntyre <mc...@gmail.com>.
On 7/21/06, Rick Hillegas (JIRA) <de...@db.apache.org> wrote:
> Detaching this issue from 10.3. Fixing it seems to require changing
> the non-ANSI behavior of implicit and explicit casts. Changing this
> behavior probably will affect existing applications and it seems to be
> the sort of fix which we need to defer to a major release. That is, this
> is appropriate for 11.0 but not 10.3.
Would it be useful to have an 11.0 in JIRA to track these sorts of changes?
andrew
Re: [jira] Commented: (DERBY-887) Select statement returns wrong
number of rows if you compare an integer column with a boolean expression in
the where clause
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Rajesh Kartha (JIRA) wrote (2006-01-29 02:36:33):
> [ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364353 ]
>
> Rajesh Kartha commented on DERBY-887:
> -------------------------------------
>
> Would be interesting to know what the SQL standard proposes for
> comparing int and boolean.
The SQL spec (99) chapter 6.22 states that a BOOLEAN may be converted
(with a CAST specification) to CHAR, VARCHAR, BOOLEAN and CLOB and
that CHAR, VARCHAR, CLOB and BOOLEAN may be converted to BOOLEAN.
My conclusion from that is that BOOLEAN and int types are not
compatible for comparision.
> Also, does boolean in Derby mean false (0) or true (>0) ?
No. Booleans have the possible values TRUE, FALSE and UNKNOWN and
nothing is said about the representation of these (of course, Derby
will have some internal representation but that should be of no
interest since the types are not compatible).
> In that case, if comparisions are allowed, one would expect all the rows with id>0 to be returned for the
>
> select * from t where id=true;
>
> statement under both the scenarios (with and without primary key).
>
> The results in case of the table without the primary key looks consistent and so does the id=0<3 statement.
>
>
>
>
>
>
>
> > Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> > ----------------------------------------------------------------------------------------------------------------------------
> >
> > Key: DERBY-887
> > URL: http://issues.apache.org/jira/browse/DERBY-887
> > Project: Derby
> > Type: Bug
> > Components: SQL
> > Versions: 10.2.0.0
> > Environment: Java : java version "1.5.0_02"
> > Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> > Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> > Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> > OS: Windows XP Professional
> > Reporter: Manjula Kutty
> > Fix For: 10.2.0.0
> > Attachments: DERBY-887.sql
> >
> > I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> > ij> select id,date from inbox where id = 0<3;
> > ID |DATE
> > -----------------------------------------------
> > 25 |2006-01-26 14:35:46.584
> > 34 |2006-01-26 14:36:16.588
> > 21 |2006-01-26 14:34:46.455
> > 22 |2006-01-26 14:34:47.176
> > 27 |2006-01-26 14:35:47.054
> > 24 |2006-01-26 14:35:16.58
> > 28 |2006-01-26 14:35:47.305
> > 35 |2006-01-26 14:36:18.771
> > 31 |2006-01-26 14:35:48.496
> > 32 |2006-01-26 14:35:48.887
> > 33 |2006-01-26 14:35:49.308
> > 11 rows selected
> > ij> select id,date from inbox where id = true;
> > ID |DATE
> > -----------------------------------------------
> > 21 |2006-01-26 14:34:46.455
> > 1 row selected
> > Both queries should return same number of rows
> > Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> > ij> delete from inbox where id=21;
> > 1 row inserted/updated/deleted
> > Then again run
> > ij> select id ,date from inbox where id=true;
> > ID |DATE
> > -----------------------------------------------
> > 22 |2006-01-26 14:35:16.58
> > Which indicates that the select returns only the first row and then is getting closed
>
> --
> 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
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364616 ]
Daniel John Debrunner commented on DERBY-887:
---------------------------------------------
Fixing this will also disable previous queries that compiled successfully and possibly returned "valid" results. I couldn't tell from the original description if this query was returning all rows in the table.
select id,date from inbox where id = 0<3
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Urgency: Normal
Triaged for 10.5.3: assigned normal urgency,
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364649 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
Although DERBY-499 introduced some additional casting cases, the root causes of this bug are in an older version of Derby. Older releases allow illegal comparisons with BOOLEANs. In addition, the problem query can be expressed in a way which gets past the 10.1.1.0 parser and produces incorrect results. The following incorrect results may be observed in 10.1.1.0:
ij> create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2), primary key(id))
;
0 rows inserted/updated/deleted
ij> insert into t(col1) values('ca');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ba');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ea');
1 row inserted/updated/deleted
ij> insert into t(col1) values('dd');
1 row inserted/updated/deleted
ij> select * from t ;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=0<3;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where (id=0)<3;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=(0<3);
ID |COL1
-------------------------
2 |ba
1 row selected
I think there are at least two bugs in here:
o A pre-existing 10.1.1.0 bug.
o Some illegal casts introduced by DERBY-499.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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] Assigned: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas reassigned DERBY-887:
-----------------------------------
Assign To: Rick Hillegas
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Manjula Kutty (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Manjula Kutty updated DERBY-887:
--------------------------------
Attachment: DERBY-887.sql
Even I feel the same. Still, if it is valid then it should show consistent behaviour.
I just happend to run this query and was expecting an error message , but ij returned me some rows. In my schema I had primary key on the column 'id'. If I remove the primary key then every thing works fine
I'm attaching the script to reproduce the problem.
The derby jars I have used are :
[C:\pantry\derby.jar] 10.2.0.0 alpha - (372715)
[C:\pantry\derbytools.jar] 10.2.0.0 alpha - (372715)
......................................................................................................
Here is the results with primary key :
ij> create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2), primary key(id));
0 rows inserted/updated/deleted
ij> insert into t(col1) values('ca');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ba');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ea');
1 row inserted/updated/deleted
ij> insert into t(col1) values('dd');
1 row inserted/updated/deleted
ij> select * from t ;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=0<3;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=true;
ID |COL1
-------------------------
2 |ba
--------------------------------------------------------
With out primary key
ij>create table t (id bigint generated always as identity (start with 1,increment by 1), col1 char(2));
0 rows inserted/updated/deleted
ij> insert into t(col1) values('ca');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ba');
1 row inserted/updated/deleted
ij> insert into t(col1) values('ea');
1 row inserted/updated/deleted
ij> insert into t(col1) values('dd');
1 row inserted/updated/deleted
ij> select * from t ;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=0<3;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
ij> select * from t where id=true;
ID |COL1
-------------------------
1 |ca
2 |ba
3 |ea
4 |dd
4 rows selected
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12765122#action_12765122 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
There are a 108 errors in DatabaseMetaDataTest, so this patch needs a little work.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Attachment: derby-887-01-aj-noImplicitCasts.diff
Attaching derby-887-01-aj-noImplicitCasts.diff. This differs from the previous version of the patch as follows:
1) The builtin INTEGER function cannot be used to correct the ODBC metadata queries because the parser turns invocations of this builtin into CASTs to INT. That, of course, fails as a result of the core changes made by this bug-fix.
2) Instead of using the INTEGER function to correct the ODBC metadata queries, I now correct them by adding CASE clauses which map BOOLEAN values to INTEGER values.
I am running the regression tests now.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-887.
----------------------------------
Assignee: Rick Hillegas (was: Kathey Marsden)
Fix Version/s: 10.5.3.1
Resolution: Fixed
resolving for 10.5 and reassign Rick.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Fix For: 10.5.3.1, 10.6.1.0
>
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364353 ]
Rajesh Kartha commented on DERBY-887:
-------------------------------------
Would be interesting to know what the SQL standard proposes for comparing int and boolean.
Also, does boolean in Derby mean false (0) or true (>0) ?
In that case, if comparisions are allowed, one would expect all the rows with id>0 to be returned for the
select * from t where id=true;
statement under both the scenarios (with and without primary key).
The results in case of the table without the primary key looks consistent and so does the id=0<3 statement.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12767817#action_12767817 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
Committed derby-887-01-aj-noImplicitCasts.diff at subversion revision 827505.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-887:
-----------------------------------
Assignee: Rick Hillegas
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12762209#action_12762209 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
The legal SQL casts are laid out in a table in the SQL Standard, part 2, section 6.12 (<cast specification>), syntax rule 6. The only allowed casts involving booleans are these:
1) the cast of a boolean to a character type
2) the cast of a character type to a boolean
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rajesh Kartha (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364519 ]
Rajesh Kartha commented on DERBY-887:
-------------------------------------
Bernt Johnsen replied to my question on the SQL standard on the mailing list. Following is the archive link:
http://article.gmane.org/gmane.comp.apache.db.derby.devel/13128
<quote>
The SQL spec (99) chapter 6.22 states that a BOOLEAN may be converted
(with a CAST specification) to CHAR, VARCHAR, BOOLEAN and CLOB and
that CHAR, VARCHAR, CLOB and BOOLEAN may be converted to BOOLEAN.
<unquote>
So if the BOOLEAN and INT types are not compatabile, the select statements with 'id=0<3' and 'id=true'
needs to throw an error.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364280 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
Hi Manjula,
Could you attach a script (including the ddl and table population) which demonstrates this problem? The following simplified test seems to work correctly. Thanks.
ij> connect 'jdbc:derby:testdb';
ij> --connect 'jdbc:derby:testdb;create=true';
--connect 'jdbc:derby://localhost:8246/testdb';
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1( id bigint );
0 rows inserted/updated/deleted
ij> insert into t1 ( id ) values ( 0 ), ( 1 ), ( 2 ), ( 4 );
4 rows inserted/updated/deleted
ij> select * from t1
where id = true;
ID
--------------------
1
2
4
3 rows selected
ij> select * from t1 where id = 0<3;
ID
--------------------
1
2
4
3 rows selected
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Fix For: 10.2.0.0
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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] Assigned: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-887:
------------------------------------
Assignee: Kathey Marsden (was: Rick Hillegas)
Assigning to myself for back port to 10.5
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Kathey Marsden
> Fix For: 10.6.1.0
>
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Attachment: derby-887-01-ag-noImplicitCasts.diff
Attaching derby-887-01-ag-noImplicitCasts.diff. This patch disables the illegal implicit and explicit casting between BOOLEANs and non-string data types. I am running regression tests now.
As pointed out in previous comments, one tricky piece of this bug is the fact that the Derby metadata queries rely on illegal casts which involve BOOLEANs. The solution proposed here is:
1) Raise coercion errors when trying to bind an expression which needs to cast between BOOLEANs and non-string types.
2) Change all of the metadata queries which have illegal BOOLEAN casts. Replace the casts with calls to the INTEGER function. That function can already convert a BOOLEAN to an INT, although our user guides can't say that because technically BOOLEAN is not supported.
3) Adds new test cases to verify the correct casting behavior. One class of tests cannot be written today: the explicit casting of other data types to BOOLEAN. Those tests cannot be written until BOOLEAN is re-enabled.
Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/BooleanTypeCompiler.java
M java/engine/org/apache/derby/impl/sql/compile/CLOBTypeCompiler.java
M java/engine/org/apache/derby/impl/sql/compile/BaseTypeCompiler.java
M java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
M java/engine/org/apache/derby/impl/sql/compile/BitTypeCompiler.java
M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
Raise errors if an attempt is made to coerce between BOOLEAN and a non-string data type.
M java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java
Change metadata queries to use the INTEGER function rather than illegal casts.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/CastingTest.java
Add new test cases to verify that the bug is fixed and to verify that we have disabled implicit and explict casting between BOOLEANs and non-string data types.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
M java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
Removed a test case which relied on illegal casts.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/ProcedureInTriggerTest.java
Reworded a test case which relied on illegal casts.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Affects Version/s: 10.0.2.0
10.0.2.1
10.1.1.0
10.1.2.1
10.1.3.1
10.2.2.0
10.3.1.4
10.3.2.1
10.3.3.0
10.4.1.3
10.4.2.0
10.5.1.1
10.5.2.0
10.5.3.0
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Fix For: 10.6.0.0
>
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364633 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
If you add an explicit cast to the problem query, it returns "correct" results:
select * from t where cast(id as boolean) = true;
However, as Bernt points out, the cast (explicit or implicit) is illegal according to the ANSI spec.
So, I propose to fix this bug by forbidding these casts, unless someone objects. Thanks to Manjula for logging this issue and for everyone's feedback so far.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12766188#action_12766188 ]
Rick Hillegas commented on DERBY-887:
-------------------------------------
Hooray. The regression tests passed cleanly on the derby-887-01-aj-noImplicitCasts.diff patch.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Commented: (DERBY-887) Select statement returns wrong
number of rows if you compare an integer column with a boolean expression
in the where clause
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
> I have trudged some way into this bug and would like to ask the
> community's advice.
>
> If we adopt the SQL spec's rules for casting to/from BOOLEAN, then we
> have to forbid the casting of BOOLEAN to integer types. Unfortunately,
> we have system procedures which do just this. Some of our system
> procedures cast the BOOLEAN columns in system tables to SMALLINT. In
> particular, SYSIBM.SQLGETTYPEINFO performs this cast when asked to
> retrieve ODBC type info.
>
Thanks Rick for thinking about backward compatibility.
For internal changes an adjustment like that seems ok but the
*really* important thing is that we make sure that if
metadata.properties is being changed that these calls still work on soft
upgrade and particularly going back to an earlier version. I have
always been an advocate of just dropping these all together when you go
up or down in version, but got no traction with this idea when I
mentioned it before. We have had some very unfortunate bugs in the past
because of changes to the metadata file requiring time travel to fix.
There may be an external impact to some users as well. It really
bothers me to disable f functionality that users may be relying upon,
but in this case I am really hoping it is not going to be a big problem,
since that functionality looks pretty broken anyway and is not
documented. You may wish to mention the change to derby-user and see
if anyone has objections. It is important to document this risk in the
release notes. I have on my list to start a Jira entry or Wiki to
compile all of the changes that might affect existing users, but have
not gotten to it yet.
As for it being the tip of the iceburg, I don't know.
Kathey
Re: [jira] Commented: (DERBY-887) Select statement returns wrong
number of rows if you compare an integer column with a boolean expression in
the where clause
Posted by Rick Hillegas <Ri...@Sun.COM>.
I have trudged some way into this bug and would like to ask the
community's advice.
If we adopt the SQL spec's rules for casting to/from BOOLEAN, then we
have to forbid the casting of BOOLEAN to integer types. Unfortunately,
we have system procedures which do just this. Some of our system
procedures cast the BOOLEAN columns in system tables to SMALLINT. In
particular, SYSIBM.SQLGETTYPEINFO performs this cast when asked to
retrieve ODBC type info.
I suspect we can kludge around this particular problem by replacing the
CAST with a call to a coercing function. Howver, I fear this may be the
tip of the iceberg. I am worried that removing the old, non-ANSI casting
behavior (inherited from Cloudscape days) may create other backward
compatibility problems.
I would like to ask for your guidance.
Thanks,
-Rick
Re: [jira] Commented: (DERBY-887) Select statement returns wrong
number of rows if you compare an integer column with a boolean expression in
the where clause
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Kathey,
Sure. I'm also adjusting the casting tests in BooleanTest.java.
Regards,
-Rick
Kathey Marsden (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364653 ]
>
>Kathey Marsden commented on DERBY-887:
>--------------------------------------
>
>Thanks Rick for looking at this issue.
>As part of the fix, can you add BOOLEAN to the the cast and comparison tables in the lang/casting.java test?
>
>Thanks
>
>Kathey
>
>
>
>
>>Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
>>----------------------------------------------------------------------------------------------------------------------------
>>
>> Key: DERBY-887
>> URL: http://issues.apache.org/jira/browse/DERBY-887
>> Project: Derby
>> Type: Bug
>> Components: SQL
>> Versions: 10.2.0.0
>> Environment: Java : java version "1.5.0_02"
>>Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
>>Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
>>Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
>>OS: Windows XP Professional
>> Reporter: Manjula Kutty
>> Assignee: Rick Hillegas
>> Priority: Critical
>> Fix For: 10.2.0.0
>> Attachments: DERBY-887.sql
>>
>>I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
>> ij> select id,date from inbox where id = 0<3;
>>ID |DATE
>>-----------------------------------------------
>>25 |2006-01-26 14:35:46.584
>>34 |2006-01-26 14:36:16.588
>>21 |2006-01-26 14:34:46.455
>>22 |2006-01-26 14:34:47.176
>>27 |2006-01-26 14:35:47.054
>>24 |2006-01-26 14:35:16.58
>>28 |2006-01-26 14:35:47.305
>>35 |2006-01-26 14:36:18.771
>>31 |2006-01-26 14:35:48.496
>>32 |2006-01-26 14:35:48.887
>>33 |2006-01-26 14:35:49.308
>>11 rows selected
>>ij> select id,date from inbox where id = true;
>>ID |DATE
>>-----------------------------------------------
>>21 |2006-01-26 14:34:46.455
>>1 row selected
>>Both queries should return same number of rows
>>Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
>>ij> delete from inbox where id=21;
>>1 row inserted/updated/deleted
>>Then again run
>>ij> select id ,date from inbox where id=true;
>>ID |DATE
>>-----------------------------------------------
>>22 |2006-01-26 14:35:16.58
>>Which indicates that the select returns only the first row and then is getting closed
>>
>>
>
>
>
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364653 ]
Kathey Marsden commented on DERBY-887:
--------------------------------------
Thanks Rick for looking at this issue.
As part of the fix, can you add BOOLEAN to the the cast and comparison tables in the lang/casting.java test?
Thanks
Kathey
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Attachment: releaseNote.html
Attaching first rev of a release note for this bug-fix.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas updated DERBY-887:
--------------------------------
Attachment: bug887_interim.diff
Hi Satheesh,
I have attached my half-baked work on this bug: bug887_interim.diff. If someone else wants to pick up this JIRA, they may find that patch useful. In particular, the patch contains changes to various datatype classes which disable the illegal explicit and implicit casts. However, although the patch fixes the bugs, it breaks some other functionality including some unit tests. In particular, it breaks the ODBC metadata mapping, which relies on these illegal casts. My fix to that problem was to add a system function which performs the job formerly done by the illegal cast.
I have not worked on this for a week and a half and it's clear that I am swamped with other work. I too would like to see this bug fixed for 10.2 but the reality is, I am not going to get to it soon. If someone else is itching badly, please go ahead and scratch.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql, bug887_interim.diff, bug887_interim.diff
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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] Assigned: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Satheesh Bandaram reassigned DERBY-887:
---------------------------------------
Assign To: Rick Hillegas
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Priority: Critical
> Fix For: 10.3.0.0
> Attachments: DERBY-887.sql, bug887_interim.diff, bug887_interim.diff
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364281 ]
Daniel John Debrunner commented on DERBY-887:
---------------------------------------------
Is it valid to compare integer columns to boolean columns?
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Fix For: 10.2.0.0
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas updated DERBY-887:
--------------------------------
Priority: Major (was: Critical)
Reducing the priority of this bug. With the patch for DERBY-1029, the behavior has reverted to what it was in 10.1.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assigned To: Rick Hillegas
> Fix For: 10.3.0.0
>
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Satheesh Bandaram updated DERBY-887:
------------------------------------
Fix Version: 10.3.0.0
(was: 10.2.0.0)
Moving this to fix under 10.3. David proposed that boolean work be left on trunk to be completed in 10.3 timeframe and removed from 10.2 once a branch is cut. Since the rollback of boolean work for 10.2 is being tracked under DERBY-1029, I am moving this one to 10.3.
Rick, I am assigning this 10.3 issue back to you... expecting you would be working on this for 10.3. Let me know if this is incorrect.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.3.0.0
> Attachments: DERBY-887.sql, bug887_interim.diff, bug887_interim.diff
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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] Reopened: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reopened DERBY-887:
----------------------------------
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Fix For: 10.6.1.0
>
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas reassigned DERBY-887:
-----------------------------------
Assign To: (was: Rick Hillegas)
I am relinquishing this bug right now. It has been crowded off my calendar.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Manjula Kutty (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12364285 ]
Manjula Kutty commented on DERBY-887:
-------------------------------------
Even I feel the same. Still if it is valid then it should show consistent behaviour.
I just happend to run this query and was expecting an error message , but ij returned me some rows. In my schema I had primary key on the column id. If I remove the primary key then evry thing works fine
I'm attaching the script to reproduce the problem.
The derby jars I have used are :
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Fix For: 10.2.0.0
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Kathey Marsden updated DERBY-887:
---------------------------------
Priority: Critical (was: Major)
I am marking this as critical since we get wrong results for a query that previously failed with a syntax error.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas updated DERBY-887:
--------------------------------
Fix Version/s: (was: 10.3.0.0)
Detaching this issue from 10.3. Fixing it seems to require changing the non-ANSI behavior of implicit and explicit casts. Changing this behavior probably will affect existing applications and it seems to be the sort of fix which we need to defer to a major release. That is, this is appropriate for 11.0 but not 10.3.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assigned To: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Bug behavior facts: [Wrong query result]
Marking as wrong query result.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas updated DERBY-887:
--------------------------------
Attachment: bug887_interim.diff
Hi Satheesh,
I have attached my half-baked work on this bug: bug887_interim.diff. If someone else wants to pick up this JIRA, they may find that patch useful. In particular, the patch contains changes to various datatype classes which disable the illegal explicit and implicit casts. However, although the patch fixes the bugs, it breaks some other functionality including some unit tests. In particular, it breaks the ODBC metadata mapping, which relies on these illegal casts. My fix to that problem was to add a system function which performs the job formerly done by the illegal cast.
I have not worked on this for a week and a half and it's clear that I am swamped with other work. I too would like to see this bug fixed for 10.2 but the reality is, I am not going to get to it soon. If someone else is itching badly, please go ahead and scratch.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql, bug887_interim.diff
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Jazarine Jamal (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12547622 ]
Jazarine Jamal commented on DERBY-887:
--------------------------------------
i encoutnered the following problem when trying to run the queries.....
ij> create table test887(id bigint);
0 rows inserted/updated/deleted
ij> INSERT INTO TEST887 VALUES (801), (802), (803), (804);
ij> select * from test887 where id = 0<3;
ID
--------------------
801
802
803
804
ij> select * from test887 where id = true;
ERROR 42X01: Syntax error: true.
i dont understand why is the second statement giving a syntax error while the first one generates the correct output....
both (0<3) and true are equivalent....
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-887:
--------------------------------
Fix Version/s: 10.6.0.0
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Fix For: 10.6.0.0
>
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=comments#action_12366269 ]
Satheesh Bandaram commented on DERBY-887:
-----------------------------------------
Rick, do you know if you or someone else might be able to address this issue soon? I thought, at least, part of the problem was introduced by boolean type work. If it can be deemed a "regression", I think this bug should be on high priority list for someone. You know who is looking for regressions in the product, very closely :)
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Priority: Critical
> Fix For: 10.2.0.0
> Attachments: DERBY-887.sql
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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] Resolved: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-887.
---------------------------------
Resolution: Fixed
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: https://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Assignee: Rick Hillegas
> Attachments: bug887_interim.diff, bug887_interim.diff, derby-887-01-ag-noImplicitCasts.diff, derby-887-01-aj-noImplicitCasts.diff, DERBY-887.sql, releaseNote.html
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-887) Select statement returns wrong number
of rows if you compare an integer column with a boolean expression in the
where clause
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-887?page=all ]
Rick Hillegas reassigned DERBY-887:
-----------------------------------
Assignee: (was: Rick Hillegas)
Unassigning myself from this issue. We have moved out the timeframe for fixing this bug.
> Select statement returns wrong number of rows if you compare an integer column with a boolean expression in the where clause
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-887
> URL: http://issues.apache.org/jira/browse/DERBY-887
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.0.0
> Environment: Java : java version "1.5.0_02"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_02-b09)
> Java HotSpot(TM) Client VM (build 1.5.0_02-b09, mixed mode)
> Classpath : c:/pantry/derby.jar;c:/pantry/derbytools.jar;
> OS: Windows XP Professional
> Reporter: Manjula Kutty
> Attachments: bug887_interim.diff, bug887_interim.diff, DERBY-887.sql
>
>
> I have a table with 2 columns one is BIGINT which is Generated always starting with 1 and increment by1 and the other one is timestamp
> ij> select id,date from inbox where id = 0<3;
> ID |DATE
> -----------------------------------------------
> 25 |2006-01-26 14:35:46.584
> 34 |2006-01-26 14:36:16.588
> 21 |2006-01-26 14:34:46.455
> 22 |2006-01-26 14:34:47.176
> 27 |2006-01-26 14:35:47.054
> 24 |2006-01-26 14:35:16.58
> 28 |2006-01-26 14:35:47.305
> 35 |2006-01-26 14:36:18.771
> 31 |2006-01-26 14:35:48.496
> 32 |2006-01-26 14:35:48.887
> 33 |2006-01-26 14:35:49.308
> 11 rows selected
> ij> select id,date from inbox where id = true;
> ID |DATE
> -----------------------------------------------
> 21 |2006-01-26 14:34:46.455
> 1 row selected
> Both queries should return same number of rows
> Also If I delete the first row from the table then select id,date from inbox where id = true; returns the second row
> ij> delete from inbox where id=21;
> 1 row inserted/updated/deleted
> Then again run
> ij> select id ,date from inbox where id=true;
> ID |DATE
> -----------------------------------------------
> 22 |2006-01-26 14:35:16.58
> Which indicates that the select returns only the first row and then is getting closed
--
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