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