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 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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 |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|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 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 |1 |4.0 |1992-01-02 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 |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 |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 |1 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 |1 |4.0 |1992-01-02 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 |1 |4.0 |1992-01-02 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 |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 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 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 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> -- . just nulls delete from t; 2 rows inserted/updated/deleted ij> -- all the defaults are null, so just get a row in easily insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 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 ----------- NULL ij> select distinct s from t; S ------ NULL ij> select distinct r from t; R ------------- NULL ij> select distinct f from t; F ---------------------- NULL ij> select distinct d from t; D ---------- NULL ij> select distinct t from t; T -------- NULL ij> select distinct ts from t; TS -------------------------- NULL ij> select distinct c from t; C ---------- NULL ij> select distinct v from t; V -------------------- NULL 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 ------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL ij> select distinct * from t; I |S |R |F |D |T |TS |C |V -------------------------------------------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select distinct t.*,ts from t; I |S |R |F |D |T |TS |C |V |TS ----------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 -------------------------------------------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from t where exists (select distinct * from t); I |S |R |F |D |T |TS |C |V -------------------------------------------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 ------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL ij> select * from (select distinct * from t) as s; I |S |R |F |D |T |TS |C |V -------------------------------------------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from (select distinct t.*,ts as tts from t) as s; I |S |R |F |D |T |TS |C |V |TTS ----------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 ------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 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 ----------- NULL NULL ij> -- at most 1 row of any value select distinct * from (select i from t union all select i from t) as s; I ----------- NULL 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 ------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 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 ------------------------------------------------------------- NULL |NULL |NULL |NULL |NULL 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 null in the mix 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> insert into t (i) values (null); 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 NULL ij> select distinct s from t; S ------ 2 1 NULL ij> select distinct r from t; R ------------- 3.0 NULL ij> select distinct f from t; F ---------------------- 4.0 NULL ij> select distinct d from t; D ---------- 1992-01-02 1992-01-01 NULL ij> select distinct t from t; T -------- NULL 19:01:01 ij> select distinct ts from t; TS -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx NULL ij> select distinct c from t; C ---------- goodbye hello NULL ij> select distinct v from t; V -------------------- planet NULL 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 NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 -------------------------------------------------------------------------------------------------------------------------------------- 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 -------------------------------------------------------------------------------------------------------------------------------------- ij> -- . select distinct in a scalar subquery select * from t where c = (select distinct v from t); ERROR 21000: Scalar subquery is only allowed to return a single row. 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 NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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 |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|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 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 NULL |NULL |NULL |NULL |NULL 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 |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 |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 |1 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 NULL 1 NULL 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 NULL 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 NULL |NULL |NULL |NULL |NULL 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 NULL |NULL |NULL |NULL |NULL 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 |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 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 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 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> -- . several nulls in the mix insert into t (i) values (null); 1 row inserted/updated/deleted ij> insert into t (i) values (null); 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 NULL ij> select distinct s from t; S ------ 2 1 NULL ij> select distinct r from t; R ------------- 3.0 NULL ij> select distinct f from t; F ---------------------- 4.0 NULL ij> select distinct d from t; D ---------- 1992-01-02 1992-01-01 NULL ij> select distinct t from t; T -------- NULL 19:01:01 ij> select distinct ts from t; TS -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx NULL ij> select distinct c from t; C ---------- goodbye hello NULL ij> select distinct v from t; V -------------------- planet NULL 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 NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 -------------------------------------------------------------------------------------------------------------------------------------- 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 -------------------------------------------------------------------------------------------------------------------------------------- ij> -- . select distinct in a scalar subquery select * from t where c = (select distinct v from t); ERROR 21000: Scalar subquery is only allowed to return a single row. 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 NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 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 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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 |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|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 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 NULL |NULL |NULL |NULL |NULL 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 |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 |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 |1 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 NULL 1 NULL 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 NULL 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 NULL |NULL |NULL |NULL |NULL 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 NULL |NULL |NULL |NULL |NULL 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 |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 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 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 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> -- . nulls in some columns, not others delete from t where i is null; 3 rows inserted/updated/deleted ij> insert into t values (null, 1, null, 4, null, '19:01:01', null, 'goodbye', null); 1 row inserted/updated/deleted ij> insert into t values (1, null, 3, null, '1992-01-02', null, 'xxxxxxFILTERED-TIMESTAMPxxxxx', null, '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 NULL ij> select distinct s from t; S ------ 2 1 NULL ij> select distinct r from t; R ------------- 3.0 NULL ij> select distinct f from t; F ---------------------- 4.0 NULL ij> select distinct d from t; D ---------- 1992-01-02 1992-01-01 NULL ij> select distinct t from t; T -------- NULL 19:01:01 ij> select distinct ts from t; TS -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx NULL ij> select distinct c from t; C ---------- goodbye hello NULL ij> select distinct v from t; V -------------------- planet NULL 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 19:01:01|NULL |1 |4.0 |NULL NULL |1 |NULL |NULL |1992-01-02 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 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL 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 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |xxxxxxFILTERED-TIMESTAMPxxxxx NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL |NULL 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 NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet 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 -------------------------------------------------------------------------------------------------------------------------------------- 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 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL 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 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet 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); ERROR 21000: Scalar subquery is only allowed to return a single row. 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 19:01:01|NULL |1 |4.0 |NULL NULL |1 |NULL |NULL |1992-01-02 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 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL 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 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |xxxxxxFILTERED-TIMESTAMPxxxxx NULL |1 |NULL |4.0 |NULL |19:01:01|NULL |goodbye |NULL |NULL 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 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |1 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet |xxxxxxFILTERED-TIMESTAMPxxxxx 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |xxxxxxFILTERED-TIMESTAMPxxxxx 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 |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 |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 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet |xxxxxxFILTERED-TIMESTAMPxxxxx|1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |xxxxxxFILTERED-TIMESTAMPxxxxx|1 |2 |3.0 |4.0 |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello |planet 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |xxxxxxFILTERED-TIMESTAMPxxxxx|1 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |xxxxxxFILTERED-TIMESTAMPxxxxx|1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 |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|1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 19:01:01|NULL |1 |4.0 |NULL NULL |1 |NULL |NULL |1992-01-02 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 |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 |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 |1 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 NULL 1 NULL 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 NULL 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 19:01:01|NULL |1 |4.0 |NULL NULL |1 |NULL |NULL |1992-01-02 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 |1 |4.0 |1992-01-02 19:01:01|1 |2 |4.0 |1992-01-01 19:01:01|NULL |1 |4.0 |NULL NULL |1 |NULL |NULL |1992-01-02 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 |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 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 1 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet |1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet 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 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 1 |2 |3.0 |4.0 |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello |planet |1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |1 |1 |3.0 |4.0 |1992-01-02|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|goodbye |planet 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |1 |2 |3.0 |4.0 |1992-01-01|19:01:01|xxxxxxFILTERED-TIMESTAMPxxxxx|hello |planet 1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet |1 |NULL |3.0 |NULL |1992-01-02|NULL |xxxxxxFILTERED-TIMESTAMPxxxxx|NULL |planet ij> drop table t; 0 rows inserted/updated/deleted ij> rollback; ij> -- sure would like to dump all those selects now...they are invalid, at least, -- maybe that frees up some space (FLW) -- here are other things to test: -- . select distinct over a values table -- three rows select distinct * from (values (1,2),(1,3),(1,2),(2,3)) as t(a,b); A |B ----------------------- 1 |2 1 |3 2 |3 ij> -- two rows select distinct a from (values (1,2),(1,3),(1,2),(2,3)) as t(a,b); A ----------- 1 2 ij> -- . show that distinct is a keyword, not a column name select distinct from t; ERROR 42X01: Syntax error: Encountered "from" at line 2, column 17. ij> rollback; ij> -- . usertypes -- To test usertypes in a way that works with weblogic, we -- pick one we can construct with other functionality available to us -- (UUID won't work) -- At the time these tests were written, user type comparisons -- were not supported. create table userInt (u integer); 0 rows inserted/updated/deleted ij> insert into userInt values (123); 1 row inserted/updated/deleted ij> insert into userInt values (123); 1 row inserted/updated/deleted ij> insert into userInt values (456); 1 row inserted/updated/deleted ij> insert into userInt values (null); 1 row inserted/updated/deleted ij> create table sqlInt (i int not null); 0 rows inserted/updated/deleted ij> insert into sqlInt values(123); 1 row inserted/updated/deleted ij> -- expect two rows, 123 and 456 select distinct u from userInt where u is not null; U ----------- 123 456 ij> -- two rows, 123 and 456 select u from userInt where u is not null; U ----------- 123 123 456 ij> -- multiple rows in subquery get correct complaint select distinct i from sqlInt where i = (select distinct u from userInt); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> drop table userInt; 0 rows inserted/updated/deleted ij> drop table sqlInt; 0 rows inserted/updated/deleted ij> rollback; ij> -- . varchar blank padding is ignored, length will vary depending on row selected create table v (v varchar(40)); 0 rows inserted/updated/deleted ij> insert into v values ('hello'); 1 row inserted/updated/deleted ij> insert into v values ('hello '); 1 row inserted/updated/deleted ij> insert into v values ('hello '); 1 row inserted/updated/deleted ij> -- the |'s are just for visual demarcation select distinct '|' as "|", v, '|' as "|" from v; ||V || -------------------------------------------- ||hello || ij> select {fn length(c)} from (select distinct v from v) as t(c); 1 ----------- 5 ij> drop table v; 0 rows inserted/updated/deleted ij> rollback; ij> -- distinct bigint create table li (l bigint, i int); 0 rows inserted/updated/deleted ij> insert into li values(1, 1); 1 row inserted/updated/deleted ij> insert into li values(1, 1); 1 row inserted/updated/deleted ij> insert into li values(9223372036854775807, 2147483647); 1 row inserted/updated/deleted ij> select distinct l from li; L -------------------- 1 9223372036854775807 ij> (select distinct l from li) union all (select distinct i from li) order by 1; 1 -------------------- 1 1 2147483647 9223372036854775807 ij> select distinct l from li union select distinct i from li; 1 -------------------- 1 2147483647 9223372036854775807 ij> select distinct l from (select l from li union all select i from li) a(l); L -------------------- 1 2147483647 9223372036854775807 ij> drop table li; 0 rows inserted/updated/deleted ij> rollback; ij> autocommit off; ij> -- was off above, ensure it stayed off for this part of test create table u (d date); 0 rows inserted/updated/deleted ij> -- three rows insert into u values ('1997-09-09'),('1997-09-09'); 2 rows inserted/updated/deleted ij> insert into u values (null); 1 row inserted/updated/deleted ij> -- . nexting past the last row of a distinct get cursor past as 'select distinct d from u'; ij> next past; D ---------- 1997-09-09 ij> next past; D ---------- NULL ij> -- should report no current row: next past; No current row ij> next past; No current row ij> close past; ij> -- . for update on a select distinct -- both should get errors, not updatable. select distinct d from u for update; ERROR 42Y90: FOR UPDATE is not permitted on this type of statement. ij> select distinct d from u for update of d; ERROR 42Y90: FOR UPDATE is not permitted on this type of statement. ij> -- . positioned update/delete on a select distinct get cursor c1 as 'select distinct d from u'; ij> next c1; D ---------- 1997-09-09 ij> -- both should fail with cursor not updatable update u set d='1992-01-01' where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> delete from u where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> close c1; ij> get cursor c1 as 'select distinct d from u'; ij> -- both should fail with cursor not updatable (not no current row) update u set d='1992-01-01' where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> delete from u where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> next c1; D ---------- 1997-09-09 ij> next c1; D ---------- NULL ij> next c1; No current row ij> next c1; No current row ij> -- both should fail with cursor not updatable, or cursor closed/does not exist update u set d='1992-01-01' where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> delete from u where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> close c1; ij> get cursor c1 as 'select distinct d from u'; ij> close c1; ij> -- both should fail with cursor not updatable, or cursor closed/does not exist update u set d='1992-01-01' where current of c1; ERROR 42X30: Cursor 'C1' not found. Verify that autocommit is OFF. ij> delete from u where current of c1; ERROR 42X30: Cursor 'C1' not found. Verify that autocommit is OFF. ij> drop table u; 0 rows inserted/updated/deleted ij> rollback; ij> -- insert tests 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> create table insert_test (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> -- populate the tables 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, 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> insert into insert_test select distinct * from t; 2 rows inserted/updated/deleted ij> select * from insert_test; 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> delete from insert_test; 2 rows inserted/updated/deleted ij> insert into insert_test select distinct * from t union select * from t; 2 rows inserted/updated/deleted ij> select * from insert_test; 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> delete from insert_test; 2 rows inserted/updated/deleted ij> rollback; ij> -- for bug 4194, "insert into select distinct" into a table with a generated column create table destWithAI(c11 int generated always as identity, c12 int); 0 rows inserted/updated/deleted ij> alter table destWithAI alter c11 set increment by 1; 0 rows inserted/updated/deleted ij> create table destWithNoAI(c21 int, c22 int); 0 rows inserted/updated/deleted ij> create table source(c31 int, c32 int, c33 int); 0 rows inserted/updated/deleted ij> insert into source values(1,1,1); 1 row inserted/updated/deleted ij> insert into source values(1,2,1); 1 row inserted/updated/deleted ij> insert into source values(2,1,1); 1 row inserted/updated/deleted ij> insert into source values(2,2,1); 1 row inserted/updated/deleted ij> select distinct(c31) from source; C31 ----------- 2 1 ij> insert into destWithAI(c12) select distinct(c31) from source; 2 rows inserted/updated/deleted ij> -- we will see gaps in the autoincrement column for all the duplicate rows from source select * from destWithAI; C11 |C12 ----------------------- 1 |1 3 |2 ij> insert into destWithNoAI(c22) select distinct(c31) from source; 2 rows inserted/updated/deleted ij> select * from destWithNoAI; C21 |C22 ----------------------- NULL |1 NULL |2 ij> -- test for beetle 4402 -- problem with check that a result set is in order since it is retrieved using -- an index CREATE TABLE netbutton1 ( lname varchar(128) not null, name varchar(128), summary varchar(256), lsummary varchar(256), description varchar(2000), ldescription varchar(2000), publisher_username varchar(256), publisher_lusername varchar(256), version varchar(16), source long varchar for bit data, updated timestamp, created timestamp DEFAULT current_timestamp, primary key (lname)) ; 0 rows inserted/updated/deleted ij> insert into netbutton1 values('lname1','name1','sum2','lsum1', 'des1','ldes1','pubu1', 'publu1', 'ver1', null, current_timestamp, default); 1 row inserted/updated/deleted ij> insert into netbutton1 values('lname2','name2','sum2','lsum2', 'des2','ldes2','pubu2', 'publu2', 'ver2', null, current_timestamp, default); 1 row inserted/updated/deleted ij> CREATE TABLE library_netbutton ( netbuttonlibrary_id int not null, lname varchar(128) not null, primary key (netbuttonlibrary_id, lname)) ; 0 rows inserted/updated/deleted ij> insert into library_netbutton values(1, 'lname1'); 1 row inserted/updated/deleted ij> insert into library_netbutton values(2, 'lname2'); 1 row inserted/updated/deleted ij> -- this is the index which causes the bug to be exposed create unique index ln_library_id on library_netbutton(netbuttonlibrary_id); 0 rows inserted/updated/deleted ij> ALTER TABLE library_netbutton ADD CONSTRAINT ln_lname_fk FOREIGN KEY (lname) REFERENCES netbutton1(lname) ; 0 rows inserted/updated/deleted ij> CREATE TABLE netbuttonlibraryrole1 ( lusername varchar(512) not null, netbuttonlibrary_id int not null, username varchar(512), role varchar(24), created timestamp DEFAULT current_timestamp, primary key (lusername, netbuttonlibrary_id)) ; 0 rows inserted/updated/deleted ij> insert into netbuttonlibraryrole1 values('lusername1', 1,'user1', 'role1', default); 1 row inserted/updated/deleted ij> insert into netbuttonlibraryrole1 values('lusername2', 2,'user2', 'role2', default); 1 row inserted/updated/deleted ij> autocommit off; ij> prepare c1 as 'SELECT DISTINCT nb.name AS name, nb.summary AS summary FROM netbutton1 nb, netbuttonlibraryrole1 nlr, library_netbutton ln WHERE nlr.netbuttonlibrary_id = ln.netbuttonlibrary_id AND nb.lname = ln.lname AND ( nlr.lusername = ? OR nlr.lusername = ?) AND nb.lname = ? ORDER BY summary'; ij> execute c1 using 'values(''lusername1'', ''lusername2'', ''lname1'')'; NAME |SUMMARY ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- name1 |sum2 ij> rollback; ij> -- reset autocomiit autocommit on; ij>