You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by fu...@apache.org on 2005/06/13 23:41:35 UTC
svn commit: r190514 [5/7] - in /incubator/derby/code/branches/10.1/java:
engine/org/apache/derby/iapi/reference/
engine/org/apache/derby/iapi/services/io/
engine/org/apache/derby/iapi/services/loader/
engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/jdbc/
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/
testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/
testing/org/apache/derbyTesting/functionTests/master/j9_13/
testing/org/apache/derbyTesting/functionTests/master/j9_22/
testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/functionTests/tests/tools/
Modified: incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out?rev=190514&r1=190513&r2=190514&view=diff
==============================================================================
--- incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out (original)
+++ incubator/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out Mon Jun 13 14:41:33 2005
@@ -1,2482 +1,2482 @@
-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
-1 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet |1 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet |xxxxxxFILTERED-TIMESTAMPxxxxx
-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 |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
[... 3827 lines stripped ...]