Test subquery2.sql (id c8fd537c0429) (err)
subquery2.stable.out.FILTERED 2020-07-31 19:38:50.049345082 +0200subquery2.test.out.FILTERED 2020-07-31 19:38:50.063345161 +0200
1 Statistics about stdout of test 'subquery2` in directory 'sql/test/subquery`: 1 1 Statistics about stdout of test 'subquery2` in directory 'sql/test/subquery`: 1
2 26 lines, 130 words, 582 chars in lines not matching '^$|^(\| |)#|^=` 2 ! 2 218 lines, 879 words, 3054 chars in lines not matching '^$|^(\| |)#|^=` 2
3 = 26 lines, 130 words, 582 chars in lines not matching '^$|^(\| |)#` 3 ! 3 = 218 lines, 879 words, 3054 chars in lines not matching '^$|^(\| |)#` 3
4 # 67 lines, 433 words, 2904 chars in all lines 4 ! 4 # 382 lines, 1968 words, 9821 chars in all lines 4
5 5 5 5
..................
#drop table students;
67 #drop table exams; 67 50 #drop table exams; 50
+ 51 #SELECT 1 IN (1, (SELECT 2)), 1 NOT IN (1, (SELECT 2)); 51
+ 52 % ., . # table_name 52
+ 53 # .L11, .L22 # table_name 53
+ 54 % L, L # name 54
+ 55 # L11, L22 # name 55
+ 56 % boolean, boolean # type 56
+ 57 % 5, 5 # length 57
+ 58 [ false, true ] 58
+ 59 #CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64), Product_Name varchar(64), TotalSales int); 59
+ 60 #INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100); 60
+ 61 [ 4 ] 61
+ 62 #CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT, col7 INT, col8 INT); 62
+ 63 #INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88), (111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888); 63
+ 64 [ 4 ] 64
+ 65 #SELECT col1 IN (SELECT ColID + col1 FROM tbl_ProductSales) FROM another_T GROUP BY col1; 65
+ 66 % . # table_name 66
+ 67 # .L7 # table_name 67
+ 68 % L # name 68
+ 69 # L7 # name 69
+ 70 % boolean # type 70
+ 71 % 5 # length 71
+ 72 [ false ] 72
+ 73 [ false ] 73
+ 74 [ false ] 74
+ 75 [ false ] 75
+ 76 #SELECT col1 IN (SELECT SUM(ColID + col1) FROM tbl_ProductSales) FROM another_T GROUP BY col1; 76
+ 77 % . # table_name 77
+ 78 # .L12 # table_name 78
+ 79 % L # name 79
+ 80 # L12 # name 80
+ 81 % boolean # type 81
+ 82 % 5 # length 82
+ 83 [ false ] 83
+ 84 [ false ] 84
+ 85 [ false ] 85
+ 86 [ false ] 86
+ 87 #SELECT (SELECT col2 FROM tbl_ProductSales WHERE tbl_ProductSales.ColID = another_T.col1) FROM another_T GROUP BY col1, col2; 87
+ 88 % . # table_name 88
+ 89 # .L1 # table_name 89
+ 90 % L # name 90
+ 91 # L1 # name 91
+ 92 % int # type 92
+ 93 % 1 # length 93
+ 94 [ 2 ] 94
+ 95 [ NULL ] 95
+ 96 [ NULL ] 96
+ 97 [ NULL ] 97
+ 98 #SELECT 98
+ 99 # EXISTS (SELECT col2 FROM tbl_ProductSales WHERE tbl_ProductSales.ColID = another_T.col1) 99
+ 100 #FROM another_T GROUP BY col1, col2, col5, col8; 100
+ 101 % . # table_name 101
+ 102 # .L1 # table_name 102
+ 103 % L # name 103
+ 104 # L1 # name 104
+ 105 % boolean # type 105
+ 106 % 5 # length 106
+ 107 [ true ] 107
+ 108 [ false ] 108
+ 109 [ false ] 109
+ 110 [ false ] 110
+ 111 #SELECT 111
+ 112 # -col1 IN (SELECT ColID FROM tbl_ProductSales), 112
+ 113 # col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MIN(col8) IS NULL) 113
+ 114 #FROM another_T GROUP BY col1, col2, col5, col8; 114
+ 115 % ., . # table_name 115
+ 116 # .L11, .L20 # table_name 116
+ 117 % L, L # name 117
+ 118 # L11, L20 # name 118
+ 119 % boolean, boolean # type 119
+ 120 % 5, 5 # length 120
+ 121 [ false, true ] 121
+ 122 [ false, true ] 122
+ 123 [ false, true ] 123
+ 124 [ false, true ] 124
+ 125 #SELECT 125
+ 126 # col1 + col5 = (SELECT MIN(ColID) FROM tbl_ProductSales), 126
+ 127 # CAST(SUM(DISTINCT CASE WHEN col5 - col8 = (SELECT MIN(ColID / col2) FROM tbl_ProductSales) THEN col2 - 5 ELSE ABS(col1) END) AS BIGINT), 127
+ 128 # (SELECT MAX(ColID + col2) FROM tbl_ProductSales) * DENSE_RANK() OVER (PARTITION BY AVG(DISTINCT col5)) 128
+ 129 #FROM another_T 129
+ 130 #GROUP BY col1, col2, col5, col8; 130
+ 131 % sys., sys., . # table_name 131
+ 132 # sys.L4, sys.L13, .L26 # table_name 132
+ 133 % L, L, L # name 133
+ 134 # L4, L13, L26 # name 134
+ 135 % boolean, bigint, hugeint # type 135
+ 136 % 5, 4, 4 # length 136
+ 137 [ false, 1, 6 ] 137
+ 138 [ false, 11, 26 ] 138
+ 139 [ false, 111, 226 ] 139
+ 140 [ false, 1111, 2226 ] 140
+ 141 #SELECT 141
+ 142 # col1 + col5 = (SELECT MIN(ColID) FROM tbl_ProductSales), 142
+ 143 # MIN(col8) OVER (PARTITION BY col5 ORDER BY col1 ROWS UNBOUNDED PRECEDING) 143
+ 144 #FROM another_T 144
+ 145 #GROUP BY col1, col2, col5, col8; 145
+ 146 % sys., sys. # table_name 146
+ 147 # sys.L4, sys.L7 # table_name 147
+ 148 % L, L # name 148
+ 149 # L4, L7 # name 149
+ 150 % boolean, int # type 150
+ 151 % 5, 4 # length 151
+ 152 [ false, 8 ] 152
+ 153 [ false, 88 ] 153
+ 154 [ false, 888 ] 154
+ 155 [ false, 8888 ] 155
+ 156 #SELECT 156
+ 157 # EXISTS (SELECT 1 FROM tbl_ProductSales), 157
+ 158 # NOT EXISTS (SELECT 1 FROM tbl_ProductSales) 158
+ 159 #FROM another_T 159
+ 160 #GROUP BY col1; 160
+ 161 % ., . # table_name 161
+ 162 # .L2, .L4 # table_name 162
+ 163 % L, L # name 163
+ 164 # L2, L4 # name 164
+ 165 % boolean, boolean # type 165
+ 166 % 5, 5 # length 166
+ 167 [ true, false ] 167
+ 168 [ true, false ] 168
+ 169 [ true, false ] 169
+ 170 [ true, false ] 170
+ 171 #SELECT NOT col2 <> ANY (SELECT 20 FROM tbl_ProductSales GROUP BY ColID HAVING NOT MAX(col1) <> col1 * AVG(col1 + ColID) * ColID) FROM another_T GROUP BY col1, col2, col5, col8; 171
+ 172 % sys. # table_name 172
+ 173 # sys.L7 # table_name 173
+ 174 % L # name 174
+ 175 # L7 # name 175
+ 176 % boolean # type 176
+ 177 % 5 # length 177
+ 178 #SELECT 178
+ 179 # SUM(col1) IN (SELECT DISTINCT col2 FROM another_T GROUP BY col2) 179
+ 180 #FROM another_T 180
+ 181 #GROUP BY col4; 181
+ 182 % . # table_name 182
+ 183 # .L11 # table_name 183
+ 184 % L # name 184
+ 185 # L11 # name 185
+ 186 % boolean # type 186
+ 187 % 5 # length 187
+ 188 [ false ] 188
+ 189 [ false ] 189
+ 190 [ false ] 190
+ 191 [ false ] 191
+ 192 #SELECT 192
+ 193 # (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t1.col5 = t2.col1) 193
+ 194 #FROM another_T t1; 194
+ 195 % . # table_name 195
+ 196 # .L4 # table_name 196
+ 197 % L # name 197
+ 198 # L4 # name 198
+ 199 % int # type 199
+ 200 % 1 # length 200
+ 201 [ NULL ] 201
+ 202 [ NULL ] 202
+ 203 [ NULL ] 203
+ 204 [ NULL ] 204
+ 205 #SELECT 205
+ 206 # t1.col1 = ALL (SELECT col4 + SUM(t1.col5) FROM another_T INNER JOIN tbl_ProductSales ON another_T.col1 = tbl_ProductSales.ColID) 206
+ 207 #FROM another_T t1 207
+ 208 #GROUP BY t1.col1; 208
+ 209 % sys. # table_name 209
+ 210 # sys.L13 # table_name 210
+ 211 % L # name 211
+ 212 # L13 # name 212
+ 213 % boolean # type 213
+ 214 % 5 # length 214
+ 215 [ false ] 215
+ 216 [ false ] 216
+ 217 [ false ] 217
+ 218 [ false ] 218
+ 219 #SELECT 219
+ 220 # SUM(t1.col6) NOT IN (SELECT t1.col7), 220
+ 221 # t1.col6 NOT IN (SELECT t1.col7), 221
+ 222 # t1.col6 IN (SELECT SUM(t1.col7)), 222
+ 223 # t1.col6 IN (SELECT SUM(t1.col7) FROM tbl_ProductSales) 223
+ 224 #FROM another_T t1 224
+ 225 #GROUP BY t1.col6, t1.col7; 225
+ 226 % ., ., ., . # table_name 226
+ 227 # .L7, .L16, .L31, .L42 # table_name 227
+ 228 % L, L, L, L # name 228
+ 229 # L7, L16, L31, L42 # name 229
+ 230 % boolean, boolean, boolean, boolean # type 230
+ 231 % 5, 5, 5, 5 # length 231
+ 232 [ true, true, false, false ] 232
+ 233 [ true, true, false, false ] 233
+ 234 [ true, true, false, false ] 234
+ 235 [ true, true, false, false ] 235
+ 236 #SELECT 236
+ 237 # SUM(col3 + col2) 237
+ 238 #FROM another_T 238
+ 239 #GROUP BY col1 239
+ 240 #HAVING NOT col1 = ANY (SELECT 0 FROM tbl_ProductSales GROUP BY ColID HAVING NOT MAX(col1) <> AVG(col1)); 240
+ 241 % sys. # table_name 241
+ 242 # sys.L1 # table_name 242
+ 243 % L # name 243
+ 244 # L1 # name 244
+ 245 % bigint # type 245
+ 246 % 4 # length 246
+ 247 [ 5 ] 247
+ 248 [ 55 ] 248
+ 249 [ 555 ] 249
+ 250 [ 5555 ] 250
+ 251 #SELECT 251
+ 252 # SUM(col3) * col1 252
+ 253 #FROM another_T 253
+ 254 #GROUP BY col1 254
+ 255 #HAVING NOT col1 <> ANY (SELECT 0 FROM tbl_ProductSales GROUP BY ColID HAVING NOT MAX(col1) <> col1 * AVG(col1 + ColID) * ColID); 255
+ 256 % sys. # table_name 256
+ 257 # sys.L2 # table_name 257
+ 258 % L # name 258
+ 259 # L2 # name 259
+ 260 % hugeint # type 260
+ 261 % 1 # length 261
+ 262 #SELECT 262
+ 263 # SUM(CAST(t1.col1 IN (SELECT t1.col1 FROM another_T) AS INTEGER)) 263
+ 264 #FROM another_T t1 264
+ 265 #GROUP BY t1.col2; 265
+ 266 % . # table_name 266
+ 267 # .L10 # table_name 267
+ 268 % L # name 268
+ 269 # L10 # name 269
+ 270 % hugeint # type 270
+ 271 % 1 # length 271
+ 272 [ 1 ] 272
+ 273 [ 1 ] 273
+ 274 [ 1 ] 274
+ 275 [ 1 ] 275
+ 276 #SELECT 276
+ 277 # (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t1.col7 <> SOME(SELECT MAX(t1.col1 + t3.col4) FROM another_T t3)) 277
+ 278 #FROM another_T t1; 278
+ 279 % . # table_name 279
+ 280 # .L11 # table_name 280
+ 281 % L # name 281
+ 282 # L11 # name 282
+ 283 % int # type 283
+ 284 % 1 # length 284
+ 285 #SELECT 285
+ 286 # CASE WHEN 1 IN (SELECT (SELECT MAX(col7)) UNION ALL (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t2.col5 = t2.col1)) THEN 2 ELSE NULL END 286
+ 287 #FROM another_T t1; 287
+ 288 % . # table_name 288
+ 289 # .L26 # table_name 289
+ 290 % L # name 290
+ 291 # L26 # name 291
+ 292 % tinyint # type 292
+ 293 % 1 # length 293
+ 294 [ NULL ] 294
+ 295 [ NULL ] 295
+ 296 [ NULL ] 296
+ 297 [ NULL ] 297
+ 298 #SELECT 298
+ 299 # CASE WHEN 1 IN (SELECT MAX(col7) UNION ALL (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t2.col5 = t2.col1)) THEN 2 ELSE NULL END 299
+ 300 #FROM another_T t1; 300
+ 301 % . # table_name 301
+ 302 # .L26 # table_name 302
+ 303 % L # name 303
+ 304 # L26 # name 304
+ 305 % tinyint # type 305
+ 306 % 1 # length 306
+ 307 [ NULL ] 307
+ 308 [ NULL ] 308
+ 309 [ NULL ] 309
+ 310 [ NULL ] 310
+ 311 #SELECT 311
+ 312 # CASE WHEN 1 IN (SELECT (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t2.col5 = t2.col1) UNION ALL (SELECT MAX(col7))) THEN 2 ELSE NULL END 312
+ 313 #FROM another_T t1; 313
+ 314 % . # table_name 314
+ 315 # .L36 # table_name 315
+ 316 % L # name 316
+ 317 # L36 # name 317
+ 318 % tinyint # type 318
+ 319 % 1 # length 319
+ 320 [ NULL ] 320
+ 321 [ NULL ] 321
+ 322 [ NULL ] 322
+ 323 [ NULL ] 323
+ 324 #SELECT 324
+ 325 # CASE WHEN NOT col1 NOT IN (SELECT (SELECT MAX(col7)) UNION (SELECT MIN(ColID) FROM tbl_ProductSales LEFT JOIN another_T t2 ON t2.col5 = t1.col1)) THEN 1 ELSE 2 END 325
+ 326 #FROM another_T t1 326
+ 327 #GROUP BY col1; 327
+ 328 % sys. # table_name 328
+ 329 # sys.L25 # table_name 329
+ 330 % L # name 330
+ 331 # L25 # name 331
+ 332 % tinyint # type 332
+ 333 % 1 # length 333
+ 334 [ 1 ] 334
+ 335 [ 2 ] 335
+ 336 [ 2 ] 336
+ 337 [ 2 ] 337
+ 338 #SELECT 338
+ 339 # t1.col1 IN (SELECT ColID FROM tbl_ProductSales GROUP BY t1.col1, tbl_ProductSales.ColID) 339
+ 340 #FROM another_T t1 340
+ 341 #GROUP BY col1; 341
+ 342 % . # table_name 342
+ 343 # .L6 # table_name 343
+ 344 % L # name 344
+ 345 # L6 # name 345
+ 346 % boolean # type 346
+ 347 % 5 # length 347
+ 348 [ true ] 348
+ 349 [ false ] 349
+ 350 [ false ] 350
+ 351 [ false ] 351
+ 352 #SELECT t1.col1 FROM another_T t1 WHERE t1.col1 >= ANY(SELECT t1.col1 + t2.col1 FROM another_T t2); --empty result 352
+ 353 % sys.t1 # table_name 353
+ 354 % col1 # name 354
+ 355 % int # type 355
+ 356 % 1 # length 356
+ 357 #INSERT INTO tbl_ProductSales VALUES (0, 'a', 'b', 0); 357
+ 358 [ 1 ] 358
+ 359 #SELECT col1 IN (SELECT ColID + col1 FROM tbl_ProductSales) FROM another_T GROUP BY col1; 359
+ 360 % . # table_name 360
+ 361 # .L7 # table_name 361
+ 362 % L # name 362
+ 363 # L7 # name 363
+ 364 % boolean # type 364
+ 365 % 5 # length 365
+ 366 [ true ] 366
+ 367 [ true ] 367
+ 368 [ true ] 368
+ 369 [ true ] 369
+ 370 #SELECT col1 IN (SELECT col1 * SUM(ColID + col1) FROM tbl_ProductSales) FROM another_T GROUP BY col1; 370
+ 371 % . # table_name 371
+ 372 # .L13 # table_name 372
+ 373 % L # name 373
+ 374 # L13 # name 374
+ 375 % boolean # type 375
+ 376 % 5 # length 376
+ 377 [ false ] 377
+ 378 [ false ] 378
+ 379 [ false ] 379
+ 380 [ false ] 380
+ 381 #DROP TABLE tbl_ProductSales; 381
+ 382 #DROP TABLE another_T; 382
68 68 383 383