typeCoercion/native/promoteStrings.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 CREATE TEMPORARY VIEW t AS SELECT 1;
0019
0020 -- Binary arithmetic
0021 SELECT '1' + cast(1 as tinyint)                         FROM t;
0022 SELECT '1' + cast(1 as smallint)                        FROM t;
0023 SELECT '1' + cast(1 as int)                             FROM t;
0024 SELECT '1' + cast(1 as bigint)                          FROM t;
0025 SELECT '1' + cast(1 as float)                           FROM t;
0026 SELECT '1' + cast(1 as double)                          FROM t;
0027 SELECT '1' + cast(1 as decimal(10, 0))                  FROM t;
0028 SELECT '1' + '1'                                        FROM t;
0029 SELECT '1' + cast('1' as binary)                        FROM t;
0030 SELECT '1' + cast(1 as boolean)                         FROM t;
0031 SELECT '1' + cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0032 SELECT '1' + cast('2017-12-11 09:30:00' as date)        FROM t;
0033
0034 SELECT '1' - cast(1 as tinyint)                         FROM t;
0035 SELECT '1' - cast(1 as smallint)                        FROM t;
0036 SELECT '1' - cast(1 as int)                             FROM t;
0037 SELECT '1' - cast(1 as bigint)                          FROM t;
0038 SELECT '1' - cast(1 as float)                           FROM t;
0039 SELECT '1' - cast(1 as double)                          FROM t;
0040 SELECT '1' - cast(1 as decimal(10, 0))                  FROM t;
0041 SELECT '1' - '1'                                        FROM t;
0042 SELECT '1' - cast('1' as binary)                        FROM t;
0043 SELECT '1' - cast(1 as boolean)                         FROM t;
0044 SELECT '1' - cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0045 SELECT '1' - cast('2017-12-11 09:30:00' as date)        FROM t;
0046
0047 SELECT '1' * cast(1 as tinyint)                         FROM t;
0048 SELECT '1' * cast(1 as smallint)                        FROM t;
0049 SELECT '1' * cast(1 as int)                             FROM t;
0050 SELECT '1' * cast(1 as bigint)                          FROM t;
0051 SELECT '1' * cast(1 as float)                           FROM t;
0052 SELECT '1' * cast(1 as double)                          FROM t;
0053 SELECT '1' * cast(1 as decimal(10, 0))                  FROM t;
0054 SELECT '1' * '1'                                        FROM t;
0055 SELECT '1' * cast('1' as binary)                        FROM t;
0056 SELECT '1' * cast(1 as boolean)                         FROM t;
0057 SELECT '1' * cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0058 SELECT '1' * cast('2017-12-11 09:30:00' as date)        FROM t;
0059
0060 SELECT '1' / cast(1 as tinyint)                         FROM t;
0061 SELECT '1' / cast(1 as smallint)                        FROM t;
0062 SELECT '1' / cast(1 as int)                             FROM t;
0063 SELECT '1' / cast(1 as bigint)                          FROM t;
0064 SELECT '1' / cast(1 as float)                           FROM t;
0065 SELECT '1' / cast(1 as double)                          FROM t;
0066 SELECT '1' / cast(1 as decimal(10, 0))                  FROM t;
0067 SELECT '1' / '1'                                        FROM t;
0068 SELECT '1' / cast('1' as binary)                        FROM t;
0069 SELECT '1' / cast(1 as boolean)                         FROM t;
0070 SELECT '1' / cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0071 SELECT '1' / cast('2017-12-11 09:30:00' as date)        FROM t;
0072
0073 SELECT '1' % cast(1 as tinyint)                         FROM t;
0074 SELECT '1' % cast(1 as smallint)                        FROM t;
0075 SELECT '1' % cast(1 as int)                             FROM t;
0076 SELECT '1' % cast(1 as bigint)                          FROM t;
0077 SELECT '1' % cast(1 as float)                           FROM t;
0078 SELECT '1' % cast(1 as double)                          FROM t;
0079 SELECT '1' % cast(1 as decimal(10, 0))                  FROM t;
0080 SELECT '1' % '1'                                        FROM t;
0081 SELECT '1' % cast('1' as binary)                        FROM t;
0082 SELECT '1' % cast(1 as boolean)                         FROM t;
0083 SELECT '1' % cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0084 SELECT '1' % cast('2017-12-11 09:30:00' as date)        FROM t;
0085
0086 SELECT pmod('1', cast(1 as tinyint))                         FROM t;
0087 SELECT pmod('1', cast(1 as smallint))                        FROM t;
0088 SELECT pmod('1', cast(1 as int))                             FROM t;
0089 SELECT pmod('1', cast(1 as bigint))                          FROM t;
0090 SELECT pmod('1', cast(1 as float))                           FROM t;
0091 SELECT pmod('1', cast(1 as double))                          FROM t;
0092 SELECT pmod('1', cast(1 as decimal(10, 0)))                  FROM t;
0093 SELECT pmod('1', '1')                                        FROM t;
0094 SELECT pmod('1', cast('1' as binary))                        FROM t;
0095 SELECT pmod('1', cast(1 as boolean))                         FROM t;
0096 SELECT pmod('1', cast('2017-12-11 09:30:00.0' as timestamp)) FROM t;
0097 SELECT pmod('1', cast('2017-12-11 09:30:00' as date))        FROM t;
0098
0099 SELECT cast(1 as tinyint)                         + '1' FROM t;
0100 SELECT cast(1 as smallint)                        + '1' FROM t;
0101 SELECT cast(1 as int)                             + '1' FROM t;
0102 SELECT cast(1 as bigint)                          + '1' FROM t;
0103 SELECT cast(1 as float)                           + '1' FROM t;
0104 SELECT cast(1 as double)                          + '1' FROM t;
0105 SELECT cast(1 as decimal(10, 0))                  + '1' FROM t;
0106 SELECT cast('1' as binary)                        + '1' FROM t;
0107 SELECT cast(1 as boolean)                         + '1' FROM t;
0108 SELECT cast('2017-12-11 09:30:00.0' as timestamp) + '1' FROM t;
0109 SELECT cast('2017-12-11 09:30:00' as date)        + '1' FROM t;
0110
0111 SELECT cast(1 as tinyint)                         - '1' FROM t;
0112 SELECT cast(1 as smallint)                        - '1' FROM t;
0113 SELECT cast(1 as int)                             - '1' FROM t;
0114 SELECT cast(1 as bigint)                          - '1' FROM t;
0115 SELECT cast(1 as float)                           - '1' FROM t;
0116 SELECT cast(1 as double)                          - '1' FROM t;
0117 SELECT cast(1 as decimal(10, 0))                  - '1' FROM t;
0118 SELECT cast('1' as binary)                        - '1' FROM t;
0119 SELECT cast(1 as boolean)                         - '1' FROM t;
0120 SELECT cast('2017-12-11 09:30:00.0' as timestamp) - '1' FROM t;
0121 SELECT cast('2017-12-11 09:30:00' as date)        - '1' FROM t;
0122
0123 SELECT cast(1 as tinyint)                         * '1' FROM t;
0124 SELECT cast(1 as smallint)                        * '1' FROM t;
0125 SELECT cast(1 as int)                             * '1' FROM t;
0126 SELECT cast(1 as bigint)                          * '1' FROM t;
0127 SELECT cast(1 as float)                           * '1' FROM t;
0128 SELECT cast(1 as double)                          * '1' FROM t;
0129 SELECT cast(1 as decimal(10, 0))                  * '1' FROM t;
0130 SELECT cast('1' as binary)                        * '1' FROM t;
0131 SELECT cast(1 as boolean)                         * '1' FROM t;
0132 SELECT cast('2017-12-11 09:30:00.0' as timestamp) * '1' FROM t;
0133 SELECT cast('2017-12-11 09:30:00' as date)        * '1' FROM t;
0134
0135 SELECT cast(1 as tinyint)                         / '1' FROM t;
0136 SELECT cast(1 as smallint)                        / '1' FROM t;
0137 SELECT cast(1 as int)                             / '1' FROM t;
0138 SELECT cast(1 as bigint)                          / '1' FROM t;
0139 SELECT cast(1 as float)                           / '1' FROM t;
0140 SELECT cast(1 as double)                          / '1' FROM t;
0141 SELECT cast(1 as decimal(10, 0))                  / '1' FROM t;
0142 SELECT cast('1' as binary)                        / '1' FROM t;
0143 SELECT cast(1 as boolean)                         / '1' FROM t;
0144 SELECT cast('2017-12-11 09:30:00.0' as timestamp) / '1' FROM t;
0145 SELECT cast('2017-12-11 09:30:00' as date)        / '1' FROM t;
0146
0147 SELECT cast(1 as tinyint)                         % '1' FROM t;
0148 SELECT cast(1 as smallint)                        % '1' FROM t;
0149 SELECT cast(1 as int)                             % '1' FROM t;
0150 SELECT cast(1 as bigint)                          % '1' FROM t;
0151 SELECT cast(1 as float)                           % '1' FROM t;
0152 SELECT cast(1 as double)                          % '1' FROM t;
0153 SELECT cast(1 as decimal(10, 0))                  % '1' FROM t;
0154 SELECT cast('1' as binary)                        % '1' FROM t;
0155 SELECT cast(1 as boolean)                         % '1' FROM t;
0156 SELECT cast('2017-12-11 09:30:00.0' as timestamp) % '1' FROM t;
0157 SELECT cast('2017-12-11 09:30:00' as date)        % '1' FROM t;
0158
0159 SELECT pmod(cast(1 as tinyint), '1')                         FROM t;
0160 SELECT pmod(cast(1 as smallint), '1')                        FROM t;
0161 SELECT pmod(cast(1 as int), '1')                             FROM t;
0162 SELECT pmod(cast(1 as bigint), '1')                          FROM t;
0163 SELECT pmod(cast(1 as float), '1')                           FROM t;
0164 SELECT pmod(cast(1 as double), '1')                          FROM t;
0165 SELECT pmod(cast(1 as decimal(10, 0)), '1')                  FROM t;
0166 SELECT pmod(cast('1' as binary), '1')                        FROM t;
0167 SELECT pmod(cast(1 as boolean), '1')                         FROM t;
0168 SELECT pmod(cast('2017-12-11 09:30:00.0' as timestamp), '1') FROM t;
0169 SELECT pmod(cast('2017-12-11 09:30:00' as date), '1')        FROM t;
0170
0171 -- Equality
0172 SELECT '1' = cast(1 as tinyint)                         FROM t;
0173 SELECT '1' = cast(1 as smallint)                        FROM t;
0174 SELECT '1' = cast(1 as int)                             FROM t;
0175 SELECT '1' = cast(1 as bigint)                          FROM t;
0176 SELECT '1' = cast(1 as float)                           FROM t;
0177 SELECT '1' = cast(1 as double)                          FROM t;
0178 SELECT '1' = cast(1 as decimal(10, 0))                  FROM t;
0179 SELECT '1' = '1'                                        FROM t;
0180 SELECT '1' = cast('1' as binary)                        FROM t;
0181 SELECT '1' = cast(1 as boolean)                         FROM t;
0182 SELECT '1' = cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0183 SELECT '1' = cast('2017-12-11 09:30:00' as date)        FROM t;
0184
0185 SELECT cast(1 as tinyint)                         = '1' FROM t;
0186 SELECT cast(1 as smallint)                        = '1' FROM t;
0187 SELECT cast(1 as int)                             = '1' FROM t;
0188 SELECT cast(1 as bigint)                          = '1' FROM t;
0189 SELECT cast(1 as float)                           = '1' FROM t;
0190 SELECT cast(1 as double)                          = '1' FROM t;
0191 SELECT cast(1 as decimal(10, 0))                  = '1' FROM t;
0192 SELECT cast('1' as binary)                        = '1' FROM t;
0193 SELECT cast(1 as boolean)                         = '1' FROM t;
0194 SELECT cast('2017-12-11 09:30:00.0' as timestamp) = '1' FROM t;
0195 SELECT cast('2017-12-11 09:30:00' as date)        = '1' FROM t;
0196
0197 SELECT '1' <=> cast(1 as tinyint)                         FROM t;
0198 SELECT '1' <=> cast(1 as smallint)                        FROM t;
0199 SELECT '1' <=> cast(1 as int)                             FROM t;
0200 SELECT '1' <=> cast(1 as bigint)                          FROM t;
0201 SELECT '1' <=> cast(1 as float)                           FROM t;
0202 SELECT '1' <=> cast(1 as double)                          FROM t;
0203 SELECT '1' <=> cast(1 as decimal(10, 0))                  FROM t;
0204 SELECT '1' <=> '1'                                        FROM t;
0205 SELECT '1' <=> cast('1' as binary)                        FROM t;
0206 SELECT '1' <=> cast(1 as boolean)                         FROM t;
0207 SELECT '1' <=> cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0208 SELECT '1' <=> cast('2017-12-11 09:30:00' as date)        FROM t;
0209
0210 SELECT cast(1 as tinyint)                         <=> '1' FROM t;
0211 SELECT cast(1 as smallint)                        <=> '1' FROM t;
0212 SELECT cast(1 as int)                             <=> '1' FROM t;
0213 SELECT cast(1 as bigint)                          <=> '1' FROM t;
0214 SELECT cast(1 as float)                           <=> '1' FROM t;
0215 SELECT cast(1 as double)                          <=> '1' FROM t;
0216 SELECT cast(1 as decimal(10, 0))                  <=> '1' FROM t;
0217 SELECT cast('1' as binary)                        <=> '1' FROM t;
0218 SELECT cast(1 as boolean)                         <=> '1' FROM t;
0219 SELECT cast('2017-12-11 09:30:00.0' as timestamp) <=> '1' FROM t;
0220 SELECT cast('2017-12-11 09:30:00' as date)        <=> '1' FROM t;
0221
0222 -- Binary comparison
0223 SELECT '1' < cast(1 as tinyint)                         FROM t;
0224 SELECT '1' < cast(1 as smallint)                        FROM t;
0225 SELECT '1' < cast(1 as int)                             FROM t;
0226 SELECT '1' < cast(1 as bigint)                          FROM t;
0227 SELECT '1' < cast(1 as float)                           FROM t;
0228 SELECT '1' < cast(1 as double)                          FROM t;
0229 SELECT '1' < cast(1 as decimal(10, 0))                  FROM t;
0230 SELECT '1' < '1'                                        FROM t;
0231 SELECT '1' < cast('1' as binary)                        FROM t;
0232 SELECT '1' < cast(1 as boolean)                         FROM t;
0233 SELECT '1' < cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0234 SELECT '1' < cast('2017-12-11 09:30:00' as date)        FROM t;
0235
0236 SELECT '1' <= cast(1 as tinyint)                         FROM t;
0237 SELECT '1' <= cast(1 as smallint)                        FROM t;
0238 SELECT '1' <= cast(1 as int)                             FROM t;
0239 SELECT '1' <= cast(1 as bigint)                          FROM t;
0240 SELECT '1' <= cast(1 as float)                           FROM t;
0241 SELECT '1' <= cast(1 as double)                          FROM t;
0242 SELECT '1' <= cast(1 as decimal(10, 0))                  FROM t;
0243 SELECT '1' <= '1'                                        FROM t;
0244 SELECT '1' <= cast('1' as binary)                        FROM t;
0245 SELECT '1' <= cast(1 as boolean)                         FROM t;
0246 SELECT '1' <= cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0247 SELECT '1' <= cast('2017-12-11 09:30:00' as date)        FROM t;
0248
0249 SELECT '1' > cast(1 as tinyint)                         FROM t;
0250 SELECT '1' > cast(1 as smallint)                        FROM t;
0251 SELECT '1' > cast(1 as int)                             FROM t;
0252 SELECT '1' > cast(1 as bigint)                          FROM t;
0253 SELECT '1' > cast(1 as float)                           FROM t;
0254 SELECT '1' > cast(1 as double)                          FROM t;
0255 SELECT '1' > cast(1 as decimal(10, 0))                  FROM t;
0256 SELECT '1' > '1'                                        FROM t;
0257 SELECT '1' > cast('1' as binary)                        FROM t;
0258 SELECT '1' > cast(1 as boolean)                         FROM t;
0259 SELECT '1' > cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0260 SELECT '1' > cast('2017-12-11 09:30:00' as date)        FROM t;
0261
0262 SELECT '1' >= cast(1 as tinyint)                         FROM t;
0263 SELECT '1' >= cast(1 as smallint)                        FROM t;
0264 SELECT '1' >= cast(1 as int)                             FROM t;
0265 SELECT '1' >= cast(1 as bigint)                          FROM t;
0266 SELECT '1' >= cast(1 as float)                           FROM t;
0267 SELECT '1' >= cast(1 as double)                          FROM t;
0268 SELECT '1' >= cast(1 as decimal(10, 0))                  FROM t;
0269 SELECT '1' >= '1'                                        FROM t;
0270 SELECT '1' >= cast('1' as binary)                        FROM t;
0271 SELECT '1' >= cast(1 as boolean)                         FROM t;
0272 SELECT '1' >= cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0273 SELECT '1' >= cast('2017-12-11 09:30:00' as date)        FROM t;
0274
0275 SELECT '1' <> cast(1 as tinyint)                         FROM t;
0276 SELECT '1' <> cast(1 as smallint)                        FROM t;
0277 SELECT '1' <> cast(1 as int)                             FROM t;
0278 SELECT '1' <> cast(1 as bigint)                          FROM t;
0279 SELECT '1' <> cast(1 as float)                           FROM t;
0280 SELECT '1' <> cast(1 as double)                          FROM t;
0281 SELECT '1' <> cast(1 as decimal(10, 0))                  FROM t;
0282 SELECT '1' <> '1'                                        FROM t;
0283 SELECT '1' <> cast('1' as binary)                        FROM t;
0284 SELECT '1' <> cast(1 as boolean)                         FROM t;
0285 SELECT '1' <> cast('2017-12-11 09:30:00.0' as timestamp) FROM t;
0286 SELECT '1' <> cast('2017-12-11 09:30:00' as date)        FROM t;
0287
0288 SELECT cast(1 as tinyint)                         < '1' FROM t;
0289 SELECT cast(1 as smallint)                        < '1' FROM t;
0290 SELECT cast(1 as int)                             < '1' FROM t;
0291 SELECT cast(1 as bigint)                          < '1' FROM t;
0292 SELECT cast(1 as float)                           < '1' FROM t;
0293 SELECT cast(1 as double)                          < '1' FROM t;
0294 SELECT cast(1 as decimal(10, 0))                  < '1' FROM t;
0295 SELECT '1'                                        < '1' FROM t;
0296 SELECT cast('1' as binary)                        < '1' FROM t;
0297 SELECT cast(1 as boolean)                         < '1' FROM t;
0298 SELECT cast('2017-12-11 09:30:00.0' as timestamp) < '1' FROM t;
0299 SELECT cast('2017-12-11 09:30:00' as date)        < '1' FROM t;
0300
0301 SELECT cast(1 as tinyint)                         <= '1' FROM t;
0302 SELECT cast(1 as smallint)                        <= '1' FROM t;
0303 SELECT cast(1 as int)                             <= '1' FROM t;
0304 SELECT cast(1 as bigint)                          <= '1' FROM t;
0305 SELECT cast(1 as float)                           <= '1' FROM t;
0306 SELECT cast(1 as double)                          <= '1' FROM t;
0307 SELECT cast(1 as decimal(10, 0))                  <= '1' FROM t;
0308 SELECT '1'                                        <= '1' FROM t;
0309 SELECT cast('1' as binary)                        <= '1' FROM t;
0310 SELECT cast(1 as boolean)                         <= '1' FROM t;
0311 SELECT cast('2017-12-11 09:30:00.0' as timestamp) <= '1' FROM t;
0312 SELECT cast('2017-12-11 09:30:00' as date)        <= '1' FROM t;
0313
0314 SELECT cast(1 as tinyint)                         > '1' FROM t;
0315 SELECT cast(1 as smallint)                        > '1' FROM t;
0316 SELECT cast(1 as int)                             > '1' FROM t;
0317 SELECT cast(1 as bigint)                          > '1' FROM t;
0318 SELECT cast(1 as float)                           > '1' FROM t;
0319 SELECT cast(1 as double)                          > '1' FROM t;
0320 SELECT cast(1 as decimal(10, 0))                  > '1' FROM t;
0321 SELECT '1'                                        > '1' FROM t;
0322 SELECT cast('1' as binary)                        > '1' FROM t;
0323 SELECT cast(1 as boolean)                         > '1' FROM t;
0324 SELECT cast('2017-12-11 09:30:00.0' as timestamp) > '1' FROM t;
0325 SELECT cast('2017-12-11 09:30:00' as date)        > '1' FROM t;
0326
0327 SELECT cast(1 as tinyint)                         >= '1' FROM t;
0328 SELECT cast(1 as smallint)                        >= '1' FROM t;
0329 SELECT cast(1 as int)                             >= '1' FROM t;
0330 SELECT cast(1 as bigint)                          >= '1' FROM t;
0331 SELECT cast(1 as float)                           >= '1' FROM t;
0332 SELECT cast(1 as double)                          >= '1' FROM t;
0333 SELECT cast(1 as decimal(10, 0))                  >= '1' FROM t;
0334 SELECT '1'                                        >= '1' FROM t;
0335 SELECT cast('1' as binary)                        >= '1' FROM t;
0336 SELECT cast(1 as boolean)                         >= '1' FROM t;
0337 SELECT cast('2017-12-11 09:30:00.0' as timestamp) >= '1' FROM t;
0338 SELECT cast('2017-12-11 09:30:00' as date)        >= '1' FROM t;
0339
0340 SELECT cast(1 as tinyint)                         <> '1' FROM t;
0341 SELECT cast(1 as smallint)                        <> '1' FROM t;
0342 SELECT cast(1 as int)                             <> '1' FROM t;
0343 SELECT cast(1 as bigint)                          <> '1' FROM t;
0344 SELECT cast(1 as float)                           <> '1' FROM t;
0345 SELECT cast(1 as double)                          <> '1' FROM t;
0346 SELECT cast(1 as decimal(10, 0))                  <> '1' FROM t;
0347 SELECT '1'                                        <> '1' FROM t;
0348 SELECT cast('1' as binary)                        <> '1' FROM t;
0349 SELECT cast(1 as boolean)                         <> '1' FROM t;
0350 SELECT cast('2017-12-11 09:30:00.0' as timestamp) <> '1' FROM t;
0351 SELECT cast('2017-12-11 09:30:00' as date)        <> '1' FROM t;
0352
0353 -- Functions
0354 SELECT abs('1') FROM t;
0355 SELECT sum('1') FROM t;
0356 SELECT avg('1') FROM t;
0357 SELECT stddev_pop('1') FROM t;
0358 SELECT stddev_samp('1') FROM t;
0359 SELECT - '1' FROM t;
0360 SELECT + '1' FROM t;
0361 SELECT var_pop('1') FROM t;
0362 SELECT var_samp('1') FROM t;
0363 SELECT skewness('1') FROM t;
0364 SELECT kurtosis('1') FROM t;