You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by "parthasarathy (JIRA)" <ji...@apache.org> on 2006/10/09 10:29:19 UTC

[jira] Created: (TORQUE-58) Where clause with zero length string should be converted to NULL if db is Oracle

Where clause with zero length string should be converted to NULL if db is Oracle
--------------------------------------------------------------------------------

                 Key: TORQUE-58
                 URL: http://issues.apache.org/jira/browse/TORQUE-58
             Project: Torque
          Issue Type: Bug
          Components: Runtime
    Affects Versions: 3.2.1
         Environment: All OS, All Hardware, Oracle db
            Reporter: parthasarathy
             Fix For: 3.2.1


Oracle treats Zero length characters as NULL. Consider the following case 
-- Table create --
create table nullzero (field1 varchar2(10), field2 varchar2(10));

-- Record insert --
insert into nullzero('',null);

-- Query --

select * from nullzero where field1 = "" -- returns no rows

select * from nullzero where field2 is null -- returns the inserted row

select * from nullzero where field1 is null -- returns the inserted row.


The first statement should have returned a record as we inserted "" for field1. But it did not return a row as Oracle has converted the "" to NULL during insert. We cannot change the where clause as we want the criteria to be db agnostic. For example the first statement would have worked in other dbs. 

We should handle this case in torque to change the where clause to NULL if it is "" (zero length string) . One place where we can do this is at build() method of org.apache. torque.util.SQLExpression.



-- 
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

        

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

[jira] Commented: (TORQUE-58) Where clause with zero length string should be converted to NULL if db is Oracle

Posted by "Thomas Fischer (JIRA)" <ji...@apache.org>.
    [ http://issues.apache.org/jira/browse/TORQUE-58?page=comments#action_12442812 ] 
            
Thomas Fischer commented on TORQUE-58:
--------------------------------------

As users could rely on this behaviour, this should not be changed in a minor release. I'd suggest addressing this in 4.0 (see also discussion in TORQUE-44

> Where clause with zero length string should be converted to NULL if db is Oracle
> --------------------------------------------------------------------------------
>
>                 Key: TORQUE-58
>                 URL: http://issues.apache.org/jira/browse/TORQUE-58
>             Project: Torque
>          Issue Type: Bug
>          Components: Runtime
>    Affects Versions: 3.2.1
>         Environment: All OS, All Hardware, Oracle db
>            Reporter: parthasarathy
>
> Oracle treats Zero length characters as NULL. Consider the following case 
> -- Table create --
> create table nullzero (field1 varchar2(10), field2 varchar2(10));
> -- Record insert --
> insert into nullzero('',null);
> -- Query --
> select * from nullzero where field1 = "" -- returns no rows
> select * from nullzero where field2 is null -- returns the inserted row
> select * from nullzero where field1 is null -- returns the inserted row.
> The first statement should have returned a record as we inserted "" for field1. But it did not return a row as Oracle has converted the "" to NULL during insert. We cannot change the where clause as we want the criteria to be db agnostic. For example the first statement would have worked in other dbs. 
> We should handle this case in torque to change the where clause to NULL if it is "" (zero length string) . One place where we can do this is at build() method of org.apache. torque.util.SQLExpression.

-- 
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

        

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org

[jira] Updated: (TORQUE-58) Where clause with zero length string should be converted to NULL if db is Oracle

Posted by "Thomas Fischer (JIRA)" <ji...@apache.org>.
     [ http://issues.apache.org/jira/browse/TORQUE-58?page=all ]

Thomas Fischer updated TORQUE-58:
---------------------------------

    Fix Version/s:     (was: 3.2.1)

> Where clause with zero length string should be converted to NULL if db is Oracle
> --------------------------------------------------------------------------------
>
>                 Key: TORQUE-58
>                 URL: http://issues.apache.org/jira/browse/TORQUE-58
>             Project: Torque
>          Issue Type: Bug
>          Components: Runtime
>    Affects Versions: 3.2.1
>         Environment: All OS, All Hardware, Oracle db
>            Reporter: parthasarathy
>
> Oracle treats Zero length characters as NULL. Consider the following case 
> -- Table create --
> create table nullzero (field1 varchar2(10), field2 varchar2(10));
> -- Record insert --
> insert into nullzero('',null);
> -- Query --
> select * from nullzero where field1 = "" -- returns no rows
> select * from nullzero where field2 is null -- returns the inserted row
> select * from nullzero where field1 is null -- returns the inserted row.
> The first statement should have returned a record as we inserted "" for field1. But it did not return a row as Oracle has converted the "" to NULL during insert. We cannot change the where clause as we want the criteria to be db agnostic. For example the first statement would have worked in other dbs. 
> We should handle this case in torque to change the where clause to NULL if it is "" (zero length string) . One place where we can do this is at build() method of org.apache. torque.util.SQLExpression.

-- 
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

        

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org