|
||||
0001 -- 0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0003 -- 0004 -- 0005 -- SELECT_DISTINCT 0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/select_distinct.sql 0007 -- 0008 0009 CREATE OR REPLACE TEMPORARY VIEW tmp AS 0010 SELECT two, stringu1, ten, string4 0011 FROM onek; 0012 0013 -- 0014 -- awk '{print $3;}' onek.data | sort -n | uniq 0015 -- 0016 SELECT DISTINCT two FROM tmp ORDER BY 1; 0017 0018 -- 0019 -- awk '{print $5;}' onek.data | sort -n | uniq 0020 -- 0021 SELECT DISTINCT ten FROM tmp ORDER BY 1; 0022 0023 -- 0024 -- awk '{print $16;}' onek.data | sort -d | uniq 0025 -- 0026 SELECT DISTINCT string4 FROM tmp ORDER BY 1; 0027 0028 -- [SPARK-28010] Support ORDER BY ... USING syntax 0029 -- 0030 -- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | 0031 -- sort +0n -1 +1d -2 +2n -3 0032 -- 0033 -- SELECT DISTINCT two, string4, ten 0034 -- FROM tmp 0035 -- ORDER BY two using <, string4 using <, ten using <; 0036 SELECT DISTINCT two, string4, ten 0037 FROM tmp 0038 ORDER BY two ASC, string4 ASC, ten ASC; 0039 0040 -- Skip the person table because there is a point data type that we don't support. 0041 -- 0042 -- awk '{print $2;}' person.data | 0043 -- awk '{if(NF!=1){print $2;}else{print;}}' - emp.data | 0044 -- awk '{if(NF!=1){print $2;}else{print;}}' - student.data | 0045 -- awk 'BEGIN{FS=" ";}{if(NF!=1){print $5;}else{print;}}' - stud_emp.data | 0046 -- sort -n -r | uniq 0047 -- 0048 -- SELECT DISTINCT p.age FROM person* p ORDER BY age using >; 0049 0050 -- 0051 -- Check mentioning same column more than once 0052 -- 0053 0054 -- EXPLAIN (VERBOSE, COSTS OFF) 0055 -- SELECT count(*) FROM 0056 -- (SELECT DISTINCT two, four, two FROM tenk1) ss; 0057 0058 SELECT count(*) FROM 0059 (SELECT DISTINCT two, four, two FROM tenk1) ss; 0060 0061 -- 0062 -- Also, some tests of IS DISTINCT FROM, which doesn't quite deserve its 0063 -- very own regression file. 0064 -- 0065 0066 CREATE OR REPLACE TEMPORARY VIEW disttable AS SELECT * FROM 0067 (VALUES (1), (2), (3), (NULL)) 0068 AS v(f1); 0069 0070 -- basic cases 0071 SELECT f1, f1 IS DISTINCT FROM 2 as `not 2` FROM disttable; 0072 SELECT f1, f1 IS DISTINCT FROM NULL as `not null` FROM disttable; 0073 SELECT f1, f1 IS DISTINCT FROM f1 as `false` FROM disttable; 0074 SELECT f1, f1 IS DISTINCT FROM f1+1 as `not null` FROM disttable; 0075 0076 -- check that optimizer constant-folds it properly 0077 SELECT 1 IS DISTINCT FROM 2 as `yes`; 0078 SELECT 2 IS DISTINCT FROM 2 as `no`; 0079 SELECT 2 IS DISTINCT FROM null as `yes`; 0080 SELECT null IS DISTINCT FROM null as `no`; 0081 0082 -- negated form 0083 SELECT 1 IS NOT DISTINCT FROM 2 as `no`; 0084 SELECT 2 IS NOT DISTINCT FROM 2 as `yes`; 0085 SELECT 2 IS NOT DISTINCT FROM null as `no`; 0086 SELECT null IS NOT DISTINCT FROM null as `yes`;
[ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |