typeCoercion/native/binaryComparison.sql

0001 --
0002 --   Licensed to the Apache Software Foundation (ASF) under one or more
0003 --   contributor license agreements.  See the NOTICE file distributed with
0004 --   this work for additional information regarding copyright ownership.
0005 --   The ASF licenses this file to You under the Apache License, Version 2.0
0006 --   (the "License"); you may not use this file except in compliance with
0007 --   the License.  You may obtain a copy of the License at
0008 --
0009 --      http://www.apache.org/licenses/LICENSE-2.0
0010 --
0011 --   Unless required by applicable law or agreed to in writing, software
0012 --   distributed under the License is distributed on an "AS IS" BASIS,
0013 --   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0014 --   See the License for the specific language governing permissions and
0015 --   limitations under the License.
0016 --
0017
0018 -- Binary Comparison
0019
0020 CREATE TEMPORARY VIEW t AS SELECT 1;
0021
0022 SELECT cast(1 as binary) = '1' FROM t;
0023 SELECT cast(1 as binary) > '2' FROM t;
0024 SELECT cast(1 as binary) >= '2' FROM t;
0025 SELECT cast(1 as binary) < '2' FROM t;
0026 SELECT cast(1 as binary) <= '2' FROM t;
0027 SELECT cast(1 as binary) <> '2' FROM t;
0028 SELECT cast(1 as binary) = cast(null as string) FROM t;
0029 SELECT cast(1 as binary) > cast(null as string) FROM t;
0030 SELECT cast(1 as binary) >= cast(null as string) FROM t;
0031 SELECT cast(1 as binary) < cast(null as string) FROM t;
0032 SELECT cast(1 as binary) <= cast(null as string) FROM t;
0033 SELECT cast(1 as binary) <> cast(null as string) FROM t;
0034 SELECT '1' = cast(1 as binary) FROM t;
0035 SELECT '2' > cast(1 as binary) FROM t;
0036 SELECT '2' >= cast(1 as binary) FROM t;
0037 SELECT '2' < cast(1 as binary) FROM t;
0038 SELECT '2' <= cast(1 as binary) FROM t;
0039 SELECT '2' <> cast(1 as binary) FROM t;
0040 SELECT cast(null as string) = cast(1 as binary) FROM t;
0041 SELECT cast(null as string) > cast(1 as binary) FROM t;
0042 SELECT cast(null as string) >= cast(1 as binary) FROM t;
0043 SELECT cast(null as string) < cast(1 as binary) FROM t;
0044 SELECT cast(null as string) <= cast(1 as binary) FROM t;
0045 SELECT cast(null as string) <> cast(1 as binary) FROM t;
0046 SELECT cast(1 as tinyint) = '1' FROM t;
0047 SELECT cast(1 as tinyint) > '2' FROM t;
0048 SELECT cast(1 as tinyint) >= '2' FROM t;
0049 SELECT cast(1 as tinyint) < '2' FROM t;
0050 SELECT cast(1 as tinyint) <= '2' FROM t;
0051 SELECT cast(1 as tinyint) <> '2' FROM t;
0052 SELECT cast(1 as tinyint) = cast(null as string) FROM t;
0053 SELECT cast(1 as tinyint) > cast(null as string) FROM t;
0054 SELECT cast(1 as tinyint) >= cast(null as string) FROM t;
0055 SELECT cast(1 as tinyint) < cast(null as string) FROM t;
0056 SELECT cast(1 as tinyint) <= cast(null as string) FROM t;
0057 SELECT cast(1 as tinyint) <> cast(null as string) FROM t;
0058 SELECT '1' = cast(1 as tinyint) FROM t;
0059 SELECT '2' > cast(1 as tinyint) FROM t;
0060 SELECT '2' >= cast(1 as tinyint) FROM t;
0061 SELECT '2' < cast(1 as tinyint) FROM t;
0062 SELECT '2' <= cast(1 as tinyint) FROM t;
0063 SELECT '2' <> cast(1 as tinyint) FROM t;
0064 SELECT cast(null as string) = cast(1 as tinyint) FROM t;
0065 SELECT cast(null as string) > cast(1 as tinyint) FROM t;
0066 SELECT cast(null as string) >= cast(1 as tinyint) FROM t;
0067 SELECT cast(null as string) < cast(1 as tinyint) FROM t;
0068 SELECT cast(null as string) <= cast(1 as tinyint) FROM t;
0069 SELECT cast(null as string) <> cast(1 as tinyint) FROM t;
0070 SELECT cast(1 as smallint) = '1' FROM t;
0071 SELECT cast(1 as smallint) > '2' FROM t;
0072 SELECT cast(1 as smallint) >= '2' FROM t;
0073 SELECT cast(1 as smallint) < '2' FROM t;
0074 SELECT cast(1 as smallint) <= '2' FROM t;
0075 SELECT cast(1 as smallint) <> '2' FROM t;
0076 SELECT cast(1 as smallint) = cast(null as string) FROM t;
0077 SELECT cast(1 as smallint) > cast(null as string) FROM t;
0078 SELECT cast(1 as smallint) >= cast(null as string) FROM t;
0079 SELECT cast(1 as smallint) < cast(null as string) FROM t;
0080 SELECT cast(1 as smallint) <= cast(null as string) FROM t;
0081 SELECT cast(1 as smallint) <> cast(null as string) FROM t;
0082 SELECT '1' = cast(1 as smallint) FROM t;
0083 SELECT '2' > cast(1 as smallint) FROM t;
0084 SELECT '2' >= cast(1 as smallint) FROM t;
0085 SELECT '2' < cast(1 as smallint) FROM t;
0086 SELECT '2' <= cast(1 as smallint) FROM t;
0087 SELECT '2' <> cast(1 as smallint) FROM t;
0088 SELECT cast(null as string) = cast(1 as smallint) FROM t;
0089 SELECT cast(null as string) > cast(1 as smallint) FROM t;
0090 SELECT cast(null as string) >= cast(1 as smallint) FROM t;
0091 SELECT cast(null as string) < cast(1 as smallint) FROM t;
0092 SELECT cast(null as string) <= cast(1 as smallint) FROM t;
0093 SELECT cast(null as string) <> cast(1 as smallint) FROM t;
0094 SELECT cast(1 as int) = '1' FROM t;
0095 SELECT cast(1 as int) > '2' FROM t;
0096 SELECT cast(1 as int) >= '2' FROM t;
0097 SELECT cast(1 as int) < '2' FROM t;
0098 SELECT cast(1 as int) <= '2' FROM t;
0099 SELECT cast(1 as int) <> '2' FROM t;
0100 SELECT cast(1 as int) = cast(null as string) FROM t;
0101 SELECT cast(1 as int) > cast(null as string) FROM t;
0102 SELECT cast(1 as int) >= cast(null as string) FROM t;
0103 SELECT cast(1 as int) < cast(null as string) FROM t;
0104 SELECT cast(1 as int) <= cast(null as string) FROM t;
0105 SELECT cast(1 as int) <> cast(null as string) FROM t;
0106 SELECT '1' = cast(1 as int) FROM t;
0107 SELECT '2' > cast(1 as int) FROM t;
0108 SELECT '2' >= cast(1 as int) FROM t;
0109 SELECT '2' < cast(1 as int) FROM t;
0110 SELECT '2' <> cast(1 as int) FROM t;
0111 SELECT '2' <= cast(1 as int) FROM t;
0112 SELECT cast(null as string) = cast(1 as int) FROM t;
0113 SELECT cast(null as string) > cast(1 as int) FROM t;
0114 SELECT cast(null as string) >= cast(1 as int) FROM t;
0115 SELECT cast(null as string) < cast(1 as int) FROM t;
0116 SELECT cast(null as string) <> cast(1 as int) FROM t;
0117 SELECT cast(null as string) <= cast(1 as int) FROM t;
0118 SELECT cast(1 as bigint) = '1' FROM t;
0119 SELECT cast(1 as bigint) > '2' FROM t;
0120 SELECT cast(1 as bigint) >= '2' FROM t;
0121 SELECT cast(1 as bigint) < '2' FROM t;
0122 SELECT cast(1 as bigint) <= '2' FROM t;
0123 SELECT cast(1 as bigint) <> '2' FROM t;
0124 SELECT cast(1 as bigint) = cast(null as string) FROM t;
0125 SELECT cast(1 as bigint) > cast(null as string) FROM t;
0126 SELECT cast(1 as bigint) >= cast(null as string) FROM t;
0127 SELECT cast(1 as bigint) < cast(null as string) FROM t;
0128 SELECT cast(1 as bigint) <= cast(null as string) FROM t;
0129 SELECT cast(1 as bigint) <> cast(null as string) FROM t;
0130 SELECT '1' = cast(1 as bigint) FROM t;
0131 SELECT '2' > cast(1 as bigint) FROM t;
0132 SELECT '2' >= cast(1 as bigint) FROM t;
0133 SELECT '2' < cast(1 as bigint) FROM t;
0134 SELECT '2' <= cast(1 as bigint) FROM t;
0135 SELECT '2' <> cast(1 as bigint) FROM t;
0136 SELECT cast(null as string) = cast(1 as bigint) FROM t;
0137 SELECT cast(null as string) > cast(1 as bigint) FROM t;
0138 SELECT cast(null as string) >= cast(1 as bigint) FROM t;
0139 SELECT cast(null as string) < cast(1 as bigint) FROM t;
0140 SELECT cast(null as string) <= cast(1 as bigint) FROM t;
0141 SELECT cast(null as string) <> cast(1 as bigint) FROM t;
0142 SELECT cast(1 as decimal(10, 0)) = '1' FROM t;
0143 SELECT cast(1 as decimal(10, 0)) > '2' FROM t;
0144 SELECT cast(1 as decimal(10, 0)) >= '2' FROM t;
0145 SELECT cast(1 as decimal(10, 0)) < '2' FROM t;
0146 SELECT cast(1 as decimal(10, 0)) <> '2' FROM t;
0147 SELECT cast(1 as decimal(10, 0)) <= '2' FROM t;
0148 SELECT cast(1 as decimal(10, 0)) = cast(null as string) FROM t;
0149 SELECT cast(1 as decimal(10, 0)) > cast(null as string) FROM t;
0150 SELECT cast(1 as decimal(10, 0)) >= cast(null as string) FROM t;
0151 SELECT cast(1 as decimal(10, 0)) < cast(null as string) FROM t;
0152 SELECT cast(1 as decimal(10, 0)) <> cast(null as string) FROM t;
0153 SELECT cast(1 as decimal(10, 0)) <= cast(null as string) FROM t;
0154 SELECT '1' = cast(1 as decimal(10, 0)) FROM t;
0155 SELECT '2' > cast(1 as decimal(10, 0)) FROM t;
0156 SELECT '2' >= cast(1 as decimal(10, 0)) FROM t;
0157 SELECT '2' < cast(1 as decimal(10, 0)) FROM t;
0158 SELECT '2' <= cast(1 as decimal(10, 0)) FROM t;
0159 SELECT '2' <> cast(1 as decimal(10, 0)) FROM t;
0160 SELECT cast(null as string) = cast(1 as decimal(10, 0)) FROM t;
0161 SELECT cast(null as string) > cast(1 as decimal(10, 0)) FROM t;
0162 SELECT cast(null as string) >= cast(1 as decimal(10, 0)) FROM t;
0163 SELECT cast(null as string) < cast(1 as decimal(10, 0)) FROM t;
0164 SELECT cast(null as string) <= cast(1 as decimal(10, 0)) FROM t;
0165 SELECT cast(null as string) <> cast(1 as decimal(10, 0)) FROM t;
0166 SELECT cast(1 as double) = '1' FROM t;
0167 SELECT cast(1 as double) > '2' FROM t;
0168 SELECT cast(1 as double) >= '2' FROM t;
0169 SELECT cast(1 as double) < '2' FROM t;
0170 SELECT cast(1 as double) <= '2' FROM t;
0171 SELECT cast(1 as double) <> '2' FROM t;
0172 SELECT cast(1 as double) = cast(null as string) FROM t;
0173 SELECT cast(1 as double) > cast(null as string) FROM t;
0174 SELECT cast(1 as double) >= cast(null as string) FROM t;
0175 SELECT cast(1 as double) < cast(null as string) FROM t;
0176 SELECT cast(1 as double) <= cast(null as string) FROM t;
0177 SELECT cast(1 as double) <> cast(null as string) FROM t;
0178 SELECT '1' = cast(1 as double) FROM t;
0179 SELECT '2' > cast(1 as double) FROM t;
0180 SELECT '2' >= cast(1 as double) FROM t;
0181 SELECT '2' < cast(1 as double) FROM t;
0182 SELECT '2' <= cast(1 as double) FROM t;
0183 SELECT '2' <> cast(1 as double) FROM t;
0184 SELECT cast(null as string) = cast(1 as double) FROM t;
0185 SELECT cast(null as string) > cast(1 as double) FROM t;
0186 SELECT cast(null as string) >= cast(1 as double) FROM t;
0187 SELECT cast(null as string) < cast(1 as double) FROM t;
0188 SELECT cast(null as string) <= cast(1 as double) FROM t;
0189 SELECT cast(null as string) <> cast(1 as double) FROM t;
0190 SELECT cast(1 as float) = '1' FROM t;
0191 SELECT cast(1 as float) > '2' FROM t;
0192 SELECT cast(1 as float) >= '2' FROM t;
0193 SELECT cast(1 as float) < '2' FROM t;
0194 SELECT cast(1 as float) <= '2' FROM t;
0195 SELECT cast(1 as float) <> '2' FROM t;
0196 SELECT cast(1 as float) = cast(null as string) FROM t;
0197 SELECT cast(1 as float) > cast(null as string) FROM t;
0198 SELECT cast(1 as float) >= cast(null as string) FROM t;
0199 SELECT cast(1 as float) < cast(null as string) FROM t;
0200 SELECT cast(1 as float) <= cast(null as string) FROM t;
0201 SELECT cast(1 as float) <> cast(null as string) FROM t;
0202 SELECT '1' = cast(1 as float) FROM t;
0203 SELECT '2' > cast(1 as float) FROM t;
0204 SELECT '2' >= cast(1 as float) FROM t;
0205 SELECT '2' < cast(1 as float) FROM t;
0206 SELECT '2' <= cast(1 as float) FROM t;
0207 SELECT '2' <> cast(1 as float) FROM t;
0208 SELECT cast(null as string) = cast(1 as float) FROM t;
0209 SELECT cast(null as string) > cast(1 as float) FROM t;
0210 SELECT cast(null as string) >= cast(1 as float) FROM t;
0211 SELECT cast(null as string) < cast(1 as float) FROM t;
0212 SELECT cast(null as string) <= cast(1 as float) FROM t;
0213 SELECT cast(null as string) <> cast(1 as float) FROM t;
0214 -- the following queries return 1 if the search condition is satisfied
0215 -- and returns nothing if the search condition is not satisfied
0216 SELECT '1996-09-09' = date('1996-09-09') FROM t;
0217 SELECT '1996-9-10' > date('1996-09-09') FROM t;
0218 SELECT '1996-9-10' >= date('1996-09-09') FROM t;
0219 SELECT '1996-9-10' < date('1996-09-09') FROM t;
0220 SELECT '1996-9-10' <= date('1996-09-09') FROM t;
0221 SELECT '1996-9-10' <> date('1996-09-09') FROM t;
0222 SELECT cast(null as string) = date('1996-09-09') FROM t;
0223 SELECT cast(null as string)> date('1996-09-09') FROM t;
0224 SELECT cast(null as string)>= date('1996-09-09') FROM t;
0225 SELECT cast(null as string)< date('1996-09-09') FROM t;
0226 SELECT cast(null as string)<= date('1996-09-09') FROM t;
0227 SELECT cast(null as string)<> date('1996-09-09') FROM t;
0228 SELECT date('1996-09-09') = '1996-09-09' FROM t;
0229 SELECT date('1996-9-10') > '1996-09-09' FROM t;
0230 SELECT date('1996-9-10') >= '1996-09-09' FROM t;
0231 SELECT date('1996-9-10') < '1996-09-09' FROM t;
0232 SELECT date('1996-9-10') <= '1996-09-09' FROM t;
0233 SELECT date('1996-9-10') <> '1996-09-09' FROM t;
0234 SELECT date('1996-09-09') = cast(null as string) FROM t;
0235 SELECT date('1996-9-10') > cast(null as string) FROM t;
0236 SELECT date('1996-9-10') >= cast(null as string) FROM t;
0237 SELECT date('1996-9-10') < cast(null as string) FROM t;
0238 SELECT date('1996-9-10') <= cast(null as string) FROM t;
0239 SELECT date('1996-9-10') <> cast(null as string) FROM t;
0240 SELECT '1996-09-09 12:12:12.4' = timestamp('1996-09-09 12:12:12.4') FROM t;
0241 SELECT '1996-09-09 12:12:12.5' > timestamp('1996-09-09 12:12:12.4') FROM t;
0242 SELECT '1996-09-09 12:12:12.5' >= timestamp('1996-09-09 12:12:12.4') FROM t;
0243 SELECT '1996-09-09 12:12:12.5' < timestamp('1996-09-09 12:12:12.4') FROM t;
0244 SELECT '1996-09-09 12:12:12.5' <= timestamp('1996-09-09 12:12:12.4') FROM t;
0245 SELECT '1996-09-09 12:12:12.5' <> timestamp('1996-09-09 12:12:12.4') FROM t;
0246 SELECT cast(null as string) = timestamp('1996-09-09 12:12:12.4') FROM t;
0247 SELECT cast(null as string) > timestamp('1996-09-09 12:12:12.4') FROM t;
0248 SELECT cast(null as string) >= timestamp('1996-09-09 12:12:12.4') FROM t;
0249 SELECT cast(null as string) < timestamp('1996-09-09 12:12:12.4') FROM t;
0250 SELECT cast(null as string) <= timestamp('1996-09-09 12:12:12.4') FROM t;
0251 SELECT cast(null as string) <> timestamp('1996-09-09 12:12:12.4') FROM t;
0252 SELECT timestamp('1996-09-09 12:12:12.4' )= '1996-09-09 12:12:12.4' FROM t;
0253 SELECT timestamp('1996-09-09 12:12:12.5' )> '1996-09-09 12:12:12.4' FROM t;
0254 SELECT timestamp('1996-09-09 12:12:12.5' )>= '1996-09-09 12:12:12.4' FROM t;
0255 SELECT timestamp('1996-09-09 12:12:12.5' )< '1996-09-09 12:12:12.4' FROM t;
0256 SELECT timestamp('1996-09-09 12:12:12.5' )<= '1996-09-09 12:12:12.4' FROM t;
0257 SELECT timestamp('1996-09-09 12:12:12.5' )<> '1996-09-09 12:12:12.4' FROM t;
0258 SELECT timestamp('1996-09-09 12:12:12.4' )= cast(null as string) FROM t;
0259 SELECT timestamp('1996-09-09 12:12:12.5' )> cast(null as string) FROM t;
0260 SELECT timestamp('1996-09-09 12:12:12.5' )>= cast(null as string) FROM t;
0261 SELECT timestamp('1996-09-09 12:12:12.5' )< cast(null as string) FROM t;
0262 SELECT timestamp('1996-09-09 12:12:12.5' )<= cast(null as string) FROM t;
0263 SELECT timestamp('1996-09-09 12:12:12.5' )<> cast(null as string) FROM t;
0264 SELECT ' ' = X'0020' FROM t;
0265 SELECT ' ' > X'001F' FROM t;
0266 SELECT ' ' >= X'001F' FROM t;
0267 SELECT ' ' < X'001F' FROM t;
0268 SELECT ' ' <= X'001F' FROM t;
0269 SELECT ' ' <> X'001F' FROM t;
0270 SELECT cast(null as string) = X'0020' FROM t;
0271 SELECT cast(null as string) > X'001F' FROM t;
0272 SELECT cast(null as string) >= X'001F' FROM t;
0273 SELECT cast(null as string) < X'001F' FROM t;
0274 SELECT cast(null as string) <= X'001F' FROM t;
0275 SELECT cast(null as string) <> X'001F' FROM t;
0276 SELECT X'0020' = ' ' FROM t;
0277 SELECT X'001F' > ' ' FROM t;
0278 SELECT X'001F' >= ' ' FROM t;
0279 SELECT X'001F' < ' ' FROM t;
0280 SELECT X'001F' <= ' ' FROM t;
0281 SELECT X'001F' <> ' ' FROM t;
0282 SELECT X'0020' = cast(null as string) FROM t;
0283 SELECT X'001F' > cast(null as string) FROM t;
0284 SELECT X'001F' >= cast(null as string) FROM t;
0285 SELECT X'001F' < cast(null as string) FROM t;
0286 SELECT X'001F' <= cast(null as string) FROM t;
0287 SELECT X'001F' <> cast(null as string) FROM t;