db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bakk...@apache.org
Subject svn commit: r329187 [36/66] - in /db/derby/code/trunk: ./ frameworks/NetworkServer/ frameworks/NetworkServer/bin/ frameworks/embedded/bin/ java/build/ java/build/org/apache/derbyBuild/ java/build/org/apache/derbyBuild/eclipse/ java/build/org/apache/der...
Date Fri, 28 Oct 2005 12:52:21 GMT
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out Fri Oct 28 04:51:50 2005
@@ -1,2789 +1,2789 @@
-ij> --
--- tests for DISTINCT
---
--- these tests assume: no indexes, no order by, no grouping
---
--- test plan is represented by '.' items in comments. 
--- the flavors of select are shown in distinct.subsql, which is
--- run over a variety of data configurations.
--- this file expects to be run from a directory under $WS/systest.
--- speed up a fraction with autocommit off...
-autocommit off;
-ij> create table t (i int, s smallint, r real, f float, d date, t time,
-	ts timestamp, c char(10), v varchar(20));
-0 rows inserted/updated/deleted
-ij> -- data flavor:
--- . no data at all (filtered out or just plain empty)
-run resource 'distinct.subsql';
-ij> --
--- this is the script we run over various data flavors of the following
--- table.  distinct.sql uses this script repeatedly.
---
--- create table t (i int, s smallint, r real, f float, 
---  d date, t time, ts timestamp, c char(10), v varchar(20));
--- here are the flavors of select:
--- . select distinct one column, each data type (include usertypes)
-select distinct i from t;
-I          
------------
-ij> select distinct s from t;
-S     
-------
-ij> select distinct r from t;
-R            
--------------
-ij> select distinct f from t;
-F                     
-----------------------
-ij> select distinct d from t;
-D         
-----------
-ij> select distinct t from t;
-T       
---------
-ij> select distinct ts from t;
-TS                        
---------------------------
-ij> select distinct c from t;
-C         
-----------
-ij> select distinct v from t;
-V                   
---------------------
-ij> -- . select distinct multiple columns, each data type
--- . select distinct all or just some columns of the table
-select distinct t,i,s,f,d from t;
-T       |I          |S     |F                     |D         
--------------------------------------------------------------
-ij> select distinct * from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select distinct t.*,ts from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TS                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an exists subquery
-select * from t where exists (select distinct i from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where exists (select distinct * from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where not exists (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an in subquery
-select * from t where i in (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where s not in (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in a quantified subquery
--- same result as i in distinct s above
-select * from t where i =any (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- same result as s not in distinct r above
-select * from t where s <>any (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where d >=any (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where t <=all (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in a scalar subquery
-select * from t where c = (select distinct v from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where v < (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in a from subquery
-select * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-ij> select * from (select distinct * from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from (select distinct t.*,ts as tts from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in a from subquery joining with another table
-select * from t, (select distinct t.*,ts as tts from t) as s where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from (select distinct t.*,ts as tts from t) as s, t where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . multiple select distincts -- outer & sqs, just sqs, outer & from(s)
-select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-ij> select i, s from t as touter
-where touter.i in (select distinct i from t)
-and exists (select distinct s from t as ti where touter.s=ti.s);
-I          |S     
-------------------
-ij> -- same result as exists above
-select i, s from t as touter
-where touter.i in (select distinct i from t)
-and touter.s =any (select distinct s from t);
-I          |S     
-------------------
-ij> select distinct i, s from t
-where t.i in (select distinct i from t)
-and t.s in (select distinct s from t);
-I          |S     
-------------------
-ij> -- . select distinct under a union all/ over a union all
--- expect 2 rows of any value
-select distinct i from t
-union all
-select distinct i from t;
-I          
------------
-ij> -- at most 1 row of any value
-select distinct * from (select i from t union all select i from t) as s;
-I          
------------
-ij> -- . select distinct over a from subquery (itself distinct/not)
-select distinct * from (select t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-ij> select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-ij> -- . select distinct over a join
-select distinct * from t t1, t t2 where t1.i = t2.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . 1 row
-insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'hello', 'planet');
-1 row inserted/updated/deleted
-ij> run resource 'distinct.subsql';
-ij> --
--- this is the script we run over various data flavors of the following
--- table.  distinct.sql uses this script repeatedly.
---
--- create table t (i int, s smallint, r real, f float, 
---  d date, t time, ts timestamp, c char(10), v varchar(20));
--- here are the flavors of select:
--- . select distinct one column, each data type (include usertypes)
-select distinct i from t;
-I          
------------
-1          
-ij> select distinct s from t;
-S     
-------
-2     
-ij> select distinct r from t;
-R            
--------------
-3.0          
-ij> select distinct f from t;
-F                     
-----------------------
-4.0                   
-ij> select distinct d from t;
-D         
-----------
-1992-01-01
-ij> select distinct t from t;
-T       
---------
-19:01:01
-ij> select distinct ts from t;
-TS                        
---------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select distinct c from t;
-C         
-----------
-hello     
-ij> select distinct v from t;
-V                   
---------------------
-planet              
-ij> -- . select distinct multiple columns, each data type
--- . select distinct all or just some columns of the table
-select distinct t,i,s,f,d from t;
-T       |I          |S     |F                     |D         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select distinct * from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select distinct t.*,ts from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TS                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in an exists subquery
-select * from t where exists (select distinct i from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where exists (select distinct * from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where not exists (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an in subquery
-select * from t where i in (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where s not in (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a quantified subquery
--- same result as i in distinct s above
-select * from t where i =any (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- same result as s not in distinct r above
-select * from t where s <>any (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where d >=any (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where t <=all (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a scalar subquery
-select * from t where c = (select distinct v from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where v < (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
-ij> -- . select distinct in a from subquery
-select * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select * from (select distinct * from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from (select distinct t.*,ts as tts from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in a from subquery joining with another table
-select * from t, (select distinct t.*,ts as tts from t) as s where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select * from (select distinct t.*,ts as tts from t) as s, t where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . multiple select distincts -- outer & sqs, just sqs, outer & from(s)
-select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select i, s from t as touter
-where touter.i in (select distinct i from t)
-and exists (select distinct s from t as ti where touter.s=ti.s);
-I          |S     
-------------------
-1          |2     
-ij> -- same result as exists above
-select i, s from t as touter
-where touter.i in (select distinct i from t)
-and touter.s =any (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-ij> select distinct i, s from t
-where t.i in (select distinct i from t)
-and t.s in (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-ij> -- . select distinct under a union all/ over a union all
--- expect 2 rows of any value
-select distinct i from t
-union all
-select distinct i from t;
-I          
------------
-1          
-1          
-ij> -- at most 1 row of any value
-select distinct * from (select i from t union all select i from t) as s;
-I          
------------
-1          
-ij> -- . select distinct over a from subquery (itself distinct/not)
-select distinct * from (select t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> -- . select distinct over a join
-select distinct * from t t1, t t2 where t1.i = t2.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . all rows the same
-insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'hello', 'planet');
-1 row inserted/updated/deleted
-ij> run resource 'distinct.subsql';
-ij> --
--- this is the script we run over various data flavors of the following
--- table.  distinct.sql uses this script repeatedly.
---
--- create table t (i int, s smallint, r real, f float, 
---  d date, t time, ts timestamp, c char(10), v varchar(20));
--- here are the flavors of select:
--- . select distinct one column, each data type (include usertypes)
-select distinct i from t;
-I          
------------
-1          
-ij> select distinct s from t;
-S     
-------
-2     
-ij> select distinct r from t;
-R            
--------------
-3.0          
-ij> select distinct f from t;
-F                     
-----------------------
-4.0                   
-ij> select distinct d from t;
-D         
-----------
-1992-01-01
-ij> select distinct t from t;
-T       
---------
-19:01:01
-ij> select distinct ts from t;
-TS                        
---------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select distinct c from t;
-C         
-----------
-hello     
-ij> select distinct v from t;
-V                   
---------------------
-planet              
-ij> -- . select distinct multiple columns, each data type
--- . select distinct all or just some columns of the table
-select distinct t,i,s,f,d from t;
-T       |I          |S     |F                     |D         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select distinct * from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select distinct t.*,ts from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TS                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in an exists subquery
-select * from t where exists (select distinct i from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where exists (select distinct * from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where not exists (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an in subquery
-select * from t where i in (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where s not in (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a quantified subquery
--- same result as i in distinct s above
-select * from t where i =any (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- same result as s not in distinct r above
-select * from t where s <>any (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where d >=any (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where t <=all (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a scalar subquery
-select * from t where c = (select distinct v from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where v < (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
-ij> -- . select distinct in a from subquery
-select * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select * from (select distinct * from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from (select distinct t.*,ts as tts from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in a from subquery joining with another table
-select * from t, (select distinct t.*,ts as tts from t) as s where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select * from (select distinct t.*,ts as tts from t) as s, t where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . multiple select distincts -- outer & sqs, just sqs, outer & from(s)
-select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select i, s from t as touter
-where touter.i in (select distinct i from t)
-and exists (select distinct s from t as ti where touter.s=ti.s);
-I          |S     
-------------------
-1          |2     
-1          |2     
-ij> -- same result as exists above
-select i, s from t as touter
-where touter.i in (select distinct i from t)
-and touter.s =any (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-1          |2     
-ij> select distinct i, s from t
-where t.i in (select distinct i from t)
-and t.s in (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-ij> -- . select distinct under a union all/ over a union all
--- expect 2 rows of any value
-select distinct i from t
-union all
-select distinct i from t;
-I          
------------
-1          
-1          
-ij> -- at most 1 row of any value
-select distinct * from (select i from t union all select i from t) as s;
-I          
------------
-1          
-ij> -- . select distinct over a from subquery (itself distinct/not)
-select distinct * from (select t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> -- . select distinct over a join
-select distinct * from t t1, t t2 where t1.i = t2.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . variety of rows, some same and some different
-insert into t values (2, 1, 4, 3, '1992-01-01', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'hello', 'planet');
-1 row inserted/updated/deleted
-ij> run resource 'distinct.subsql';
-ij> --
--- this is the script we run over various data flavors of the following
--- table.  distinct.sql uses this script repeatedly.
---
--- create table t (i int, s smallint, r real, f float, 
---  d date, t time, ts timestamp, c char(10), v varchar(20));
--- here are the flavors of select:
--- . select distinct one column, each data type (include usertypes)
-select distinct i from t;
-I          
------------
-2          
-1          
-ij> select distinct s from t;
-S     
-------
-2     
-1     
-ij> select distinct r from t;
-R            
--------------
-4.0          
-3.0          
-ij> select distinct f from t;
-F                     
-----------------------
-4.0                   
-3.0                   
-ij> select distinct d from t;
-D         
-----------
-1992-01-01
-ij> select distinct t from t;
-T       
---------
-19:01:01
-ij> select distinct ts from t;
-TS                        
---------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select distinct c from t;
-C         
-----------
-hello     
-ij> select distinct v from t;
-V                   
---------------------
-planet              
-ij> -- . select distinct multiple columns, each data type
--- . select distinct all or just some columns of the table
-select distinct t,i,s,f,d from t;
-T       |I          |S     |F                     |D         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select distinct * from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select distinct t.*,ts from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TS                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in an exists subquery
-select * from t where exists (select distinct i from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where exists (select distinct * from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where not exists (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an in subquery
-select * from t where i in (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where s not in (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a quantified subquery
--- same result as i in distinct s above
-select * from t where i =any (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- same result as s not in distinct r above
-select * from t where s <>any (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where d >=any (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where t <=all (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a scalar subquery
-select * from t where c = (select distinct v from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where v < (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
-ij> -- . select distinct in a from subquery
-select * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select * from (select distinct * from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from (select distinct t.*,ts as tts from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in a from subquery joining with another table
-select * from t, (select distinct t.*,ts as tts from t) as s where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select * from (select distinct t.*,ts as tts from t) as s, t where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . multiple select distincts -- outer & sqs, just sqs, outer & from(s)
-select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select i, s from t as touter
-where touter.i in (select distinct i from t)
-and exists (select distinct s from t as ti where touter.s=ti.s);
-I          |S     
-------------------
-1          |2     
-1          |2     
-2          |1     
-ij> -- same result as exists above
-select i, s from t as touter
-where touter.i in (select distinct i from t)
-and touter.s =any (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-1          |2     
-2          |1     
-ij> select distinct i, s from t
-where t.i in (select distinct i from t)
-and t.s in (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-2          |1     
-ij> -- . select distinct under a union all/ over a union all
--- expect 2 rows of any value
-select distinct i from t
-union all
-select distinct i from t;
-I          
------------
-2          
-1          
-2          
-1          
-ij> -- at most 1 row of any value
-select distinct * from (select i from t union all select i from t) as s;
-I          
------------
-1          
-2          
-ij> -- . select distinct over a from subquery (itself distinct/not)
-select distinct * from (select t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> -- . select distinct over a join
-select distinct * from t t1, t t2 where t1.i = t2.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . variety of rows, all different
-delete from t;
-3 rows inserted/updated/deleted
-ij> insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'hello', 'planet');
-1 row inserted/updated/deleted
-ij> insert into t values (2, 1, 4, 3, '1992-01-01', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'hello', 'planet');
-1 row inserted/updated/deleted
-ij> run resource 'distinct.subsql';
-ij> --
--- this is the script we run over various data flavors of the following
--- table.  distinct.sql uses this script repeatedly.
---
--- create table t (i int, s smallint, r real, f float, 
---  d date, t time, ts timestamp, c char(10), v varchar(20));
--- here are the flavors of select:
--- . select distinct one column, each data type (include usertypes)
-select distinct i from t;
-I          
------------
-2          
-1          
-ij> select distinct s from t;
-S     
-------
-2     
-1     
-ij> select distinct r from t;
-R            
--------------
-4.0          
-3.0          
-ij> select distinct f from t;
-F                     
-----------------------
-4.0                   
-3.0                   
-ij> select distinct d from t;
-D         
-----------
-1992-01-01
-ij> select distinct t from t;
-T       
---------
-19:01:01
-ij> select distinct ts from t;
-TS                        
---------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select distinct c from t;
-C         
-----------
-hello     
-ij> select distinct v from t;
-V                   
---------------------
-planet              
-ij> -- . select distinct multiple columns, each data type
--- . select distinct all or just some columns of the table
-select distinct t,i,s,f,d from t;
-T       |I          |S     |F                     |D         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select distinct * from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select distinct t.*,ts from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TS                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in an exists subquery
-select * from t where exists (select distinct i from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where exists (select distinct * from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where not exists (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an in subquery
-select * from t where i in (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where s not in (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a quantified subquery
--- same result as i in distinct s above
-select * from t where i =any (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- same result as s not in distinct r above
-select * from t where s <>any (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where d >=any (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from t where t <=all (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . select distinct in a scalar subquery
-select * from t where c = (select distinct v from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where v < (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
-ij> -- . select distinct in a from subquery
-select * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select * from (select distinct * from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from (select distinct t.*,ts as tts from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in a from subquery joining with another table
-select * from t, (select distinct t.*,ts as tts from t) as s where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select * from (select distinct t.*,ts as tts from t) as s, t where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx|1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . multiple select distincts -- outer & sqs, just sqs, outer & from(s)
-select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select i, s from t as touter
-where touter.i in (select distinct i from t)
-and exists (select distinct s from t as ti where touter.s=ti.s);
-I          |S     
-------------------
-1          |2     
-2          |1     
-ij> -- same result as exists above
-select i, s from t as touter
-where touter.i in (select distinct i from t)
-and touter.s =any (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-2          |1     
-ij> select distinct i, s from t
-where t.i in (select distinct i from t)
-and t.s in (select distinct s from t);
-I          |S     
-------------------
-1          |2     
-2          |1     
-ij> -- . select distinct under a union all/ over a union all
--- expect 2 rows of any value
-select distinct i from t
-union all
-select distinct i from t;
-I          
------------
-2          
-1          
-2          
-1          
-ij> -- at most 1 row of any value
-select distinct * from (select i from t union all select i from t) as s;
-I          
------------
-1          
-2          
-ij> -- . select distinct over a from subquery (itself distinct/not)
-select distinct * from (select t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> select distinct * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |2     |4.0                   |1992-01-01
-19:01:01|2          |1     |3.0                   |1992-01-01
-ij> -- . select distinct over a join
-select distinct * from t t1, t t2 where t1.i = t2.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |2          |1     |4.0          |3.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> -- . variety of rows, some same in some columns but not others
-delete from t;
-2 rows inserted/updated/deleted
-ij> insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'hello', 'planet');
-1 row inserted/updated/deleted
-ij> insert into t values (1, 1, 3, 4, '1992-01-02', '19:01:01',
-'xxxxxxFILTERED-TIMESTAMPxxxxx', 'goodbye', 'planet');
-1 row inserted/updated/deleted
-ij> run resource 'distinct.subsql';
-ij> --
--- this is the script we run over various data flavors of the following
--- table.  distinct.sql uses this script repeatedly.
---
--- create table t (i int, s smallint, r real, f float, 
---  d date, t time, ts timestamp, c char(10), v varchar(20));
--- here are the flavors of select:
--- . select distinct one column, each data type (include usertypes)
-select distinct i from t;
-I          
------------
-1          
-ij> select distinct s from t;
-S     
-------
-2     
-1     
-ij> select distinct r from t;
-R            
--------------
-3.0          
-ij> select distinct f from t;
-F                     
-----------------------
-4.0                   
-ij> select distinct d from t;
-D         
-----------
-1992-01-02
-1992-01-01
-ij> select distinct t from t;
-T       
---------
-19:01:01
-ij> select distinct ts from t;
-TS                        
---------------------------
-xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> select distinct c from t;
-C         
-----------
-goodbye   
-hello     
-ij> select distinct v from t;
-V                   
---------------------
-planet              
-ij> -- . select distinct multiple columns, each data type
--- . select distinct all or just some columns of the table
-select distinct t,i,s,f,d from t;
-T       |I          |S     |F                     |D         
--------------------------------------------------------------
-19:01:01|1          |1     |4.0                   |1992-01-02
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select distinct * from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select distinct t.*,ts from t;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TS                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in an exists subquery
-select * from t where exists (select distinct i from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> select * from t where exists (select distinct * from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> select * from t where not exists (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> -- . select distinct in an in subquery
-select * from t where i in (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> select * from t where s not in (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> -- . select distinct in a quantified subquery
--- same result as i in distinct s above
-select * from t where i =any (select distinct s from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> -- same result as s not in distinct r above
-select * from t where s <>any (select distinct r from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> select * from t where d >=any (select distinct d from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> select * from t where t <=all (select distinct t from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-ij> -- . select distinct in a scalar subquery
-select * from t where c = (select distinct v from t);
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-ij> select * from t where v < (select distinct d from t);
-ERROR 21000: Scalar subquery is only allowed to return a single row.
-ij> -- . select distinct in a from subquery
-select * from (select distinct t,i,s,f,d from t) as s(a,b,c,d,e);
-A       |B          |C     |D                     |E         
--------------------------------------------------------------
-19:01:01|1          |1     |4.0                   |1992-01-02
-19:01:01|1          |2     |4.0                   |1992-01-01
-ij> select * from (select distinct * from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   
---------------------------------------------------------------------------------------------------------------------------------------
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              
-ij> select * from (select distinct t.*,ts as tts from t) as s;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-ij> -- . select distinct in a from subquery joining with another table
-select * from t, (select distinct t.*,ts as tts from t) as s where t.i=s.i;
-I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |I          |S     |R            |F                     |D         |T       |TS                        |C         |V                   |TTS                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |1     |3.0          |4.0                   |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye   |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx
-1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |1          |2     |3.0          |4.0                   |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello     |planet              |xxxxxxFILTERED-TIMESTAMPxxxxx

[... 4445 lines stripped ...]


Mime
View raw message