ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- 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 in this type of statement. ij> select distinct d from u for update of d; ERROR 42Y90: FOR UPDATE is not permitted in 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> create table td (x int); 0 rows inserted/updated/deleted ij> insert into td values (1); 1 row inserted/updated/deleted ij> insert into td values (1); 1 row inserted/updated/deleted ij> insert into td values (2); 1 row inserted/updated/deleted ij> -- distinct in subquery where the store does not perform the sort. select * from td, (select distinct 1 from td) as sub(x); X |X ----------------------- 1 |1 1 |1 2 |1 ij> -- get the storage system to do the sort. select * from td, (select distinct x from td) as sub(x); X |X ----------------------- 1 |2 1 |1 1 |2 1 |1 2 |2 2 |1 ij> -- Tests for DERBY-504 (select distinct from a subquery) create table names (id int, name varchar(10), age int); 0 rows inserted/updated/deleted ij> insert into names (id, name, age) values (1, 'Anna', 23), (2, 'Ben', 24), (3, 'Carl', 25), (4, 'Anna', 23), (5, 'Ben', 24), (6, 'Carl', 25); 6 rows inserted/updated/deleted ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 20000; ij> -- distinct names should be returned select distinct name from (select name, id from names) as n; NAME ---------- Anna Ben Carl ij> -- runtime statistics should not have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- distinct names should be returned select distinct name from (select name, id from names) as n Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 6 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=6 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 6 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=6 Number of rows visited=6 Scan type=heap start position: null stop position: null qualifiers: None ij> -- distinct names should be returned select distinct name from (select name from names) as n; NAME ---------- Anna Ben Carl ij> -- runtime statistics should have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- distinct names should be returned select distinct name from (select name from names) as n Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Distinct Scan ResultSet for NAMES at read committed isolation level using instantaneous share row locking: Number of opens = 1 Hash table size = 3 Distinct column is column number 1 Rows seen = 3 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={1} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=6 Number of rows visited=6 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m; A |B |B |A --------------------------------------------- Anna |1 |1 |Anna Carl |6 |6 |Carl Ben |5 |5 |Ben Carl |3 |3 |Carl Ben |2 |2 |Ben Anna |4 |4 |Anna ij> -- runtime statistics should have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct a, b, b, a from (select y as a, x as b from (select id as x, name as y from names) as n) as m Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (2): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Distinct Scan ResultSet for NAMES at read committed isolation level using instantaneous share row locking: Number of opens = 1 Hash table size = 6 Distinct columns are column numbers (0,1) Rows seen = 6 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=6 Number of rows visited=6 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: None ij> select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m; A |A --------------------- Anna |Anna Ben |Ben Carl |Carl ij> -- runtime statistics should not have Distinct Scan in it values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct a, a from (select y as a from (select id as x, name as y from names) as n) as m Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Sort ResultSet: Number of opens = 1 Rows input = 6 Rows returned = 3 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=6 Number of rows output=3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for NAMES at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 6 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=6 Number of rows visited=6 Scan type=heap start position: null stop position: null qualifiers: None ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 0 rows inserted/updated/deleted ij> drop table names; 0 rows inserted/updated/deleted ij> -- bug 578. declare global temporary table session.ztemp ( orderID varchar( 50 ) ) not logged; 0 rows inserted/updated/deleted ij> select orderID from session.ztemp group by orderID; ORDERID -------------------------------------------------- ij> -- reset autocommit autocommit on; ij>