>>>>>>>>>>>> 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
pgpYvPElHQdNc.pgp
Description: PGP signature
