subquery.out

derby database source code.good for you.

No open scans, etc.
2 dependencies found                                                                                        
ij> select * from s where s = (select s from t where s = 1);
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
2 dependencies found                                                                                        
ij> update s set b = (select max(b) from t)
where vc <> (select vc from t where vc = '1');
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
2 dependencies found                                                                                        
ij> delete from s where c = (select c from t where c = '1');
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- do consistency check on scans, etc.
values ConsistencyChecker();
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
No open scans, etc.
2 dependencies found                                                                                        
ij> -- positive tests
select * from s;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from t;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
1          |1     |1                             |1                             |1                   
2          |2     |2                             |2                             |1                   
ij> -- simple subquery for each data type
select * from s where i = (select i from t where i = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where s = (select s from t where s = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where c = (select c from t where c = '0');
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where vc = (select vc from t where vc = '0');
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where b = (select max(b) from t where b = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where b = (select max(b) from t where i = 2);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
1          |1     |1                             |1                             |1                   
ij> -- ? parameter on left hand side of expression subquery
prepare subq1 as 'select * from s where ? = (select i from t where i = 0)';
ij> execute subq1 using 'values (0)';
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> remove subq1;
ij> -- conversions
select * from s where i = (select s from t where s = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where s = (select i from t where i = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where c = (select vc from t where vc = '0');
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> select * from s where vc = (select c from t where c = '0');
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> -- (select nullable_column ...) is null
-- On of each data type to test clone()
select * from s where (select s from s where i is null) is null;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where (select i from s where i is null) is null;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where (select c from s where i is null) is null;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where (select vc from s where i is null) is null;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where (select b from s where i is null) is null;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where
	(select 1 from t where exists (select * from t where 1 = 0) and s = -1) is null;
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> -- subquery = subquery
select * from s where
(select i from t where i = 0) = (select s from t where s = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> -- multiple subqueries at the same level
select * from s
where i = (select s from t where s = 0) and
	  s = (select i from t where i = 2);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
ij> select * from s
where i = (select s from t where s = 0) and
	  s = (select i from t where i = 0);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
ij> -- nested subqueries
select * from s
where i = (select i from t where s = (select i from t where s = 2));
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
ij> select * from s
where i = (select i - 1 from t where s = (select i from t where s = 2));
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
1          |1     |1                             |1                             |1                   
ij> -- expression subqueries in select list
select (select i from t where 0=1) from s;
1          
-----------
NULL       
NULL       
NULL       
ij> select (select i from t where i = 2) * (select s from t where i = 2) from s
where i > (select i from t where i = 0) - (select i from t where i = 0);
1          
-----------
4          
ij> -- in subqueries
-- negative tests
-- select * subquery
select * from s where s in (select * from s);
ERROR 42X38: 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.
ij> -- incompatable types
select * from s where s in (select b from t);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> -- positive tests
-- constants on left side of subquery
select * from s where 1 in (select s from t);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where -1 in (select i from t);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
ij> select * from s where '1' in (select vc from t);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------
NULL       |NULL  |NULL                          |NULL                          |NULL                
0          |0     |0                             |0                             |0                   
1          |1     |1                             |1                             |1                   
ij> select * from s where 0 in (select b from t);
I          |S     |C                             |VC                            |B                   
-----------------------------------------------------------------------------------------------------