paramij.out

derby database source code.good for you.

ij> --
-- test that we do not allow incorrect where <boolean> syntax
create table t1 (c11 int);
0 rows inserted/updated/deleted
ij> insert into t1 values (1);
1 row inserted/updated/deleted
ij> autocommit off;
ij> -- positive tests
-- In following test cases, where clause results in a boolean value
prepare p1 as 'select * from t1 where ?=1';
ij> execute p1 using 'values(1)';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where ? like ''2'' ';
ij> execute p1 using 'values(''a'')';
C11        
-----------
ij> prepare p1 as 'select * from t1 where not ? > 1';
ij> execute p1 using 'values(1)';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where lower(?) = ''a''';
ij> execute p1 using 'values(''a'')';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where {fn length(?)} > 1';
ij> execute p1 using 'values(''a'')';
C11        
-----------
ij> prepare p1 as 'select * from t1 where {fn locate(?,''a'',1)} = 1';
ij> execute p1 using 'values(''a'')';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where ? between 1 and 3';
ij> execute p1 using 'values(2)';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where ? in (1, ?)';
ij> execute p1 using 'values(2,1)';
C11        
-----------
ij> prepare p1 as 'select * from t1 where ? is null';
ij> execute p1 using 'values(1)';
C11        
-----------
ij> prepare p1 as 'select * from t1 where ? is not null';
ij> execute p1 using 'values(1)';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where ? <> ALL (values(1))';
ij> execute p1 using 'values(3)';
C11        
-----------
1          
ij> prepare p1 as 'select * from t1 where exists (select c11 from t1 where 1=?)';
ij> execute p1 using 'values(3)';
C11        
-----------
ij> prepare p1 as 'select * from t1 where cast(? as int) = 1';
ij> execute p1 using 'values(1)';
C11        
-----------
1          
ij> -- negative tests
-- In following test cases, there is no way to ensure where with ? will result in a boolean value
prepare p1 as 'select * from t1 where c11';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where c11+1';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where 1';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where ?';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where ? for update';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where (?)';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where ? and 1=1';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where ? and 1=? or 2=2';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'PARAMETER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where not ?';
ERROR 42X36: The 'not' operator is not allowed to take a ? parameter as an operand.
ij> prepare p1 as 'select * from t1 where lower(?)';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'VARCHAR' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where lower(?) and 1=1';
ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN.  
ij> prepare p1 as 'select * from t1 where {fn length(?)}';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where {fn locate(?,''a'',1)}';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where cast(? as int)';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression.  It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where (?||''1'')';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'LONG VARCHAR' expression.  It must be a BOOLEAN expression.
ij>