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