db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject 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
Date Mon, 30 Jan 2006 11:16:51 GMT
>>>>>>>>>>>> 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

Mime
View raw message