0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011
0012
0013 create temporary view int4_tbl as select * from values
0014 (0),
0015 (123456),
0016 (-123456),
0017 (2147483647),
0018 (-2147483647)
0019 as int4_tbl(f1);
0020
0021
0022
0023
0024
0025
0026
0027
0028
0029
0030
0031
0032
0033
0034
0035
0036
0037
0038
0039
0040
0041
0042
0043
0044
0045
0046
0047
0048
0049 CREATE OR REPLACE TEMPORARY VIEW bitwise_test AS SELECT * FROM VALUES
0050 (1, 1, 1, 1L),
0051 (3, 3, 3, null),
0052 (7, 7, 7, 3L) AS bitwise_test(b1, b2, b3, b4);
0053
0054
0055 SELECT BIT_AND(b1) AS n1, BIT_OR(b2) AS n2 FROM bitwise_test where 1 = 0;
0056
0057
0058 SELECT BIT_AND(b4) AS n1, BIT_OR(b4) AS n2 FROM bitwise_test where b4 is null;
0059
0060 SELECT
0061 BIT_AND(cast(b1 as tinyint)) AS a1,
0062 BIT_AND(cast(b2 as smallint)) AS b1,
0063 BIT_AND(b3) AS c1,
0064 BIT_AND(b4) AS d1,
0065 BIT_OR(cast(b1 as tinyint)) AS e7,
0066 BIT_OR(cast(b2 as smallint)) AS f7,
0067 BIT_OR(b3) AS g7,
0068 BIT_OR(b4) AS h3
0069 FROM bitwise_test;
0070
0071
0072 SELECT b1 , bit_and(b2), bit_or(b4) FROM bitwise_test GROUP BY b1;
0073
0074
0075 SELECT b1, bit_and(b2) FROM bitwise_test GROUP BY b1 HAVING bit_and(b2) < 7;
0076
0077
0078 SELECT b1, b2, bit_and(b2) OVER (PARTITION BY b1 ORDER BY b2) FROM bitwise_test;
0079 SELECT b1, b2, bit_or(b2) OVER (PARTITION BY b1 ORDER BY b2) FROM bitwise_test;
0080
0081
0082
0083
0084
0085
0086
0087 SELECT
0088
0089
0090 (NULL AND NULL) IS NULL AS `t`,
0091 (TRUE AND NULL) IS NULL AS `t`,
0092 (FALSE AND NULL) IS NULL AS `t`,
0093 (NULL AND TRUE) IS NULL AS `t`,
0094 (NULL AND FALSE) IS NULL AS `t`,
0095
0096 (TRUE AND TRUE) AS `t`,
0097 NOT (TRUE AND FALSE) AS `t`,
0098 NOT (FALSE AND TRUE) AS `t`,
0099 NOT (FALSE AND FALSE) AS `t`;
0100
0101
0102 SELECT
0103
0104
0105 (NULL OR NULL) IS NULL AS `t`,
0106 (TRUE OR NULL) IS NULL AS `t`,
0107 (FALSE OR NULL) IS NULL AS `t`,
0108 (NULL OR TRUE) IS NULL AS `t`,
0109 (NULL OR FALSE) IS NULL AS `t`,
0110
0111 (TRUE OR TRUE) AS `t`,
0112 (TRUE OR FALSE) AS `t`,
0113 (FALSE OR TRUE) AS `t`,
0114 NOT (FALSE OR FALSE) AS `t`;
0115
0116
0117 CREATE OR REPLACE TEMPORARY VIEW bool_test AS SELECT * FROM VALUES
0118 (TRUE, null, FALSE, null),
0119 (FALSE, TRUE, null, null),
0120 (null, TRUE, FALSE, null) AS bool_test(b1, b2, b3, b4);
0121
0122
0123 SELECT BOOL_AND(b1) AS n1, BOOL_OR(b3) AS n2 FROM bool_test WHERE 1 = 0;
0124
0125 SELECT
0126 BOOL_AND(b1) AS f1,
0127 BOOL_AND(b2) AS t2,
0128 BOOL_AND(b3) AS f3,
0129 BOOL_AND(b4) AS n4,
0130 BOOL_AND(NOT b2) AS f5,
0131 BOOL_AND(NOT b3) AS t6
0132 FROM bool_test;
0133
0134 SELECT
0135 EVERY(b1) AS f1,
0136 EVERY(b2) AS t2,
0137 EVERY(b3) AS f3,
0138 EVERY(b4) AS n4,
0139 EVERY(NOT b2) AS f5,
0140 EVERY(NOT b3) AS t6
0141 FROM bool_test;
0142
0143 SELECT
0144 BOOL_OR(b1) AS t1,
0145 BOOL_OR(b2) AS t2,
0146 BOOL_OR(b3) AS f3,
0147 BOOL_OR(b4) AS n4,
0148 BOOL_OR(NOT b2) AS f5,
0149 BOOL_OR(NOT b3) AS t6
0150 FROM bool_test;
0151
0152
0153
0154
0155
0156
0157
0158
0159
0160 select min(unique1) from tenk1;
0161
0162
0163 select max(unique1) from tenk1;
0164
0165
0166 select max(unique1) from tenk1 where unique1 < 42;
0167
0168
0169 select max(unique1) from tenk1 where unique1 > 42;
0170
0171
0172
0173
0174
0175
0176
0177
0178
0179 select max(unique1) from tenk1 where unique1 > 42000;
0180
0181
0182
0183
0184
0185 select max(tenthous) from tenk1 where thousand = 33;
0186
0187
0188 select min(tenthous) from tenk1 where thousand = 33;
0189
0190
0191
0192
0193
0194
0195
0196
0197
0198
0199
0200
0201 select distinct max(unique2) from tenk1;
0202
0203
0204 select max(unique2) from tenk1 order by 1;
0205
0206
0207 select max(unique2) from tenk1 order by max(unique2);
0208
0209
0210 select max(unique2) from tenk1 order by max(unique2)+1;
0211
0212
0213 select t1.max_unique2, g from (select max(unique2) as max_unique2 FROM tenk1) t1 LATERAL VIEW explode(array(1,2,3)) t2 AS g order by g desc;
0214
0215
0216
0217
0218 select max(100) from tenk1;