github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/hibernate (about) 1 # These are queries that test correlated subqueries, and are taken from the 2 # Hibernate test suite. See this issue: 3 # https://github.com/cockroachdb/cockroach/issues/26658 4 5 # ------------------------------------------------------------------------------ 6 # Query #1 7 # org.hibernate.userguide.collections.UnidirectionalMapTest testLifecycle 8 # ------------------------------------------------------------------------------ 9 exec-ddl 10 create table Person ( 11 id int8 not null, 12 primary key (id) 13 ) 14 ---- 15 16 exec-ddl 17 create table Phone ( 18 id int8 not null, 19 "number" varchar(255), 20 since timestamp, 21 type int4, 22 primary key (id) 23 ) 24 ---- 25 26 exec-ddl 27 create table phone_register ( 28 phone_id int8 not null, 29 person_id int8 not null, 30 primary key (phone_id, person_id), 31 foreign key (person_id) references Phone (id), 32 foreign key (phone_id) references Person (id), 33 unique (person_id) 34 ) 35 ---- 36 37 opt 38 select 39 phoneregis0_.phone_id as phone_id1_2_0_, 40 phoneregis0_.person_id as person_i2_2_0_, 41 ( 42 select a10.since 43 from Phone a10 44 where a10.id=phoneregis0_.person_id 45 ) as formula159_0_, 46 unidirecti1_.id as id1_1_1_, 47 unidirecti1_."number" as number2_1_1_, 48 unidirecti1_.since as since3_1_1_, 49 unidirecti1_.type as type4_1_1_ 50 from 51 phone_register phoneregis0_ 52 inner join Phone unidirecti1_ 53 on phoneregis0_.person_id=unidirecti1_.id 54 where phoneregis0_.phone_id=1; 55 ---- 56 project 57 ├── columns: phone_id1_2_0_:1!null person_i2_2_0_:2!null formula159_0_:11 id1_1_1_:3!null number2_1_1_:4 since3_1_1_:5 type4_1_1_:6 58 ├── key: (3) 59 ├── fd: ()-->(1), (3)-->(4-6), (2)==(3), (3)==(2), (2)-->(11) 60 ├── inner-join (lookup phone) 61 │ ├── columns: phone_id:1!null person_id:2!null unidirecti1_.id:3!null unidirecti1_.number:4 unidirecti1_.since:5 unidirecti1_.type:6 a10.id:7!null a10.since:9 62 │ ├── key columns: [2] = [7] 63 │ ├── lookup columns are key 64 │ ├── key: (7) 65 │ ├── fd: ()-->(1), (3)-->(4-6), (2)==(3,7), (3)==(2,7), (7)-->(9), (7)==(2,3) 66 │ ├── inner-join (lookup phone) 67 │ │ ├── columns: phone_id:1!null person_id:2!null unidirecti1_.id:3!null unidirecti1_.number:4 unidirecti1_.since:5 unidirecti1_.type:6 68 │ │ ├── key columns: [2] = [3] 69 │ │ ├── lookup columns are key 70 │ │ ├── key: (3) 71 │ │ ├── fd: ()-->(1), (3)-->(4-6), (2)==(3), (3)==(2) 72 │ │ ├── scan phoneregis0_ 73 │ │ │ ├── columns: phone_id:1!null person_id:2!null 74 │ │ │ ├── constraint: /1/2: [/1 - /1] 75 │ │ │ ├── key: (2) 76 │ │ │ └── fd: ()-->(1) 77 │ │ └── filters (true) 78 │ └── filters (true) 79 └── projections 80 └── a10.since:9 [as=formula159_0_:11, outer=(9)] 81 82 exec-ddl 83 drop table phone_register, Person, Phone; 84 ---- 85 86 # ------------------------------------------------------------------------------ 87 # Query #2 88 # org.hibernate.userguide.criteria.CriteriaTest: 89 # 90 # test_criteria_from_fetch_example 91 # test_criteria_from_join_example 92 # test_criteria_from_multiple_root_example 93 # ------------------------------------------------------------------------------ 94 exec-ddl 95 create table Person ( 96 id int8 not null, 97 address varchar(255), 98 createdOn timestamp, 99 name varchar(255), 100 nickName varchar(255), 101 version int4 not null, 102 primary key (id) 103 ) 104 ---- 105 106 exec-ddl 107 create table Person_addresses ( 108 Person_id int8 not null, 109 addresses varchar(255), 110 addresses_KEY varchar(255) not null, 111 primary key (Person_id, addresses_KEY) 112 ) 113 ---- 114 115 exec-ddl 116 create table Phone ( 117 id int8 not null, 118 phone_number varchar(255), 119 phone_type varchar(255), 120 person_id int8, 121 order_id int4, 122 primary key (id) 123 ) 124 ---- 125 126 exec-ddl 127 create table phone_call ( 128 id int8 not null, 129 duration int4 not null, 130 call_timestamp timestamp, 131 phone_id int8, 132 primary key (id) 133 ) 134 ---- 135 136 exec-ddl 137 create table Partner ( 138 id int8 not null, 139 name varchar(255), 140 version int4 not null, 141 primary key (id) 142 ) 143 ---- 144 145 opt 146 select 147 phone0_.id as id1_6_0_, 148 person1_.id as id1_4_1_, 149 phone0_.phone_number as phone_nu2_6_0_, 150 phone0_.person_id as person_i4_6_0_, 151 phone0_.phone_type as phone_ty3_6_0_, 152 addresses2_.Person_id as Person_i1_5_0__, 153 addresses2_.addresses as addresse2_5_0__, 154 addresses2_.addresses_KEY as addresse3_0__, 155 person1_.address as address2_4_1_, 156 person1_.createdOn as createdO3_4_1_, 157 person1_.name as name4_4_1_, 158 person1_.nickName as nickName5_4_1_, 159 person1_.version as version6_4_1_, 160 addresses2_.Person_id as Person_i1_5_0__, 161 addresses2_.addresses as addresse2_5_0__, 162 addresses2_.addresses_KEY as addresse3_0__ 163 from 164 Phone phone0_ 165 inner join 166 Person person1_ 167 on phone0_.person_id=person1_.id 168 inner join 169 Person_addresses addresses2_ 170 on person1_.id=addresses2_.Person_id 171 where 172 exists ( 173 select 174 calls3_.id 175 from 176 phone_call calls3_ 177 where 178 phone0_.id=calls3_.phone_id 179 ) 180 ---- 181 inner-join (hash) 182 ├── columns: id1_6_0_:1!null id1_4_1_:6!null phone_nu2_6_0_:2 person_i4_6_0_:4!null phone_ty3_6_0_:3 person_i1_5_0__:12!null addresse2_5_0__:13 addresse3_0__:14!null address2_4_1_:7 createdo3_4_1_:8 name4_4_1_:9 nickname5_4_1_:10 version6_4_1_:11!null person_i1_5_0__:12!null addresse2_5_0__:13 addresse3_0__:14!null 183 ├── key: (1,14) 184 ├── fd: (1)-->(2-4), (6)-->(7-11), (4)==(6,12), (6)==(4,12), (12,14)-->(13), (12)==(4,6) 185 ├── scan addresses2_ 186 │ ├── columns: addresses2_.person_id:12!null addresses:13 addresses_key:14!null 187 │ ├── key: (12,14) 188 │ └── fd: (12,14)-->(13) 189 ├── inner-join (lookup person) 190 │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4!null person1_.id:6!null address:7 createdon:8 name:9 nickname:10 version:11!null 191 │ ├── key columns: [4] = [6] 192 │ ├── lookup columns are key 193 │ ├── key: (1) 194 │ ├── fd: (1)-->(2-4), (6)-->(7-11), (4)==(6), (6)==(4) 195 │ ├── project 196 │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4 197 │ │ ├── key: (1) 198 │ │ ├── fd: (1)-->(2-4) 199 │ │ └── inner-join (lookup phone) 200 │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4 phone_id:18!null 201 │ │ ├── key columns: [18] = [1] 202 │ │ ├── lookup columns are key 203 │ │ ├── key: (18) 204 │ │ ├── fd: (1)-->(2-4), (1)==(18), (18)==(1) 205 │ │ ├── distinct-on 206 │ │ │ ├── columns: phone_id:18 207 │ │ │ ├── grouping columns: phone_id:18 208 │ │ │ ├── key: (18) 209 │ │ │ └── scan calls3_ 210 │ │ │ └── columns: phone_id:18 211 │ │ └── filters (true) 212 │ └── filters (true) 213 └── filters 214 └── person1_.id:6 = addresses2_.person_id:12 [outer=(6,12), constraints=(/6: (/NULL - ]; /12: (/NULL - ]), fd=(6)==(12), (12)==(6)] 215 216 opt 217 select 218 phone0_.id as id1_6_, 219 phone0_.phone_number as phone_nu2_6_, 220 phone0_.person_id as person_i4_6_, 221 phone0_.phone_type as phone_ty3_6_ 222 from 223 Phone phone0_ 224 inner join 225 Person person1_ 226 on phone0_.person_id=person1_.id 227 inner join 228 Person_addresses addresses2_ 229 on person1_.id=addresses2_.Person_id 230 where 231 exists ( 232 select 233 calls3_.id 234 from 235 phone_call calls3_ 236 where 237 phone0_.id=calls3_.phone_id 238 ) 239 ---- 240 project 241 ├── columns: id1_6_:1!null phone_nu2_6_:2 person_i4_6_:4!null phone_ty3_6_:3 242 ├── fd: (1)-->(2-4) 243 └── inner-join (hash) 244 ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4!null person1_.id:6!null addresses2_.person_id:12!null 245 ├── fd: (1)-->(2-4), (4)==(6,12), (6)==(4,12), (12)==(4,6) 246 ├── scan addresses2_ 247 │ └── columns: addresses2_.person_id:12!null 248 ├── inner-join (lookup person) 249 │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4!null person1_.id:6!null 250 │ ├── key columns: [4] = [6] 251 │ ├── lookup columns are key 252 │ ├── key: (1) 253 │ ├── fd: (1)-->(2-4), (4)==(6), (6)==(4) 254 │ ├── project 255 │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4 256 │ │ ├── key: (1) 257 │ │ ├── fd: (1)-->(2-4) 258 │ │ └── inner-join (lookup phone) 259 │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 phone0_.person_id:4 phone_id:18!null 260 │ │ ├── key columns: [18] = [1] 261 │ │ ├── lookup columns are key 262 │ │ ├── key: (18) 263 │ │ ├── fd: (1)-->(2-4), (1)==(18), (18)==(1) 264 │ │ ├── distinct-on 265 │ │ │ ├── columns: phone_id:18 266 │ │ │ ├── grouping columns: phone_id:18 267 │ │ │ ├── key: (18) 268 │ │ │ └── scan calls3_ 269 │ │ │ └── columns: phone_id:18 270 │ │ └── filters (true) 271 │ └── filters (true) 272 └── filters 273 └── person1_.id:6 = addresses2_.person_id:12 [outer=(6,12), constraints=(/6: (/NULL - ]; /12: (/NULL - ]), fd=(6)==(12), (12)==(6)] 274 275 opt 276 select 277 person0_.id as id1_4_0_, 278 partner1_.id as id1_2_1_, 279 person0_.address as address2_4_0_, 280 person0_.createdOn as createdO3_4_0_, 281 person0_.name as name4_4_0_, 282 person0_.nickName as nickName5_4_0_, 283 person0_.version as version6_4_0_, 284 partner1_.name as name2_2_1_, 285 partner1_.version as version3_2_1_ 286 from 287 Person person0_ cross 288 join 289 Partner partner1_ 290 where 291 person0_.address=$1 292 and ( 293 exists ( 294 select 295 phones2_.id 296 from 297 Phone phones2_ 298 where 299 person0_.id=phones2_.person_id 300 ) 301 ) 302 and ( 303 partner1_.name like $2 304 ) 305 and partner1_.version=0 306 ---- 307 inner-join (cross) 308 ├── columns: id1_4_0_:1!null id1_2_1_:7!null address2_4_0_:2!null createdo3_4_0_:3 name4_4_0_:4 nickname5_4_0_:5 version6_4_0_:6!null name2_2_1_:8!null version3_2_1_:9!null 309 ├── has-placeholder 310 ├── key: (1,7) 311 ├── fd: ()-->(9), (1)-->(2-6), (7)-->(8) 312 ├── project 313 │ ├── columns: person0_.id:1!null address:2!null createdon:3 person0_.name:4 nickname:5 person0_.version:6!null 314 │ ├── has-placeholder 315 │ ├── key: (1) 316 │ ├── fd: (1)-->(2-6) 317 │ └── inner-join (lookup person) 318 │ ├── columns: person0_.id:1!null address:2!null createdon:3 person0_.name:4 nickname:5 person0_.version:6!null person_id:13!null 319 │ ├── key columns: [13] = [1] 320 │ ├── lookup columns are key 321 │ ├── has-placeholder 322 │ ├── key: (13) 323 │ ├── fd: (1)-->(2-6), (1)==(13), (13)==(1) 324 │ ├── distinct-on 325 │ │ ├── columns: person_id:13 326 │ │ ├── grouping columns: person_id:13 327 │ │ ├── key: (13) 328 │ │ └── scan phones2_ 329 │ │ └── columns: person_id:13 330 │ └── filters 331 │ └── address:2 = $1 [outer=(2), constraints=(/2: (/NULL - ])] 332 ├── select 333 │ ├── columns: partner1_.id:7!null partner1_.name:8!null partner1_.version:9!null 334 │ ├── has-placeholder 335 │ ├── key: (7) 336 │ ├── fd: ()-->(9), (7)-->(8) 337 │ ├── scan partner1_ 338 │ │ ├── columns: partner1_.id:7!null partner1_.name:8 partner1_.version:9!null 339 │ │ ├── key: (7) 340 │ │ └── fd: (7)-->(8,9) 341 │ └── filters 342 │ ├── partner1_.name:8 LIKE $2 [outer=(8), constraints=(/8: (/NULL - ])] 343 │ └── partner1_.version:9 = 0 [outer=(9), constraints=(/9: [/0 - /0]; tight), fd=()-->(9)] 344 └── filters (true) 345 346 exec-ddl 347 drop table Person, Person_addresses, Phone, phone_call, Partner; 348 ---- 349 350 # ------------------------------------------------------------------------------ 351 # Query #3 352 # org.hibernate.userguide.envers.DefaultAuditTest test 353 # ------------------------------------------------------------------------------ 354 exec-ddl 355 create table Customer_AUD ( 356 id int8 not null, 357 REV int4 not null, 358 REVTYPE int2, 359 created_on timestamp, 360 firstName varchar(255), 361 lastName varchar(255), 362 primary key (id, REV) 363 ) 364 ---- 365 366 opt 367 select 368 defaultaud0_.id as id1_1_, 369 defaultaud0_.REV as REV2_1_, 370 defaultaud0_.REVTYPE as REVTYPE3_1_, 371 defaultaud0_.created_on as created_4_1_, 372 defaultaud0_.firstName as firstNam5_1_, 373 defaultaud0_.lastName as lastName6_1_ 374 from 375 Customer_AUD defaultaud0_ 376 where 377 defaultaud0_.REV=( 378 select 379 max(defaultaud1_.REV) 380 from 381 Customer_AUD defaultaud1_ 382 where 383 defaultaud1_.REV<=$1 384 and defaultaud0_.id=defaultaud1_.id 385 ) 386 and defaultaud0_.REVTYPE<>$2 387 ---- 388 project 389 ├── columns: id1_1_:1!null rev2_1_:2!null revtype3_1_:3!null created_4_1_:4 firstnam5_1_:5 lastname6_1_:6 390 ├── has-placeholder 391 ├── key: (1,2) 392 ├── fd: (1,2)-->(3-6) 393 └── select 394 ├── columns: defaultaud0_.id:1!null defaultaud0_.rev:2!null defaultaud0_.revtype:3!null defaultaud0_.created_on:4 defaultaud0_.firstname:5 defaultaud0_.lastname:6 max:13!null 395 ├── has-placeholder 396 ├── key: (1,2) 397 ├── fd: (1,2)-->(3-6,13), (2)==(13), (13)==(2) 398 ├── group-by 399 │ ├── columns: defaultaud0_.id:1!null defaultaud0_.rev:2!null defaultaud0_.revtype:3!null defaultaud0_.created_on:4 defaultaud0_.firstname:5 defaultaud0_.lastname:6 max:13!null 400 │ ├── grouping columns: defaultaud0_.id:1!null defaultaud0_.rev:2!null 401 │ ├── has-placeholder 402 │ ├── key: (1,2) 403 │ ├── fd: (1,2)-->(3-6,13) 404 │ ├── inner-join (merge) 405 │ │ ├── columns: defaultaud0_.id:1!null defaultaud0_.rev:2!null defaultaud0_.revtype:3!null defaultaud0_.created_on:4 defaultaud0_.firstname:5 defaultaud0_.lastname:6 defaultaud1_.id:7!null defaultaud1_.rev:8!null 406 │ │ ├── left ordering: +1 407 │ │ ├── right ordering: +7 408 │ │ ├── has-placeholder 409 │ │ ├── key: (2,7,8) 410 │ │ ├── fd: (1,2)-->(3-6), (1)==(7), (7)==(1) 411 │ │ ├── select 412 │ │ │ ├── columns: defaultaud0_.id:1!null defaultaud0_.rev:2!null defaultaud0_.revtype:3!null defaultaud0_.created_on:4 defaultaud0_.firstname:5 defaultaud0_.lastname:6 413 │ │ │ ├── has-placeholder 414 │ │ │ ├── key: (1,2) 415 │ │ │ ├── fd: (1,2)-->(3-6) 416 │ │ │ ├── ordering: +1 417 │ │ │ ├── scan defaultaud0_ 418 │ │ │ │ ├── columns: defaultaud0_.id:1!null defaultaud0_.rev:2!null defaultaud0_.revtype:3 defaultaud0_.created_on:4 defaultaud0_.firstname:5 defaultaud0_.lastname:6 419 │ │ │ │ ├── key: (1,2) 420 │ │ │ │ ├── fd: (1,2)-->(3-6) 421 │ │ │ │ └── ordering: +1 422 │ │ │ └── filters 423 │ │ │ └── defaultaud0_.revtype:3 != $2 [outer=(3), constraints=(/3: (/NULL - ])] 424 │ │ ├── select 425 │ │ │ ├── columns: defaultaud1_.id:7!null defaultaud1_.rev:8!null 426 │ │ │ ├── has-placeholder 427 │ │ │ ├── key: (7,8) 428 │ │ │ ├── ordering: +7 429 │ │ │ ├── scan defaultaud1_ 430 │ │ │ │ ├── columns: defaultaud1_.id:7!null defaultaud1_.rev:8!null 431 │ │ │ │ ├── key: (7,8) 432 │ │ │ │ └── ordering: +7 433 │ │ │ └── filters 434 │ │ │ └── defaultaud1_.rev:8 <= $1 [outer=(8), constraints=(/8: (/NULL - ])] 435 │ │ └── filters (true) 436 │ └── aggregations 437 │ ├── max [as=max:13, outer=(8)] 438 │ │ └── defaultaud1_.rev:8 439 │ ├── const-agg [as=defaultaud0_.revtype:3, outer=(3)] 440 │ │ └── defaultaud0_.revtype:3 441 │ ├── const-agg [as=defaultaud0_.created_on:4, outer=(4)] 442 │ │ └── defaultaud0_.created_on:4 443 │ ├── const-agg [as=defaultaud0_.firstname:5, outer=(5)] 444 │ │ └── defaultaud0_.firstname:5 445 │ └── const-agg [as=defaultaud0_.lastname:6, outer=(6)] 446 │ └── defaultaud0_.lastname:6 447 └── filters 448 └── defaultaud0_.rev:2 = max:13 [outer=(2,13), constraints=(/2: (/NULL - ]; /13: (/NULL - ]), fd=(2)==(13), (13)==(2)] 449 450 exec-ddl 451 drop table Customer_AUD; 452 ---- 453 454 # ------------------------------------------------------------------------------ 455 # Query #4 456 # org.hibernate.userguide.envers.QueryAuditTest test 457 # ------------------------------------------------------------------------------ 458 exec-ddl 459 create table Customer_AUD ( 460 id int8 not null, 461 REV int4 not null, 462 REVTYPE int2, 463 REVEND int4, 464 created_on timestamp, 465 firstName varchar(255), 466 lastName varchar(255), 467 address_id int8, 468 primary key (id, REV) 469 ) 470 ---- 471 472 opt 473 select 474 queryaudit0_.id as id1_3_, 475 queryaudit0_.REV as REV2_3_, 476 queryaudit0_.REVTYPE as REVTYPE3_3_, 477 queryaudit0_.REVEND as REVEND4_3_, 478 queryaudit0_.created_on as created_5_3_, 479 queryaudit0_.firstName as firstNam6_3_, 480 queryaudit0_.lastName as lastName7_3_, 481 queryaudit0_.address_id as address_8_3_ 482 from 483 Customer_AUD queryaudit0_ 484 where 485 queryaudit0_.REVTYPE<>$1 486 and queryaudit0_.REV=( 487 select 488 max(queryaudit1_.REV) 489 from 490 Customer_AUD queryaudit1_ 491 where 492 queryaudit1_.id=queryaudit0_.id 493 ) 494 order by 495 queryaudit0_.REV asc 496 ---- 497 sort 498 ├── columns: id1_3_:1!null rev2_3_:2!null revtype3_3_:3!null revend4_3_:4 created_5_3_:5 firstnam6_3_:6 lastname7_3_:7 address_8_3_:8 499 ├── has-placeholder 500 ├── key: (1,2) 501 ├── fd: (1,2)-->(3-8) 502 ├── ordering: +2 503 └── project 504 ├── columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null queryaudit0_.revtype:3!null queryaudit0_.revend:4 queryaudit0_.created_on:5 queryaudit0_.firstname:6 queryaudit0_.lastname:7 queryaudit0_.address_id:8 505 ├── has-placeholder 506 ├── key: (1,2) 507 ├── fd: (1,2)-->(3-8) 508 └── select 509 ├── columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null queryaudit0_.revtype:3!null queryaudit0_.revend:4 queryaudit0_.created_on:5 queryaudit0_.firstname:6 queryaudit0_.lastname:7 queryaudit0_.address_id:8 max:17!null 510 ├── has-placeholder 511 ├── key: (1,2) 512 ├── fd: (1,2)-->(3-8,17), (2)==(17), (17)==(2) 513 ├── group-by 514 │ ├── columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null queryaudit0_.revtype:3!null queryaudit0_.revend:4 queryaudit0_.created_on:5 queryaudit0_.firstname:6 queryaudit0_.lastname:7 queryaudit0_.address_id:8 max:17!null 515 │ ├── grouping columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null 516 │ ├── has-placeholder 517 │ ├── key: (1,2) 518 │ ├── fd: (1,2)-->(3-8,17) 519 │ ├── inner-join (merge) 520 │ │ ├── columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null queryaudit0_.revtype:3!null queryaudit0_.revend:4 queryaudit0_.created_on:5 queryaudit0_.firstname:6 queryaudit0_.lastname:7 queryaudit0_.address_id:8 queryaudit1_.id:9!null queryaudit1_.rev:10!null 521 │ │ ├── left ordering: +1 522 │ │ ├── right ordering: +9 523 │ │ ├── has-placeholder 524 │ │ ├── key: (2,9,10) 525 │ │ ├── fd: (1,2)-->(3-8), (1)==(9), (9)==(1) 526 │ │ ├── select 527 │ │ │ ├── columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null queryaudit0_.revtype:3!null queryaudit0_.revend:4 queryaudit0_.created_on:5 queryaudit0_.firstname:6 queryaudit0_.lastname:7 queryaudit0_.address_id:8 528 │ │ │ ├── has-placeholder 529 │ │ │ ├── key: (1,2) 530 │ │ │ ├── fd: (1,2)-->(3-8) 531 │ │ │ ├── ordering: +1 532 │ │ │ ├── scan queryaudit0_ 533 │ │ │ │ ├── columns: queryaudit0_.id:1!null queryaudit0_.rev:2!null queryaudit0_.revtype:3 queryaudit0_.revend:4 queryaudit0_.created_on:5 queryaudit0_.firstname:6 queryaudit0_.lastname:7 queryaudit0_.address_id:8 534 │ │ │ │ ├── key: (1,2) 535 │ │ │ │ ├── fd: (1,2)-->(3-8) 536 │ │ │ │ └── ordering: +1 537 │ │ │ └── filters 538 │ │ │ └── queryaudit0_.revtype:3 != $1 [outer=(3), constraints=(/3: (/NULL - ])] 539 │ │ ├── scan queryaudit1_ 540 │ │ │ ├── columns: queryaudit1_.id:9!null queryaudit1_.rev:10!null 541 │ │ │ ├── key: (9,10) 542 │ │ │ └── ordering: +9 543 │ │ └── filters (true) 544 │ └── aggregations 545 │ ├── max [as=max:17, outer=(10)] 546 │ │ └── queryaudit1_.rev:10 547 │ ├── const-agg [as=queryaudit0_.revtype:3, outer=(3)] 548 │ │ └── queryaudit0_.revtype:3 549 │ ├── const-agg [as=queryaudit0_.revend:4, outer=(4)] 550 │ │ └── queryaudit0_.revend:4 551 │ ├── const-agg [as=queryaudit0_.created_on:5, outer=(5)] 552 │ │ └── queryaudit0_.created_on:5 553 │ ├── const-agg [as=queryaudit0_.firstname:6, outer=(6)] 554 │ │ └── queryaudit0_.firstname:6 555 │ ├── const-agg [as=queryaudit0_.lastname:7, outer=(7)] 556 │ │ └── queryaudit0_.lastname:7 557 │ └── const-agg [as=queryaudit0_.address_id:8, outer=(8)] 558 │ └── queryaudit0_.address_id:8 559 └── filters 560 └── queryaudit0_.rev:2 = max:17 [outer=(2,17), constraints=(/2: (/NULL - ]; /17: (/NULL - ]), fd=(2)==(17), (17)==(2)] 561 562 exec-ddl 563 drop table Customer_AUD; 564 ---- 565 566 # ------------------------------------------------------------------------------ 567 # Query #5 568 # org.hibernate.userguide.hql.HQLTest 569 # test_hql_all_subquery_comparison_qualifier_example 570 # test_hql_collection_expressions_example_1 571 # test_hql_collection_expressions_example_10 572 # test_hql_collection_expressions_example_2 573 # test_hql_collection_expressions_example_3 574 # test_hql_collection_expressions_example_4 575 # test_hql_collection_expressions_example_5 576 # test_hql_collection_expressions_example_6 577 # test_hql_collection_expressions_example_8 578 # test_hql_collection_expressions_example_9 579 # test_hql_collection_index_operator_example_3 580 # test_hql_empty_collection_predicate_example_1 581 # test_hql_empty_collection_predicate_example_2 582 # test_hql_group_by_example_4 583 # test_hql_member_of_collection_predicate_example_1 584 # test_hql_member_of_collection_predicate_example_2 585 # org.hibernate.jpa.test.criteria.enumcollection.EnumIsMemberTest 586 # testQueryEnumCollection 587 # ------------------------------------------------------------------------------ 588 exec-ddl 589 create table Phone ( 590 id int8 not null, 591 phone_number varchar(255), 592 phone_type varchar(255), 593 person_id int8, 594 order_id int4, 595 primary key (id) 596 ) 597 ---- 598 599 exec-ddl 600 create table phone_call ( 601 id int8 not null, 602 duration int4 not null, 603 call_timestamp timestamp, 604 phone_id int8, 605 primary key (id) 606 ) 607 ---- 608 609 exec-ddl 610 create table Person ( 611 id int8 not null, 612 address varchar(255), 613 createdOn timestamp, 614 name varchar(255), 615 nickName varchar(255), 616 version int4 not null, 617 primary key (id) 618 ) 619 ---- 620 621 exec-ddl 622 create table Phone_repairTimestamps ( 623 Phone_id int8 not null, 624 repairTimestamps timestamp 625 ) 626 ---- 627 628 exec-ddl 629 create table Person_addresses ( 630 Person_id int8 not null, 631 addresses varchar(255), 632 addresses_KEY varchar(255) not null, 633 primary key (Person_id, addresses_KEY) 634 ) 635 ---- 636 637 opt 638 select 639 distinct person2_.id as id1_2_, 640 person2_.address as address2_2_, 641 person2_.createdOn as createdO3_2_, 642 person2_.name as name4_2_, 643 person2_.nickName as nickName5_2_, 644 person2_.version as version6_2_ 645 from 646 Phone phone0_ 647 inner join 648 phone_call calls1_ 649 on phone0_.id=calls1_.phone_id 650 inner join 651 Person person2_ 652 on phone0_.person_id=person2_.id 653 where 654 50>all ( 655 select 656 call3_.duration 657 from 658 phone_call call3_ 659 where 660 call3_.phone_id=phone0_.id 661 ) 662 ---- 663 distinct-on 664 ├── columns: id1_2_:10!null address2_2_:11 createdo3_2_:12 name4_2_:13 nickname5_2_:14 version6_2_:15!null 665 ├── grouping columns: person2_.id:10!null 666 ├── key: (10) 667 ├── fd: (10)-->(11-15) 668 ├── inner-join (hash) 669 │ ├── columns: phone0_.id:1!null person_id:4!null calls1_.phone_id:9!null person2_.id:10!null address:11 createdon:12 name:13 nickname:14 version:15!null 670 │ ├── fd: (1)-->(4), (1)==(9), (9)==(1), (10)-->(11-15), (4)==(10), (10)==(4) 671 │ ├── scan calls1_ 672 │ │ └── columns: calls1_.phone_id:9 673 │ ├── inner-join (hash) 674 │ │ ├── columns: phone0_.id:1!null person_id:4!null person2_.id:10!null address:11 createdon:12 name:13 nickname:14 version:15!null 675 │ │ ├── key: (1) 676 │ │ ├── fd: (1)-->(4), (10)-->(11-15), (4)==(10), (10)==(4) 677 │ │ ├── scan person2_ 678 │ │ │ ├── columns: person2_.id:10!null address:11 createdon:12 name:13 nickname:14 version:15!null 679 │ │ │ ├── key: (10) 680 │ │ │ └── fd: (10)-->(11-15) 681 │ │ ├── anti-join (hash) 682 │ │ │ ├── columns: phone0_.id:1!null person_id:4 683 │ │ │ ├── key: (1) 684 │ │ │ ├── fd: (1)-->(4) 685 │ │ │ ├── scan phone0_ 686 │ │ │ │ ├── columns: phone0_.id:1!null person_id:4 687 │ │ │ │ ├── key: (1) 688 │ │ │ │ └── fd: (1)-->(4) 689 │ │ │ ├── select 690 │ │ │ │ ├── columns: call3_.duration:17!null call3_.phone_id:19 691 │ │ │ │ ├── scan call3_ 692 │ │ │ │ │ └── columns: call3_.duration:17!null call3_.phone_id:19 693 │ │ │ │ └── filters 694 │ │ │ │ └── (call3_.duration:17 >= 50) IS NOT false [outer=(17)] 695 │ │ │ └── filters 696 │ │ │ └── call3_.phone_id:19 = phone0_.id:1 [outer=(1,19), constraints=(/1: (/NULL - ]; /19: (/NULL - ]), fd=(1)==(19), (19)==(1)] 697 │ │ └── filters 698 │ │ └── person_id:4 = person2_.id:10 [outer=(4,10), constraints=(/4: (/NULL - ]; /10: (/NULL - ]), fd=(4)==(10), (10)==(4)] 699 │ └── filters 700 │ └── phone0_.id:1 = calls1_.phone_id:9 [outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 701 └── aggregations 702 ├── const-agg [as=address:11, outer=(11)] 703 │ └── address:11 704 ├── const-agg [as=createdon:12, outer=(12)] 705 │ └── createdon:12 706 ├── const-agg [as=name:13, outer=(13)] 707 │ └── name:13 708 ├── const-agg [as=nickname:14, outer=(14)] 709 │ └── nickname:14 710 └── const-agg [as=version:15, outer=(15)] 711 └── version:15 712 713 opt 714 select 715 phone0_.id as id1_4_, 716 phone0_.phone_number as phone_nu2_4_, 717 phone0_.person_id as person_i4_4_, 718 phone0_.phone_type as phone_ty3_4_ 719 from 720 Phone phone0_ 721 where 722 ( 723 select 724 max(calls1_.id) 725 from 726 phone_call calls1_ 727 where 728 phone0_.id=calls1_.phone_id 729 )=$1 730 ---- 731 project 732 ├── columns: id1_4_:1!null phone_nu2_4_:2 person_i4_4_:4 phone_ty3_4_:3 733 ├── has-placeholder 734 ├── key: (1) 735 ├── fd: (1)-->(2-4) 736 └── select 737 ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 max:10!null 738 ├── has-placeholder 739 ├── key: (1) 740 ├── fd: (1)-->(2-4,10) 741 ├── group-by 742 │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 max:10!null 743 │ ├── grouping columns: phone0_.id:1!null 744 │ ├── key: (1) 745 │ ├── fd: (1)-->(2-4,10) 746 │ ├── inner-join (hash) 747 │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 calls1_.id:6!null phone_id:9!null 748 │ │ ├── key: (6) 749 │ │ ├── fd: (1)-->(2-4), (6)-->(9), (1)==(9), (9)==(1) 750 │ │ ├── scan phone0_ 751 │ │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 752 │ │ │ ├── key: (1) 753 │ │ │ └── fd: (1)-->(2-4) 754 │ │ ├── scan calls1_ 755 │ │ │ ├── columns: calls1_.id:6!null phone_id:9 756 │ │ │ ├── key: (6) 757 │ │ │ └── fd: (6)-->(9) 758 │ │ └── filters 759 │ │ └── phone0_.id:1 = phone_id:9 [outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 760 │ └── aggregations 761 │ ├── max [as=max:10, outer=(6)] 762 │ │ └── calls1_.id:6 763 │ ├── const-agg [as=phone_number:2, outer=(2)] 764 │ │ └── phone_number:2 765 │ ├── const-agg [as=phone_type:3, outer=(3)] 766 │ │ └── phone_type:3 767 │ └── const-agg [as=person_id:4, outer=(4)] 768 │ └── person_id:4 769 └── filters 770 └── max:10 = $1 [outer=(10), constraints=(/10: (/NULL - ])] 771 772 opt 773 select 774 person0_.id as id1_2_, 775 person0_.address as address2_2_, 776 person0_.createdOn as createdO3_2_, 777 person0_.name as name4_2_, 778 person0_.nickName as nickName5_2_, 779 person0_.version as version6_2_ 780 from 781 Person person0_ 782 where 783 ( 784 select 785 count(phones1_.person_id) 786 from 787 Phone phones1_ 788 where 789 person0_.id=phones1_.person_id 790 )=2 791 ---- 792 project 793 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 794 ├── key: (1) 795 ├── fd: (1)-->(2-6) 796 └── select 797 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null count:12!null 798 ├── key: (1) 799 ├── fd: ()-->(12), (1)-->(2-6) 800 ├── group-by 801 │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null count:12!null 802 │ ├── grouping columns: person0_.id:1!null 803 │ ├── key: (1) 804 │ ├── fd: (1)-->(2-6,12) 805 │ ├── left-join (hash) 806 │ │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10 807 │ │ ├── fd: (1)-->(2-6) 808 │ │ ├── scan person0_ 809 │ │ │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null 810 │ │ │ ├── key: (1) 811 │ │ │ └── fd: (1)-->(2-6) 812 │ │ ├── scan phones1_ 813 │ │ │ └── columns: person_id:10 814 │ │ └── filters 815 │ │ └── person0_.id:1 = person_id:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 816 │ └── aggregations 817 │ ├── count [as=count:12, outer=(10)] 818 │ │ └── person_id:10 819 │ ├── const-agg [as=address:2, outer=(2)] 820 │ │ └── address:2 821 │ ├── const-agg [as=createdon:3, outer=(3)] 822 │ │ └── createdon:3 823 │ ├── const-agg [as=name:4, outer=(4)] 824 │ │ └── name:4 825 │ ├── const-agg [as=nickname:5, outer=(5)] 826 │ │ └── nickname:5 827 │ └── const-agg [as=version:6, outer=(6)] 828 │ └── version:6 829 └── filters 830 └── count:12 = 2 [outer=(12), constraints=(/12: [/2 - /2]; tight), fd=()-->(12)] 831 832 opt 833 select 834 phone0_.id as id1_4_, 835 phone0_.phone_number as phone_nu2_4_, 836 phone0_.person_id as person_i4_4_, 837 phone0_.phone_type as phone_ty3_4_ 838 from 839 Phone phone0_ 840 where 841 ( 842 select 843 min(calls1_.id) 844 from 845 phone_call calls1_ 846 where 847 phone0_.id=calls1_.phone_id 848 )=$1 849 ---- 850 project 851 ├── columns: id1_4_:1!null phone_nu2_4_:2 person_i4_4_:4 phone_ty3_4_:3 852 ├── has-placeholder 853 ├── key: (1) 854 ├── fd: (1)-->(2-4) 855 └── select 856 ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 min:10!null 857 ├── has-placeholder 858 ├── key: (1) 859 ├── fd: (1)-->(2-4,10) 860 ├── group-by 861 │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 min:10!null 862 │ ├── grouping columns: phone0_.id:1!null 863 │ ├── key: (1) 864 │ ├── fd: (1)-->(2-4,10) 865 │ ├── inner-join (hash) 866 │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 calls1_.id:6!null phone_id:9!null 867 │ │ ├── key: (6) 868 │ │ ├── fd: (1)-->(2-4), (6)-->(9), (1)==(9), (9)==(1) 869 │ │ ├── scan phone0_ 870 │ │ │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 871 │ │ │ ├── key: (1) 872 │ │ │ └── fd: (1)-->(2-4) 873 │ │ ├── scan calls1_ 874 │ │ │ ├── columns: calls1_.id:6!null phone_id:9 875 │ │ │ ├── key: (6) 876 │ │ │ └── fd: (6)-->(9) 877 │ │ └── filters 878 │ │ └── phone0_.id:1 = phone_id:9 [outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 879 │ └── aggregations 880 │ ├── min [as=min:10, outer=(6)] 881 │ │ └── calls1_.id:6 882 │ ├── const-agg [as=phone_number:2, outer=(2)] 883 │ │ └── phone_number:2 884 │ ├── const-agg [as=phone_type:3, outer=(3)] 885 │ │ └── phone_type:3 886 │ └── const-agg [as=person_id:4, outer=(4)] 887 │ └── person_id:4 888 └── filters 889 └── min:10 = $1 [outer=(10), constraints=(/10: (/NULL - ])] 890 891 opt 892 select 893 person0_.id as id1_2_, 894 person0_.address as address2_2_, 895 person0_.createdOn as createdO3_2_, 896 person0_.name as name4_2_, 897 person0_.nickName as nickName5_2_, 898 person0_.version as version6_2_ 899 from 900 Person person0_ 901 where 902 ( 903 select 904 max(phones1_.order_id) 905 from 906 Phone phones1_ 907 where 908 person0_.id=phones1_.person_id 909 )=0 910 ---- 911 project 912 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 913 ├── key: (1) 914 ├── fd: (1)-->(2-6) 915 └── select 916 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null max:12!null 917 ├── key: (1) 918 ├── fd: ()-->(12), (1)-->(2-6) 919 ├── group-by 920 │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null max:12!null 921 │ ├── grouping columns: person0_.id:1!null 922 │ ├── key: (1) 923 │ ├── fd: (1)-->(2-6,12) 924 │ ├── inner-join (hash) 925 │ │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10!null order_id:11!null 926 │ │ ├── fd: (1)-->(2-6), (1)==(10), (10)==(1) 927 │ │ ├── scan person0_ 928 │ │ │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null 929 │ │ │ ├── key: (1) 930 │ │ │ └── fd: (1)-->(2-6) 931 │ │ ├── select 932 │ │ │ ├── columns: person_id:10 order_id:11!null 933 │ │ │ ├── scan phones1_ 934 │ │ │ │ └── columns: person_id:10 order_id:11 935 │ │ │ └── filters 936 │ │ │ └── order_id:11 IS NOT NULL [outer=(11), constraints=(/11: (/NULL - ]; tight)] 937 │ │ └── filters 938 │ │ └── person0_.id:1 = person_id:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 939 │ └── aggregations 940 │ ├── max [as=max:12, outer=(11)] 941 │ │ └── order_id:11 942 │ ├── const-agg [as=address:2, outer=(2)] 943 │ │ └── address:2 944 │ ├── const-agg [as=createdon:3, outer=(3)] 945 │ │ └── createdon:3 946 │ ├── const-agg [as=name:4, outer=(4)] 947 │ │ └── name:4 948 │ ├── const-agg [as=nickname:5, outer=(5)] 949 │ │ └── nickname:5 950 │ └── const-agg [as=version:6, outer=(6)] 951 │ └── version:6 952 └── filters 953 └── max:12 = 0 [outer=(12), constraints=(/12: [/0 - /0]; tight), fd=()-->(12)] 954 955 opt 956 select 957 person0_.id as id1_2_, 958 person0_.address as address2_2_, 959 person0_.createdOn as createdO3_2_, 960 person0_.name as name4_2_, 961 person0_.nickName as nickName5_2_, 962 person0_.version as version6_2_ 963 from 964 Person person0_ 965 where 966 $1::int in ( 967 select 968 phones1_.id 969 from 970 Phone phones1_ 971 where 972 person0_.id=phones1_.person_id 973 ) 974 ---- 975 project 976 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 977 ├── has-placeholder 978 ├── key: (1) 979 ├── fd: (1)-->(2-6) 980 └── inner-join (lookup person) 981 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10!null 982 ├── key columns: [10] = [1] 983 ├── lookup columns are key 984 ├── has-placeholder 985 ├── key: (10) 986 ├── fd: (1)-->(2-6), (1)==(10), (10)==(1) 987 ├── distinct-on 988 │ ├── columns: person_id:10 989 │ ├── grouping columns: person_id:10 990 │ ├── has-placeholder 991 │ ├── key: (10) 992 │ └── select 993 │ ├── columns: phones1_.id:7!null person_id:10 994 │ ├── has-placeholder 995 │ ├── key: (7) 996 │ ├── fd: (7)-->(10) 997 │ ├── scan phones1_ 998 │ │ ├── columns: phones1_.id:7!null person_id:10 999 │ │ ├── key: (7) 1000 │ │ └── fd: (7)-->(10) 1001 │ └── filters 1002 │ └── phones1_.id:7 = $1::INT8 [outer=(7), constraints=(/7: (/NULL - ])] 1003 └── filters (true) 1004 1005 opt 1006 select 1007 person0_.id as id1_2_, 1008 person0_.address as address2_2_, 1009 person0_.createdOn as createdO3_2_, 1010 person0_.name as name4_2_, 1011 person0_.nickName as nickName5_2_, 1012 person0_.version as version6_2_ 1013 from 1014 Person person0_ 1015 where 1016 $1::int=some ( 1017 select 1018 phones1_.id 1019 from 1020 Phone phones1_ 1021 where 1022 person0_.id=phones1_.person_id 1023 ) 1024 ---- 1025 project 1026 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1027 ├── has-placeholder 1028 ├── key: (1) 1029 ├── fd: (1)-->(2-6) 1030 └── inner-join (lookup person) 1031 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10!null 1032 ├── key columns: [10] = [1] 1033 ├── lookup columns are key 1034 ├── has-placeholder 1035 ├── key: (10) 1036 ├── fd: (1)-->(2-6), (1)==(10), (10)==(1) 1037 ├── distinct-on 1038 │ ├── columns: person_id:10 1039 │ ├── grouping columns: person_id:10 1040 │ ├── has-placeholder 1041 │ ├── key: (10) 1042 │ └── select 1043 │ ├── columns: phones1_.id:7!null person_id:10 1044 │ ├── has-placeholder 1045 │ ├── key: (7) 1046 │ ├── fd: (7)-->(10) 1047 │ ├── scan phones1_ 1048 │ │ ├── columns: phones1_.id:7!null person_id:10 1049 │ │ ├── key: (7) 1050 │ │ └── fd: (7)-->(10) 1051 │ └── filters 1052 │ └── phones1_.id:7 = $1::INT8 [outer=(7), constraints=(/7: (/NULL - ])] 1053 └── filters (true) 1054 1055 opt 1056 select 1057 person0_.id as id1_2_, 1058 person0_.address as address2_2_, 1059 person0_.createdOn as createdO3_2_, 1060 person0_.name as name4_2_, 1061 person0_.nickName as nickName5_2_, 1062 person0_.version as version6_2_ 1063 from 1064 Person person0_ 1065 where 1066 exists ( 1067 select 1068 phones1_.id 1069 from 1070 Phone phones1_ 1071 where 1072 person0_.id=phones1_.person_id 1073 ) 1074 ---- 1075 project 1076 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1077 ├── key: (1) 1078 ├── fd: (1)-->(2-6) 1079 └── inner-join (lookup person) 1080 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10!null 1081 ├── key columns: [10] = [1] 1082 ├── lookup columns are key 1083 ├── key: (10) 1084 ├── fd: (1)-->(2-6), (1)==(10), (10)==(1) 1085 ├── distinct-on 1086 │ ├── columns: person_id:10 1087 │ ├── grouping columns: person_id:10 1088 │ ├── key: (10) 1089 │ └── scan phones1_ 1090 │ └── columns: person_id:10 1091 └── filters (true) 1092 1093 opt 1094 select 1095 phone0_.id as id1_4_, 1096 phone0_.phone_number as phone_nu2_4_, 1097 phone0_.person_id as person_i4_4_, 1098 phone0_.phone_type as phone_ty3_4_ 1099 from 1100 Phone phone0_ 1101 where 1102 $1::date>all ( 1103 select 1104 repairtime1_.repairTimestamps 1105 from 1106 Phone_repairTimestamps repairtime1_ 1107 where 1108 phone0_.id=repairtime1_.Phone_id 1109 ) 1110 ---- 1111 anti-join (hash) 1112 ├── columns: id1_4_:1!null phone_nu2_4_:2 person_i4_4_:4 phone_ty3_4_:3 1113 ├── has-placeholder 1114 ├── key: (1) 1115 ├── fd: (1)-->(2-4) 1116 ├── scan phone0_ 1117 │ ├── columns: id:1!null phone_number:2 phone_type:3 person_id:4 1118 │ ├── key: (1) 1119 │ └── fd: (1)-->(2-4) 1120 ├── select 1121 │ ├── columns: phone_id:6!null repairtimestamps:7 1122 │ ├── has-placeholder 1123 │ ├── scan repairtime1_ 1124 │ │ └── columns: phone_id:6!null repairtimestamps:7 1125 │ └── filters 1126 │ └── (repairtimestamps:7 >= $1::DATE) IS NOT false [outer=(7)] 1127 └── filters 1128 └── id:1 = phone_id:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 1129 1130 opt 1131 select 1132 person0_.id as id1_2_, 1133 person0_.address as address2_2_, 1134 person0_.createdOn as createdO3_2_, 1135 person0_.name as name4_2_, 1136 person0_.nickName as nickName5_2_, 1137 person0_.version as version6_2_ 1138 from 1139 Person person0_ 1140 where 1141 1 in ( 1142 select 1143 phones1_.order_id 1144 from 1145 Phone phones1_ 1146 where 1147 person0_.id=phones1_.person_id 1148 ) 1149 ---- 1150 project 1151 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1152 ├── key: (1) 1153 ├── fd: (1)-->(2-6) 1154 └── inner-join (lookup person) 1155 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10!null 1156 ├── key columns: [10] = [1] 1157 ├── lookup columns are key 1158 ├── key: (10) 1159 ├── fd: (1)-->(2-6), (1)==(10), (10)==(1) 1160 ├── distinct-on 1161 │ ├── columns: person_id:10 1162 │ ├── grouping columns: person_id:10 1163 │ ├── key: (10) 1164 │ └── select 1165 │ ├── columns: person_id:10 order_id:11!null 1166 │ ├── fd: ()-->(11) 1167 │ ├── scan phones1_ 1168 │ │ └── columns: person_id:10 order_id:11 1169 │ └── filters 1170 │ └── order_id:11 = 1 [outer=(11), constraints=(/11: [/1 - /1]; tight), fd=()-->(11)] 1171 └── filters (true) 1172 1173 opt 1174 select 1175 person0_.id as id1_2_, 1176 person0_.address as address2_2_, 1177 person0_.createdOn as createdO3_2_, 1178 person0_.name as name4_2_, 1179 person0_.nickName as nickName5_2_, 1180 person0_.version as version6_2_ 1181 from 1182 Person person0_ cross 1183 join 1184 Phone phones2_ 1185 where 1186 person0_.id=phones2_.person_id 1187 and phones2_.order_id = ( 1188 select 1189 max(phones1_.order_id) 1190 from 1191 Phone phones1_ 1192 where 1193 person0_.id=phones1_.person_id 1194 ) 1195 and phones2_.phone_type='LAND_LINE' 1196 ---- 1197 project 1198 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1199 ├── fd: (1)-->(2-6) 1200 └── select 1201 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null phones2_.id:7!null phones2_.order_id:11!null max:17!null 1202 ├── key: (7) 1203 ├── fd: (1)-->(2-6), (7)-->(1-6,11,17), (11)==(17), (17)==(11) 1204 ├── group-by 1205 │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null phones2_.id:7!null phones2_.order_id:11 max:17!null 1206 │ ├── grouping columns: phones2_.id:7!null 1207 │ ├── key: (7) 1208 │ ├── fd: (1)-->(2-6), (7)-->(1-6,11,17) 1209 │ ├── inner-join (hash) 1210 │ │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null phones2_.id:7!null phones2_.phone_type:9!null phones2_.person_id:10!null phones2_.order_id:11 phones1_.person_id:15!null phones1_.order_id:16!null 1211 │ │ ├── fd: ()-->(9), (1)-->(2-6), (7)-->(10,11), (1)==(10,15), (10)==(1,15), (15)==(1,10) 1212 │ │ ├── select 1213 │ │ │ ├── columns: phones1_.person_id:15 phones1_.order_id:16!null 1214 │ │ │ ├── scan phones1_ 1215 │ │ │ │ └── columns: phones1_.person_id:15 phones1_.order_id:16 1216 │ │ │ └── filters 1217 │ │ │ └── phones1_.order_id:16 IS NOT NULL [outer=(16), constraints=(/16: (/NULL - ]; tight)] 1218 │ │ ├── inner-join (lookup person) 1219 │ │ │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null phones2_.id:7!null phones2_.phone_type:9!null phones2_.person_id:10!null phones2_.order_id:11 1220 │ │ │ ├── key columns: [10] = [1] 1221 │ │ │ ├── lookup columns are key 1222 │ │ │ ├── key: (7) 1223 │ │ │ ├── fd: ()-->(9), (1)-->(2-6), (7)-->(10,11), (1)==(10), (10)==(1) 1224 │ │ │ ├── select 1225 │ │ │ │ ├── columns: phones2_.id:7!null phones2_.phone_type:9!null phones2_.person_id:10 phones2_.order_id:11 1226 │ │ │ │ ├── key: (7) 1227 │ │ │ │ ├── fd: ()-->(9), (7)-->(10,11) 1228 │ │ │ │ ├── scan phones2_ 1229 │ │ │ │ │ ├── columns: phones2_.id:7!null phones2_.phone_type:9 phones2_.person_id:10 phones2_.order_id:11 1230 │ │ │ │ │ ├── key: (7) 1231 │ │ │ │ │ └── fd: (7)-->(9-11) 1232 │ │ │ │ └── filters 1233 │ │ │ │ └── phones2_.phone_type:9 = 'LAND_LINE' [outer=(9), constraints=(/9: [/'LAND_LINE' - /'LAND_LINE']; tight), fd=()-->(9)] 1234 │ │ │ └── filters (true) 1235 │ │ └── filters 1236 │ │ └── person0_.id:1 = phones1_.person_id:15 [outer=(1,15), constraints=(/1: (/NULL - ]; /15: (/NULL - ]), fd=(1)==(15), (15)==(1)] 1237 │ └── aggregations 1238 │ ├── max [as=max:17, outer=(16)] 1239 │ │ └── phones1_.order_id:16 1240 │ ├── const-agg [as=phones2_.order_id:11, outer=(11)] 1241 │ │ └── phones2_.order_id:11 1242 │ ├── const-agg [as=address:2, outer=(2)] 1243 │ │ └── address:2 1244 │ ├── const-agg [as=createdon:3, outer=(3)] 1245 │ │ └── createdon:3 1246 │ ├── const-agg [as=name:4, outer=(4)] 1247 │ │ └── name:4 1248 │ ├── const-agg [as=nickname:5, outer=(5)] 1249 │ │ └── nickname:5 1250 │ ├── const-agg [as=version:6, outer=(6)] 1251 │ │ └── version:6 1252 │ └── const-agg [as=person0_.id:1, outer=(1)] 1253 │ └── person0_.id:1 1254 └── filters 1255 └── phones2_.order_id:11 = max:17 [outer=(11,17), constraints=(/11: (/NULL - ]; /17: (/NULL - ]), fd=(11)==(17), (17)==(11)] 1256 1257 opt 1258 select 1259 person0_.id as id1_2_, 1260 person0_.address as address2_2_, 1261 person0_.createdOn as createdO3_2_, 1262 person0_.name as name4_2_, 1263 person0_.nickName as nickName5_2_, 1264 person0_.version as version6_2_ 1265 from 1266 Person person0_ 1267 where 1268 not (exists (select 1269 phones1_.id 1270 from 1271 Phone phones1_ 1272 where 1273 person0_.id=phones1_.person_id)) 1274 ---- 1275 anti-join (hash) 1276 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1277 ├── key: (1) 1278 ├── fd: (1)-->(2-6) 1279 ├── scan person0_ 1280 │ ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null 1281 │ ├── key: (1) 1282 │ └── fd: (1)-->(2-6) 1283 ├── scan phones1_ 1284 │ └── columns: person_id:10 1285 └── filters 1286 └── person0_.id:1 = person_id:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 1287 1288 opt 1289 select 1290 person0_.id as id1_2_, 1291 person0_.address as address2_2_, 1292 person0_.createdOn as createdO3_2_, 1293 person0_.name as name4_2_, 1294 person0_.nickName as nickName5_2_, 1295 person0_.version as version6_2_ 1296 from 1297 Person person0_ 1298 where 1299 exists ( 1300 select 1301 phones1_.id 1302 from 1303 Phone phones1_ 1304 where 1305 person0_.id=phones1_.person_id 1306 ) 1307 ---- 1308 project 1309 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1310 ├── key: (1) 1311 ├── fd: (1)-->(2-6) 1312 └── inner-join (lookup person) 1313 ├── columns: person0_.id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:10!null 1314 ├── key columns: [10] = [1] 1315 ├── lookup columns are key 1316 ├── key: (10) 1317 ├── fd: (1)-->(2-6), (1)==(10), (10)==(1) 1318 ├── distinct-on 1319 │ ├── columns: person_id:10 1320 │ ├── grouping columns: person_id:10 1321 │ ├── key: (10) 1322 │ └── scan phones1_ 1323 │ └── columns: person_id:10 1324 └── filters (true) 1325 1326 opt 1327 select 1328 phone0_.id as id1_4_, 1329 phone0_.phone_number as phone_nu2_4_, 1330 phone0_.person_id as person_i4_4_, 1331 phone0_.phone_type as phone_ty3_4_ 1332 from 1333 Phone phone0_ 1334 where 1335 not (exists (select 1336 calls1_.id 1337 from 1338 phone_call calls1_ 1339 where 1340 phone0_.id=calls1_.phone_id)) 1341 ---- 1342 anti-join (hash) 1343 ├── columns: id1_4_:1!null phone_nu2_4_:2 person_i4_4_:4 phone_ty3_4_:3 1344 ├── key: (1) 1345 ├── fd: (1)-->(2-4) 1346 ├── scan phone0_ 1347 │ ├── columns: phone0_.id:1!null phone_number:2 phone_type:3 person_id:4 1348 │ ├── key: (1) 1349 │ └── fd: (1)-->(2-4) 1350 ├── scan calls1_ 1351 │ └── columns: phone_id:9 1352 └── filters 1353 └── phone0_.id:1 = phone_id:9 [outer=(1,9), constraints=(/1: (/NULL - ]; /9: (/NULL - ]), fd=(1)==(9), (9)==(1)] 1354 1355 opt 1356 select 1357 person0_.id as id1_2_, 1358 person0_.address as address2_2_, 1359 person0_.createdOn as createdO3_2_, 1360 person0_.name as name4_2_, 1361 person0_.nickName as nickName5_2_, 1362 person0_.version as version6_2_ 1363 from 1364 Person person0_ 1365 where 1366 'Home address' in ( 1367 select 1368 addresses1_.addresses 1369 from 1370 Person_addresses addresses1_ 1371 where 1372 person0_.id=addresses1_.Person_id 1373 ) 1374 ---- 1375 project 1376 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1377 ├── key: (1) 1378 ├── fd: (1)-->(2-6) 1379 └── inner-join (lookup person) 1380 ├── columns: id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null person_id:7!null 1381 ├── key columns: [7] = [1] 1382 ├── lookup columns are key 1383 ├── key: (7) 1384 ├── fd: (1)-->(2-6), (1)==(7), (7)==(1) 1385 ├── distinct-on 1386 │ ├── columns: person_id:7!null 1387 │ ├── grouping columns: person_id:7!null 1388 │ ├── internal-ordering: +7 opt(8) 1389 │ ├── key: (7) 1390 │ └── select 1391 │ ├── columns: person_id:7!null addresses:8!null 1392 │ ├── fd: ()-->(8) 1393 │ ├── ordering: +7 opt(8) [actual: +7] 1394 │ ├── scan addresses1_ 1395 │ │ ├── columns: person_id:7!null addresses:8 1396 │ │ └── ordering: +7 opt(8) [actual: +7] 1397 │ └── filters 1398 │ └── addresses:8 = 'Home address' [outer=(8), constraints=(/8: [/'Home address' - /'Home address']; tight), fd=()-->(8)] 1399 └── filters (true) 1400 1401 opt 1402 select 1403 person0_.id as id1_2_, 1404 person0_.address as address2_2_, 1405 person0_.createdOn as createdO3_2_, 1406 person0_.name as name4_2_, 1407 person0_.nickName as nickName5_2_, 1408 person0_.version as version6_2_ 1409 from 1410 Person person0_ 1411 where 1412 'Home address' not in ( 1413 select 1414 addresses1_.addresses 1415 from 1416 Person_addresses addresses1_ 1417 where 1418 person0_.id=addresses1_.Person_id 1419 ) 1420 ---- 1421 anti-join (merge) 1422 ├── columns: id1_2_:1!null address2_2_:2 createdo3_2_:3 name4_2_:4 nickname5_2_:5 version6_2_:6!null 1423 ├── left ordering: +1 1424 ├── right ordering: +7 1425 ├── key: (1) 1426 ├── fd: (1)-->(2-6) 1427 ├── scan person0_ 1428 │ ├── columns: id:1!null address:2 createdon:3 name:4 nickname:5 version:6!null 1429 │ ├── key: (1) 1430 │ ├── fd: (1)-->(2-6) 1431 │ └── ordering: +1 1432 ├── select 1433 │ ├── columns: person_id:7!null addresses:8 1434 │ ├── ordering: +7 1435 │ ├── scan addresses1_ 1436 │ │ ├── columns: person_id:7!null addresses:8 1437 │ │ └── ordering: +7 1438 │ └── filters 1439 │ └── (addresses:8 = 'Home address') IS NOT false [outer=(8)] 1440 └── filters (true) 1441 1442 exec-ddl 1443 drop table Phone, phone_call, Person, Phone_repairTimestamps, Person_addresses; 1444 ---- 1445 1446 # ------------------------------------------------------------------------------ 1447 # Query #6 1448 # ------------------------------------------------------------------------------ 1449 exec-ddl 1450 create table EMPLOYEE ( 1451 id int8 not null, 1452 email varchar(255), 1453 currentProject_id int8, 1454 primary key (id) 1455 ) 1456 ---- 1457 1458 exec-ddl 1459 create table Employee_phones ( 1460 Employee_id int8 not null, 1461 phone_number varchar(255) 1462 ) 1463 ---- 1464 1465 opt 1466 select 1467 componenti0_.id as id1_0_, 1468 componenti0_.email as email2_0_, 1469 componenti0_.currentProject_id as currentP3_0_ 1470 from 1471 EMPLOYEE componenti0_ 1472 where 1473 ( 1474 select 1475 count(phones1_.Employee_id) 1476 from 1477 Employee_phones phones1_ 1478 where 1479 componenti0_.id=phones1_.Employee_id 1480 )=1 1481 ---- 1482 project 1483 ├── columns: id1_0_:1!null email2_0_:2 currentp3_0_:3 1484 ├── key: (1) 1485 ├── fd: (1)-->(2,3) 1486 └── select 1487 ├── columns: id:1!null email:2 currentproject_id:3 count:7!null 1488 ├── key: (1) 1489 ├── fd: ()-->(7), (1)-->(2,3) 1490 ├── group-by 1491 │ ├── columns: id:1!null email:2 currentproject_id:3 count:7!null 1492 │ ├── grouping columns: id:1!null 1493 │ ├── key: (1) 1494 │ ├── fd: (1)-->(2,3,7) 1495 │ ├── left-join (hash) 1496 │ │ ├── columns: id:1!null email:2 currentproject_id:3 employee_id:4 1497 │ │ ├── fd: (1)-->(2,3) 1498 │ │ ├── scan componenti0_ 1499 │ │ │ ├── columns: id:1!null email:2 currentproject_id:3 1500 │ │ │ ├── key: (1) 1501 │ │ │ └── fd: (1)-->(2,3) 1502 │ │ ├── scan phones1_ 1503 │ │ │ └── columns: employee_id:4!null 1504 │ │ └── filters 1505 │ │ └── id:1 = employee_id:4 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 1506 │ └── aggregations 1507 │ ├── count [as=count:7, outer=(4)] 1508 │ │ └── employee_id:4 1509 │ ├── const-agg [as=email:2, outer=(2)] 1510 │ │ └── email:2 1511 │ └── const-agg [as=currentproject_id:3, outer=(3)] 1512 │ └── currentproject_id:3 1513 └── filters 1514 └── count:7 = 1 [outer=(7), constraints=(/7: [/1 - /1]; tight), fd=()-->(7)] 1515 1516 exec-ddl 1517 drop table EMPLOYEE, Employee_phones; 1518 ---- 1519 1520 # ------------------------------------------------------------------------------ 1521 # Query #7 1522 # ------------------------------------------------------------------------------ 1523 exec-ddl 1524 create table Company ( 1525 id int8 not null, 1526 location_id int8, 1527 primary key (id) 1528 ) 1529 ---- 1530 1531 exec-ddl 1532 create table Company_Employee ( 1533 Company_id int8 not null, 1534 employees_id int8 not null, 1535 primary key (Company_id, employees_id) 1536 ) 1537 ---- 1538 1539 exec-ddl 1540 create table Employee ( 1541 id int8 not null, 1542 primary key (id) 1543 ) 1544 ---- 1545 1546 exec-ddl 1547 create table Manager ( 1548 id int8 not null, 1549 primary key (id) 1550 ) 1551 ---- 1552 1553 exec-ddl 1554 create table Location ( 1555 id int8 not null, 1556 address varchar(255), 1557 zip int4 not null, 1558 primary key (id) 1559 ) 1560 ---- 1561 1562 opt 1563 select 1564 company0_.id as id1_0_0_, 1565 location3_.id as id1_8_1_, 1566 company0_.location_id as location2_0_0_, 1567 location3_.address as address2_8_1_, 1568 location3_.zip as zip3_8_1_ 1569 from 1570 Company company0_ 1571 left outer join 1572 Location location3_ 1573 on company0_.location_id=location3_.id 1574 where 1575 not (exists (select 1576 employee2_.id 1577 from 1578 Company_Employee employees1_, 1579 ( select 1580 id, 1581 0 as clazz_ 1582 from 1583 Employee 1584 union 1585 all select 1586 id, 1587 1 as clazz_ 1588 from 1589 Manager ) employee2_ 1590 where 1591 company0_.id=employees1_.Company_id 1592 and employees1_.employees_id=employee2_.id)) 1593 ---- 1594 right-join (hash) 1595 ├── columns: id1_0_0_:1!null id1_8_1_:3 location2_0_0_:2 address2_8_1_:4 zip3_8_1_:5 1596 ├── key: (1) 1597 ├── fd: (1)-->(2-5), (3)-->(4,5) 1598 ├── scan location3_ 1599 │ ├── columns: location3_.id:3!null address:4 zip:5!null 1600 │ ├── key: (3) 1601 │ └── fd: (3)-->(4,5) 1602 ├── anti-join (hash) 1603 │ ├── columns: company0_.id:1!null location_id:2 1604 │ ├── key: (1) 1605 │ ├── fd: (1)-->(2) 1606 │ ├── scan company0_ 1607 │ │ ├── columns: company0_.id:1!null location_id:2 1608 │ │ ├── key: (1) 1609 │ │ └── fd: (1)-->(2) 1610 │ ├── inner-join (hash) 1611 │ │ ├── columns: company_id:6!null employees_id:7!null id:12!null 1612 │ │ ├── fd: (7)==(12), (12)==(7) 1613 │ │ ├── union-all 1614 │ │ │ ├── columns: id:12!null 1615 │ │ │ ├── left columns: employee.id:8 1616 │ │ │ ├── right columns: manager.id:10 1617 │ │ │ ├── scan employee 1618 │ │ │ │ ├── columns: employee.id:8!null 1619 │ │ │ │ └── key: (8) 1620 │ │ │ └── scan manager 1621 │ │ │ ├── columns: manager.id:10!null 1622 │ │ │ └── key: (10) 1623 │ │ ├── scan employees1_ 1624 │ │ │ ├── columns: company_id:6!null employees_id:7!null 1625 │ │ │ └── key: (6,7) 1626 │ │ └── filters 1627 │ │ └── employees_id:7 = id:12 [outer=(7,12), constraints=(/7: (/NULL - ]; /12: (/NULL - ]), fd=(7)==(12), (12)==(7)] 1628 │ └── filters 1629 │ └── company0_.id:1 = company_id:6 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 1630 └── filters 1631 └── location_id:2 = location3_.id:3 [outer=(2,3), constraints=(/2: (/NULL - ]; /3: (/NULL - ]), fd=(2)==(3), (3)==(2)] 1632 1633 exec-ddl 1634 drop table Company, Company_Employee, Employee, Manager, Location; 1635 ---- 1636 1637 # ------------------------------------------------------------------------------ 1638 # Query #8 1639 # org.hibernate.test.annotations.indexcoll.IndexedCollectionTest 1640 # testMapKeyOnManyToMany 1641 # ------------------------------------------------------------------------------ 1642 exec-ddl 1643 create table News ( 1644 news_id int4 not null, 1645 detail varchar(255), 1646 title varchar(255), 1647 primary key (news_id) 1648 ) 1649 ---- 1650 1651 exec-ddl 1652 create table Newspaper ( 1653 id int4 not null, 1654 name varchar(255), 1655 primary key (id) 1656 ) 1657 ---- 1658 1659 exec-ddl 1660 create table Newspaper_News ( 1661 Newspaper_id int4 not null, 1662 news_news_id int4 not null, 1663 primary key (Newspaper_id, news_news_id) 1664 ) 1665 ---- 1666 1667 opt 1668 select 1669 news0_.Newspaper_id as Newspape1_23_0_, 1670 news0_.news_news_id as news_new2_23_0_, 1671 (select 1672 a0.title 1673 from 1674 News a0 1675 where 1676 a0.news_id=news0_.news_news_id) as formula140_0_, 1677 news1_.news_id as news_id1_21_1_, 1678 news1_.detail as detail2_21_1_, 1679 news1_.title as title3_21_1_ 1680 from 1681 Newspaper_News news0_ 1682 inner join 1683 News news1_ 1684 on news0_.news_news_id=news1_.news_id 1685 where 1686 news0_.Newspaper_id=1 1687 ---- 1688 project 1689 ├── columns: newspape1_23_0_:1!null news_new2_23_0_:2!null formula140_0_:9 news_id1_21_1_:3!null detail2_21_1_:4 title3_21_1_:5 1690 ├── key: (3) 1691 ├── fd: ()-->(1), (3)-->(4,5,9), (2)==(3), (3)==(2) 1692 ├── left-join (lookup news) 1693 │ ├── columns: newspaper_id:1!null news_news_id:2!null news1_.news_id:3!null news1_.detail:4 news1_.title:5 a0.news_id:6 a0.title:8 1694 │ ├── key columns: [2] = [6] 1695 │ ├── lookup columns are key 1696 │ ├── key: (3) 1697 │ ├── fd: ()-->(1), (3)-->(4-6,8), (2)==(3), (3)==(2), (6)-->(8) 1698 │ ├── inner-join (lookup news) 1699 │ │ ├── columns: newspaper_id:1!null news_news_id:2!null news1_.news_id:3!null news1_.detail:4 news1_.title:5 1700 │ │ ├── key columns: [2] = [3] 1701 │ │ ├── lookup columns are key 1702 │ │ ├── key: (3) 1703 │ │ ├── fd: ()-->(1), (3)-->(4,5), (2)==(3), (3)==(2) 1704 │ │ ├── scan news0_ 1705 │ │ │ ├── columns: newspaper_id:1!null news_news_id:2!null 1706 │ │ │ ├── constraint: /1/2: [/1 - /1] 1707 │ │ │ ├── key: (2) 1708 │ │ │ └── fd: ()-->(1) 1709 │ │ └── filters (true) 1710 │ └── filters (true) 1711 └── projections 1712 └── a0.title:8 [as=formula140_0_:9, outer=(8)] 1713 1714 exec-ddl 1715 drop table News, Newspaper, Newspaper_News; 1716 ---- 1717 1718 # ------------------------------------------------------------------------------ 1719 # Query #9 1720 # org.hibernate.test.annotations.indexcoll.MapKeyTest testMapKeyOnEmbeddedId 1721 # ------------------------------------------------------------------------------ 1722 exec-ddl 1723 create table GenerationGroup ( 1724 id int4 not null, 1725 age varchar(255), 1726 culture varchar(255), 1727 description varchar(255), 1728 primary key (id) 1729 ) 1730 ---- 1731 1732 exec-ddl 1733 create table GenerationUser ( 1734 id int4 not null, 1735 primary key (id) 1736 ) 1737 ---- 1738 1739 exec-ddl 1740 create table GenerationUser_GenerationGroup ( 1741 GenerationUser_id int4 not null, 1742 ref_id int4 not null, 1743 primary key (GenerationUser_id, ref_id) 1744 ) 1745 ---- 1746 1747 opt 1748 SELECT ref0_.generationuser_id AS generati1_2_0_ 1749 ,ref0_.ref_id AS ref_id2_2_0_ 1750 ,(SELECT a13.age 1751 FROM generationgroup AS a13 1752 WHERE a13.id = ref0_.ref_id) AS formula131_0_ 1753 ,(SELECT a15.culture 1754 FROM generationgroup AS a15 1755 WHERE a15.id = ref0_.ref_id) AS formula132_0_ 1756 ,(SELECT a13.description 1757 FROM generationgroup AS a13 1758 WHERE a13.id = ref0_.ref_id) AS formula133_0_ 1759 ,generation1_.id AS id1_0_1_ 1760 ,generation1_.age AS age2_0_1_ 1761 ,generation1_.culture AS culture3_0_1_ 1762 ,generation1_.description AS descript4_0_1_ 1763 FROM generationuser_generationgroup AS ref0_ 1764 INNER JOIN generationgroup AS generation1_ 1765 ON ref0_.ref_id = generation1_.id 1766 WHERE ref0_.generationuser_id = 1; 1767 ---- 1768 project 1769 ├── columns: generati1_2_0_:1!null ref_id2_2_0_:2!null formula131_0_:19 formula132_0_:20 formula133_0_:21 id1_0_1_:3!null age2_0_1_:4 culture3_0_1_:5 descript4_0_1_:6 1770 ├── key: (3) 1771 ├── fd: ()-->(1), (3)-->(4-6,19-21), (2)==(3), (3)==(2) 1772 ├── left-join (lookup generationgroup) 1773 │ ├── columns: generationuser_id:1!null ref_id:2!null generation1_.id:3!null generation1_.age:4 generation1_.culture:5 generation1_.description:6 a13.id:7 a13.age:8 a15.id:11 a15.culture:13 a13.id:15 a13.description:18 1774 │ ├── key columns: [2] = [15] 1775 │ ├── lookup columns are key 1776 │ ├── key: (3) 1777 │ ├── fd: ()-->(1), (3)-->(4-8,11,13,15,18), (2)==(3), (3)==(2), (7)-->(8), (11)-->(13), (15)-->(18) 1778 │ ├── left-join (lookup generationgroup) 1779 │ │ ├── columns: generationuser_id:1!null ref_id:2!null generation1_.id:3!null generation1_.age:4 generation1_.culture:5 generation1_.description:6 a13.id:7 a13.age:8 a15.id:11 a15.culture:13 1780 │ │ ├── key columns: [2] = [11] 1781 │ │ ├── lookup columns are key 1782 │ │ ├── key: (3) 1783 │ │ ├── fd: ()-->(1), (3)-->(4-8,11,13), (2)==(3), (3)==(2), (7)-->(8), (11)-->(13) 1784 │ │ ├── left-join (lookup generationgroup) 1785 │ │ │ ├── columns: generationuser_id:1!null ref_id:2!null generation1_.id:3!null generation1_.age:4 generation1_.culture:5 generation1_.description:6 a13.id:7 a13.age:8 1786 │ │ │ ├── key columns: [2] = [7] 1787 │ │ │ ├── lookup columns are key 1788 │ │ │ ├── key: (3) 1789 │ │ │ ├── fd: ()-->(1), (3)-->(4-8), (2)==(3), (3)==(2), (7)-->(8) 1790 │ │ │ ├── inner-join (lookup generationgroup) 1791 │ │ │ │ ├── columns: generationuser_id:1!null ref_id:2!null generation1_.id:3!null generation1_.age:4 generation1_.culture:5 generation1_.description:6 1792 │ │ │ │ ├── key columns: [2] = [3] 1793 │ │ │ │ ├── lookup columns are key 1794 │ │ │ │ ├── key: (3) 1795 │ │ │ │ ├── fd: ()-->(1), (3)-->(4-6), (2)==(3), (3)==(2) 1796 │ │ │ │ ├── scan ref0_ 1797 │ │ │ │ │ ├── columns: generationuser_id:1!null ref_id:2!null 1798 │ │ │ │ │ ├── constraint: /1/2: [/1 - /1] 1799 │ │ │ │ │ ├── key: (2) 1800 │ │ │ │ │ └── fd: ()-->(1) 1801 │ │ │ │ └── filters (true) 1802 │ │ │ └── filters (true) 1803 │ │ └── filters (true) 1804 │ └── filters (true) 1805 └── projections 1806 ├── a13.age:8 [as=formula131_0_:19, outer=(8)] 1807 ├── a15.culture:13 [as=formula132_0_:20, outer=(13)] 1808 └── a13.description:18 [as=formula133_0_:21, outer=(18)] 1809 1810 exec-ddl 1811 drop table GenerationGroup, GenerationUser, GenerationUser_GenerationGroup; 1812 ---- 1813 1814 # ------------------------------------------------------------------------------ 1815 # Query #10 1816 # org.hibernate.test.bidi.AuctionTest2 testLazy 1817 # ------------------------------------------------------------------------------ 1818 exec-ddl 1819 create table TAuction2 ( 1820 id int8 not null, 1821 description varchar(255), 1822 endDatetime timestamp, 1823 successfulBid int8, 1824 primary key (id) 1825 ) 1826 ---- 1827 1828 exec-ddl 1829 create table TBid2 ( 1830 id int8 not null, 1831 amount numeric(31, 19), 1832 createdDatetime timestamp, 1833 auctionId int8, 1834 primary key (id) 1835 ) 1836 ---- 1837 1838 opt 1839 select 1840 bids0_.auctionId as auctionI4_1_0_, 1841 bids0_.id as id1_1_0_, 1842 bids0_.id as id1_1_1_, 1843 bids0_.amount as amount2_1_1_, 1844 bids0_.createdDatetime as createdD3_1_1_, 1845 bids0_.auctionId as auctionI4_1_1_, 1846 exists(select 1847 a.id 1848 from 1849 TAuction2 a 1850 where 1851 a.successfulBid=bids0_.id) as formula41_1_ 1852 from 1853 TBid2 bids0_ 1854 where 1855 bids0_.auctionId=$1 1856 ---- 1857 project 1858 ├── columns: auctioni4_1_0_:4!null id1_1_0_:1!null id1_1_1_:1!null amount2_1_1_:2 createdd3_1_1_:3 auctioni4_1_1_:4!null formula41_1_:9!null 1859 ├── has-placeholder 1860 ├── key: (1) 1861 ├── fd: (1)-->(2-4,9) 1862 ├── group-by 1863 │ ├── columns: bids0_.id:1!null amount:2 createddatetime:3 auctionid:4!null true_agg:11 1864 │ ├── grouping columns: bids0_.id:1!null 1865 │ ├── has-placeholder 1866 │ ├── key: (1) 1867 │ ├── fd: (1)-->(2-4,11) 1868 │ ├── right-join (hash) 1869 │ │ ├── columns: bids0_.id:1!null amount:2 createddatetime:3 auctionid:4!null successfulbid:8 true:10 1870 │ │ ├── has-placeholder 1871 │ │ ├── fd: (1)-->(2-4) 1872 │ │ ├── project 1873 │ │ │ ├── columns: true:10!null successfulbid:8 1874 │ │ │ ├── fd: ()-->(10) 1875 │ │ │ ├── scan a 1876 │ │ │ │ └── columns: successfulbid:8 1877 │ │ │ └── projections 1878 │ │ │ └── true [as=true:10] 1879 │ │ ├── select 1880 │ │ │ ├── columns: bids0_.id:1!null amount:2 createddatetime:3 auctionid:4!null 1881 │ │ │ ├── has-placeholder 1882 │ │ │ ├── key: (1) 1883 │ │ │ ├── fd: (1)-->(2-4) 1884 │ │ │ ├── scan bids0_ 1885 │ │ │ │ ├── columns: bids0_.id:1!null amount:2 createddatetime:3 auctionid:4 1886 │ │ │ │ ├── key: (1) 1887 │ │ │ │ └── fd: (1)-->(2-4) 1888 │ │ │ └── filters 1889 │ │ │ └── auctionid:4 = $1 [outer=(4), constraints=(/4: (/NULL - ])] 1890 │ │ └── filters 1891 │ │ └── successfulbid:8 = bids0_.id:1 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)] 1892 │ └── aggregations 1893 │ ├── const-not-null-agg [as=true_agg:11, outer=(10)] 1894 │ │ └── true:10 1895 │ ├── const-agg [as=amount:2, outer=(2)] 1896 │ │ └── amount:2 1897 │ ├── const-agg [as=createddatetime:3, outer=(3)] 1898 │ │ └── createddatetime:3 1899 │ └── const-agg [as=auctionid:4, outer=(4)] 1900 │ └── auctionid:4 1901 └── projections 1902 └── true_agg:11 IS NOT NULL [as=formula41_1_:9, outer=(11)] 1903 1904 exec-ddl 1905 drop table TAuction2, TBid2; 1906 ---- 1907 1908 # ------------------------------------------------------------------------------ 1909 # Query #11 1910 # org.hibernate.test.cid.CompositeIdTest 1911 # ------------------------------------------------------------------------------ 1912 exec-ddl 1913 CREATE TABLE customer ( 1914 customerid VARCHAR(10) NOT NULL, 1915 name VARCHAR(100) NOT NULL, 1916 address VARCHAR(200) NOT NULL, 1917 PRIMARY KEY (customerid) 1918 ); 1919 ---- 1920 1921 exec-ddl 1922 CREATE TABLE customerorder ( 1923 customerid VARCHAR(10) NOT NULL, 1924 ordernumber INT4 NOT NULL, 1925 orderdate DATE NOT NULL, 1926 PRIMARY KEY (customerid, ordernumber) 1927 ); 1928 ---- 1929 1930 exec-ddl 1931 CREATE TABLE lineitem ( 1932 customerid VARCHAR(10) NOT NULL, 1933 ordernumber INT4 NOT NULL, 1934 productid VARCHAR(10) NOT NULL, 1935 quantity INT4, 1936 PRIMARY KEY (customerid, ordernumber, productid) 1937 ); 1938 ---- 1939 1940 exec-ddl 1941 CREATE TABLE product ( 1942 productid VARCHAR(10) NOT NULL, 1943 description VARCHAR(200) NOT NULL, 1944 cost NUMERIC(19,2), 1945 numberavailable INT4, 1946 PRIMARY KEY (productid) 1947 ); 1948 ---- 1949 1950 opt 1951 SELECT 1952 order0_.customerid AS customer1_1_0_, 1953 order0_.ordernumber AS ordernum2_1_0_, 1954 order0_.orderdate AS orderdat3_1_0_, 1955 ( 1956 SELECT 1957 sum(li.quantity * p.cost) 1958 FROM 1959 lineitem AS li, product AS p 1960 WHERE 1961 li.productid = p.productid 1962 AND li.customerid = order0_.customerid 1963 AND li.ordernumber = order0_.ordernumber 1964 ) 1965 AS formula101_0_, 1966 lineitems1_.customerid AS customer1_2_1_, 1967 lineitems1_.ordernumber AS ordernum2_2_1_, 1968 lineitems1_.productid AS producti3_2_1_, 1969 lineitems1_.customerid AS customer1_2_2_, 1970 lineitems1_.ordernumber AS ordernum2_2_2_, 1971 lineitems1_.productid AS producti3_2_2_, 1972 lineitems1_.quantity AS quantity4_2_2_ 1973 FROM 1974 customerorder AS order0_ 1975 LEFT JOIN lineitem AS lineitems1_ 1976 ON 1977 order0_.customerid = lineitems1_.customerid 1978 AND order0_.ordernumber = lineitems1_.ordernumber 1979 WHERE 1980 order0_.customerid = 'c111' AND order0_.ordernumber = 0; 1981 ---- 1982 project 1983 ├── columns: customer1_1_0_:1!null ordernum2_1_0_:2!null orderdat3_1_0_:3!null formula101_0_:18 customer1_2_1_:4 ordernum2_2_1_:5 producti3_2_1_:6 customer1_2_2_:4 ordernum2_2_2_:5 producti3_2_2_:6 quantity4_2_2_:7 1984 ├── key: (6) 1985 ├── fd: ()-->(1-3), (6)-->(4,5,7,18) 1986 ├── group-by 1987 │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null lineitems1_.customerid:4 lineitems1_.ordernumber:5 lineitems1_.productid:6 lineitems1_.quantity:7 sum:17 1988 │ ├── grouping columns: lineitems1_.productid:6 1989 │ ├── key: (6) 1990 │ ├── fd: ()-->(1-3), (6)-->(1-5,7,17) 1991 │ ├── right-join (hash) 1992 │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null lineitems1_.customerid:4 lineitems1_.ordernumber:5 lineitems1_.productid:6 lineitems1_.quantity:7 li.customerid:8 li.ordernumber:9 column16:16 1993 │ │ ├── fd: ()-->(1-3), (6)-->(4,5,7) 1994 │ │ ├── project 1995 │ │ │ ├── columns: column16:16 li.customerid:8!null li.ordernumber:9!null 1996 │ │ │ ├── inner-join (hash) 1997 │ │ │ │ ├── columns: li.customerid:8!null li.ordernumber:9!null li.productid:10!null li.quantity:11 p.productid:12!null cost:14 1998 │ │ │ │ ├── key: (8,9,12) 1999 │ │ │ │ ├── fd: (8-10)-->(11), (12)-->(14), (10)==(12), (12)==(10) 2000 │ │ │ │ ├── scan li 2001 │ │ │ │ │ ├── columns: li.customerid:8!null li.ordernumber:9!null li.productid:10!null li.quantity:11 2002 │ │ │ │ │ ├── key: (8-10) 2003 │ │ │ │ │ └── fd: (8-10)-->(11) 2004 │ │ │ │ ├── scan p 2005 │ │ │ │ │ ├── columns: p.productid:12!null cost:14 2006 │ │ │ │ │ ├── key: (12) 2007 │ │ │ │ │ └── fd: (12)-->(14) 2008 │ │ │ │ └── filters 2009 │ │ │ │ └── li.productid:10 = p.productid:12 [outer=(10,12), constraints=(/10: (/NULL - ]; /12: (/NULL - ]), fd=(10)==(12), (12)==(10)] 2010 │ │ │ └── projections 2011 │ │ │ └── li.quantity:11 * cost:14 [as=column16:16, outer=(11,14)] 2012 │ │ ├── left-join (merge) 2013 │ │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null lineitems1_.customerid:4 lineitems1_.ordernumber:5 lineitems1_.productid:6 lineitems1_.quantity:7 2014 │ │ │ ├── left ordering: +1,+2 2015 │ │ │ ├── right ordering: +4,+5 2016 │ │ │ ├── key: (6) 2017 │ │ │ ├── fd: ()-->(1-3), (6)-->(4,5,7) 2018 │ │ │ ├── scan order0_ 2019 │ │ │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null 2020 │ │ │ │ ├── constraint: /1/2: [/'c111'/0 - /'c111'/0] 2021 │ │ │ │ ├── cardinality: [0 - 1] 2022 │ │ │ │ ├── key: () 2023 │ │ │ │ └── fd: ()-->(1-3) 2024 │ │ │ ├── scan lineitems1_ 2025 │ │ │ │ ├── columns: lineitems1_.customerid:4!null lineitems1_.ordernumber:5!null lineitems1_.productid:6!null lineitems1_.quantity:7 2026 │ │ │ │ ├── constraint: /4/5/6: [/'c111'/0 - /'c111'/0] 2027 │ │ │ │ ├── key: (6) 2028 │ │ │ │ └── fd: ()-->(4,5), (6)-->(7) 2029 │ │ │ └── filters (true) 2030 │ │ └── filters 2031 │ │ ├── li.customerid:8 = order0_.customerid:1 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)] 2032 │ │ └── li.ordernumber:9 = order0_.ordernumber:2 [outer=(2,9), constraints=(/2: (/NULL - ]; /9: (/NULL - ]), fd=(2)==(9), (9)==(2)] 2033 │ └── aggregations 2034 │ ├── sum [as=sum:17, outer=(16)] 2035 │ │ └── column16:16 2036 │ ├── const-agg [as=order0_.customerid:1, outer=(1)] 2037 │ │ └── order0_.customerid:1 2038 │ ├── const-agg [as=order0_.ordernumber:2, outer=(2)] 2039 │ │ └── order0_.ordernumber:2 2040 │ ├── const-agg [as=orderdate:3, outer=(3)] 2041 │ │ └── orderdate:3 2042 │ ├── const-agg [as=lineitems1_.customerid:4, outer=(4)] 2043 │ │ └── lineitems1_.customerid:4 2044 │ ├── const-agg [as=lineitems1_.ordernumber:5, outer=(5)] 2045 │ │ └── lineitems1_.ordernumber:5 2046 │ └── const-agg [as=lineitems1_.quantity:7, outer=(7)] 2047 │ └── lineitems1_.quantity:7 2048 └── projections 2049 └── sum:17 [as=formula101_0_:18, outer=(17)] 2050 2051 opt 2052 SELECT 2053 customer0_.customerid AS customer1_0_0_, 2054 orders1_.customerid AS customer1_1_1_, 2055 orders1_.ordernumber AS ordernum2_1_1_, 2056 lineitems2_.customerid AS customer1_2_2_, 2057 lineitems2_.ordernumber AS ordernum2_2_2_, 2058 lineitems2_.productid AS producti3_2_2_, 2059 product3_.productid AS producti1_3_3_, 2060 customer0_.name AS name2_0_0_, 2061 customer0_.address AS address3_0_0_, 2062 orders1_.orderdate AS orderdat3_1_1_, 2063 ( 2064 SELECT 2065 sum(li.quantity * p.cost) 2066 FROM 2067 lineitem AS li, product AS p 2068 WHERE 2069 li.productid = p.productid 2070 AND li.customerid = orders1_.customerid 2071 AND li.ordernumber = orders1_.ordernumber 2072 ) 2073 AS formula103_1_, 2074 orders1_.customerid AS customer1_1_0__, 2075 orders1_.ordernumber AS ordernum2_1_0__, 2076 orders1_.ordernumber AS ordernum2_0__, 2077 lineitems2_.quantity AS quantity4_2_2_, 2078 lineitems2_.customerid AS customer1_2_1__, 2079 lineitems2_.ordernumber AS ordernum2_2_1__, 2080 lineitems2_.productid AS producti3_2_1__, 2081 product3_.description AS descript2_3_3_, 2082 product3_.cost AS cost3_3_3_, 2083 product3_.numberavailable AS numberav4_3_3_, 2084 ( 2085 SELECT 2086 sum(li.quantity) 2087 FROM 2088 lineitem AS li 2089 WHERE 2090 li.productid = product3_.productid 2091 ) 2092 AS formula104_3_ 2093 FROM 2094 customer AS customer0_ 2095 LEFT JOIN customerorder AS orders1_ 2096 ON customer0_.customerid = orders1_.customerid 2097 LEFT JOIN lineitem AS lineitems2_ 2098 ON 2099 orders1_.customerid = lineitems2_.customerid 2100 AND orders1_.ordernumber = lineitems2_.ordernumber 2101 LEFT JOIN product AS product3_ ON lineitems2_.productid = product3_.productid; 2102 ---- 2103 project 2104 ├── columns: customer1_0_0_:1!null customer1_1_1_:4 ordernum2_1_1_:5 customer1_2_2_:7 ordernum2_2_2_:8 producti3_2_2_:9 producti1_3_3_:11 name2_0_0_:2!null address3_0_0_:3!null orderdat3_1_1_:6 formula103_1_:30 customer1_1_0__:4 ordernum2_1_0__:5 ordernum2_0__:5 quantity4_2_2_:10 customer1_2_1__:7 ordernum2_2_1__:8 producti3_2_1__:9 descript2_3_3_:12 cost3_3_3_:13 numberav4_3_3_:14 formula104_3_:31 2105 ├── key: (1,4,5,7-9) 2106 ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10), (11)-->(12-14), (1,4,5,7-9)-->(2,3,6,10-14,30,31) 2107 ├── group-by 2108 │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 lineitems2_.quantity:10 product3_.productid:11 product3_.description:12 product3_.cost:13 product3_.numberavailable:14 sum:24 sum:29 2109 │ ├── grouping columns: customer0_.customerid:1!null orders1_.customerid:4 orders1_.ordernumber:5 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 2110 │ ├── key: (1,4,5,7-9) 2111 │ ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10), (11)-->(12-14), (1,4,5,7-9)-->(2,3,6,10-14,24,29) 2112 │ ├── left-join (hash) 2113 │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 lineitems2_.quantity:10 product3_.productid:11 product3_.description:12 product3_.cost:13 product3_.numberavailable:14 sum:24 li.productid:27 li.quantity:28 2114 │ │ ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10), (11)-->(12-14), (1,4,5,7-9)-->(2,3,6,10-14,24) 2115 │ │ ├── group-by 2116 │ │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 lineitems2_.quantity:10 product3_.productid:11 product3_.description:12 product3_.cost:13 product3_.numberavailable:14 sum:24 2117 │ │ │ ├── grouping columns: customer0_.customerid:1!null orders1_.customerid:4 orders1_.ordernumber:5 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 2118 │ │ │ ├── key: (1,4,5,7-9) 2119 │ │ │ ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10), (11)-->(12-14), (1,4,5,7-9)-->(2,3,6,10-14,24) 2120 │ │ │ ├── left-join (hash) 2121 │ │ │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 lineitems2_.quantity:10 product3_.productid:11 product3_.description:12 product3_.cost:13 product3_.numberavailable:14 li.customerid:15 li.ordernumber:16 column23:23 2122 │ │ │ │ ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10), (11)-->(12-14), (1,4,5,7-9)-->(11-14) 2123 │ │ │ │ ├── left-join (hash) 2124 │ │ │ │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 lineitems2_.quantity:10 product3_.productid:11 product3_.description:12 product3_.cost:13 product3_.numberavailable:14 2125 │ │ │ │ │ ├── key: (1,4,5,7-9) 2126 │ │ │ │ │ ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10), (11)-->(12-14), (1,4,5,7-9)-->(11-14) 2127 │ │ │ │ │ ├── left-join (hash) 2128 │ │ │ │ │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 lineitems2_.customerid:7 lineitems2_.ordernumber:8 lineitems2_.productid:9 lineitems2_.quantity:10 2129 │ │ │ │ │ │ ├── key: (1,4,5,7-9) 2130 │ │ │ │ │ │ ├── fd: (1)-->(2,3), (4,5)-->(6), (7-9)-->(10) 2131 │ │ │ │ │ │ ├── left-join (merge) 2132 │ │ │ │ │ │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null orders1_.customerid:4 orders1_.ordernumber:5 orderdate:6 2133 │ │ │ │ │ │ │ ├── left ordering: +1 2134 │ │ │ │ │ │ │ ├── right ordering: +4 2135 │ │ │ │ │ │ │ ├── key: (1,4,5) 2136 │ │ │ │ │ │ │ ├── fd: (1)-->(2,3), (4,5)-->(6) 2137 │ │ │ │ │ │ │ ├── scan customer0_ 2138 │ │ │ │ │ │ │ │ ├── columns: customer0_.customerid:1!null name:2!null address:3!null 2139 │ │ │ │ │ │ │ │ ├── key: (1) 2140 │ │ │ │ │ │ │ │ ├── fd: (1)-->(2,3) 2141 │ │ │ │ │ │ │ │ └── ordering: +1 2142 │ │ │ │ │ │ │ ├── scan orders1_ 2143 │ │ │ │ │ │ │ │ ├── columns: orders1_.customerid:4!null orders1_.ordernumber:5!null orderdate:6!null 2144 │ │ │ │ │ │ │ │ ├── key: (4,5) 2145 │ │ │ │ │ │ │ │ ├── fd: (4,5)-->(6) 2146 │ │ │ │ │ │ │ │ └── ordering: +4 2147 │ │ │ │ │ │ │ └── filters (true) 2148 │ │ │ │ │ │ ├── scan lineitems2_ 2149 │ │ │ │ │ │ │ ├── columns: lineitems2_.customerid:7!null lineitems2_.ordernumber:8!null lineitems2_.productid:9!null lineitems2_.quantity:10 2150 │ │ │ │ │ │ │ ├── key: (7-9) 2151 │ │ │ │ │ │ │ └── fd: (7-9)-->(10) 2152 │ │ │ │ │ │ └── filters 2153 │ │ │ │ │ │ ├── orders1_.customerid:4 = lineitems2_.customerid:7 [outer=(4,7), constraints=(/4: (/NULL - ]; /7: (/NULL - ]), fd=(4)==(7), (7)==(4)] 2154 │ │ │ │ │ │ └── orders1_.ordernumber:5 = lineitems2_.ordernumber:8 [outer=(5,8), constraints=(/5: (/NULL - ]; /8: (/NULL - ]), fd=(5)==(8), (8)==(5)] 2155 │ │ │ │ │ ├── scan product3_ 2156 │ │ │ │ │ │ ├── columns: product3_.productid:11!null product3_.description:12!null product3_.cost:13 product3_.numberavailable:14 2157 │ │ │ │ │ │ ├── key: (11) 2158 │ │ │ │ │ │ └── fd: (11)-->(12-14) 2159 │ │ │ │ │ └── filters 2160 │ │ │ │ │ └── lineitems2_.productid:9 = product3_.productid:11 [outer=(9,11), constraints=(/9: (/NULL - ]; /11: (/NULL - ]), fd=(9)==(11), (11)==(9)] 2161 │ │ │ │ ├── project 2162 │ │ │ │ │ ├── columns: column23:23 li.customerid:15!null li.ordernumber:16!null 2163 │ │ │ │ │ ├── inner-join (hash) 2164 │ │ │ │ │ │ ├── columns: li.customerid:15!null li.ordernumber:16!null li.productid:17!null li.quantity:18 p.productid:19!null p.cost:21 2165 │ │ │ │ │ │ ├── key: (15,16,19) 2166 │ │ │ │ │ │ ├── fd: (15-17)-->(18), (19)-->(21), (17)==(19), (19)==(17) 2167 │ │ │ │ │ │ ├── scan li 2168 │ │ │ │ │ │ │ ├── columns: li.customerid:15!null li.ordernumber:16!null li.productid:17!null li.quantity:18 2169 │ │ │ │ │ │ │ ├── key: (15-17) 2170 │ │ │ │ │ │ │ └── fd: (15-17)-->(18) 2171 │ │ │ │ │ │ ├── scan p 2172 │ │ │ │ │ │ │ ├── columns: p.productid:19!null p.cost:21 2173 │ │ │ │ │ │ │ ├── key: (19) 2174 │ │ │ │ │ │ │ └── fd: (19)-->(21) 2175 │ │ │ │ │ │ └── filters 2176 │ │ │ │ │ │ └── li.productid:17 = p.productid:19 [outer=(17,19), constraints=(/17: (/NULL - ]; /19: (/NULL - ]), fd=(17)==(19), (19)==(17)] 2177 │ │ │ │ │ └── projections 2178 │ │ │ │ │ └── li.quantity:18 * p.cost:21 [as=column23:23, outer=(18,21)] 2179 │ │ │ │ └── filters 2180 │ │ │ │ ├── li.customerid:15 = orders1_.customerid:4 [outer=(4,15), constraints=(/4: (/NULL - ]; /15: (/NULL - ]), fd=(4)==(15), (15)==(4)] 2181 │ │ │ │ └── li.ordernumber:16 = orders1_.ordernumber:5 [outer=(5,16), constraints=(/5: (/NULL - ]; /16: (/NULL - ]), fd=(5)==(16), (16)==(5)] 2182 │ │ │ └── aggregations 2183 │ │ │ ├── sum [as=sum:24, outer=(23)] 2184 │ │ │ │ └── column23:23 2185 │ │ │ ├── const-agg [as=name:2, outer=(2)] 2186 │ │ │ │ └── name:2 2187 │ │ │ ├── const-agg [as=address:3, outer=(3)] 2188 │ │ │ │ └── address:3 2189 │ │ │ ├── const-agg [as=orderdate:6, outer=(6)] 2190 │ │ │ │ └── orderdate:6 2191 │ │ │ ├── const-agg [as=lineitems2_.quantity:10, outer=(10)] 2192 │ │ │ │ └── lineitems2_.quantity:10 2193 │ │ │ ├── const-agg [as=product3_.productid:11, outer=(11)] 2194 │ │ │ │ └── product3_.productid:11 2195 │ │ │ ├── const-agg [as=product3_.description:12, outer=(12)] 2196 │ │ │ │ └── product3_.description:12 2197 │ │ │ ├── const-agg [as=product3_.cost:13, outer=(13)] 2198 │ │ │ │ └── product3_.cost:13 2199 │ │ │ └── const-agg [as=product3_.numberavailable:14, outer=(14)] 2200 │ │ │ └── product3_.numberavailable:14 2201 │ │ ├── scan li 2202 │ │ │ └── columns: li.productid:27!null li.quantity:28 2203 │ │ └── filters 2204 │ │ └── li.productid:27 = product3_.productid:11 [outer=(11,27), constraints=(/11: (/NULL - ]; /27: (/NULL - ]), fd=(11)==(27), (27)==(11)] 2205 │ └── aggregations 2206 │ ├── sum [as=sum:29, outer=(28)] 2207 │ │ └── li.quantity:28 2208 │ ├── const-agg [as=name:2, outer=(2)] 2209 │ │ └── name:2 2210 │ ├── const-agg [as=address:3, outer=(3)] 2211 │ │ └── address:3 2212 │ ├── const-agg [as=orderdate:6, outer=(6)] 2213 │ │ └── orderdate:6 2214 │ ├── const-agg [as=lineitems2_.quantity:10, outer=(10)] 2215 │ │ └── lineitems2_.quantity:10 2216 │ ├── const-agg [as=product3_.productid:11, outer=(11)] 2217 │ │ └── product3_.productid:11 2218 │ ├── const-agg [as=product3_.description:12, outer=(12)] 2219 │ │ └── product3_.description:12 2220 │ ├── const-agg [as=product3_.cost:13, outer=(13)] 2221 │ │ └── product3_.cost:13 2222 │ ├── const-agg [as=product3_.numberavailable:14, outer=(14)] 2223 │ │ └── product3_.numberavailable:14 2224 │ └── const-agg [as=sum:24, outer=(24)] 2225 │ └── sum:24 2226 └── projections 2227 ├── sum:24 [as=formula103_1_:30, outer=(24)] 2228 └── sum:29 [as=formula104_3_:31, outer=(29)] 2229 2230 opt 2231 SELECT 2232 order0_.customerid AS customer1_1_0_, 2233 order0_.ordernumber AS ordernum2_1_0_, 2234 order0_.orderdate AS orderdat3_1_0_, 2235 ( 2236 SELECT 2237 sum(li.quantity * p.cost) 2238 FROM 2239 lineitem AS li, product AS p 2240 WHERE 2241 li.productid = p.productid 2242 AND li.customerid = order0_.customerid 2243 AND li.ordernumber = order0_.ordernumber 2244 ) 2245 AS formula105_0_, 2246 lineitems1_.customerid AS customer1_2_1_, 2247 lineitems1_.ordernumber AS ordernum2_2_1_, 2248 lineitems1_.productid AS producti3_2_1_, 2249 lineitems1_.customerid AS customer1_2_2_, 2250 lineitems1_.ordernumber AS ordernum2_2_2_, 2251 lineitems1_.productid AS producti3_2_2_, 2252 lineitems1_.quantity AS quantity4_2_2_ 2253 FROM 2254 customerorder AS order0_ 2255 LEFT JOIN lineitem AS lineitems1_ 2256 ON 2257 order0_.customerid = lineitems1_.customerid 2258 AND order0_.ordernumber = lineitems1_.ordernumber 2259 WHERE 2260 order0_.customerid = 'c111' AND order0_.ordernumber = 0; 2261 ---- 2262 project 2263 ├── columns: customer1_1_0_:1!null ordernum2_1_0_:2!null orderdat3_1_0_:3!null formula105_0_:18 customer1_2_1_:4 ordernum2_2_1_:5 producti3_2_1_:6 customer1_2_2_:4 ordernum2_2_2_:5 producti3_2_2_:6 quantity4_2_2_:7 2264 ├── key: (6) 2265 ├── fd: ()-->(1-3), (6)-->(4,5,7,18) 2266 ├── group-by 2267 │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null lineitems1_.customerid:4 lineitems1_.ordernumber:5 lineitems1_.productid:6 lineitems1_.quantity:7 sum:17 2268 │ ├── grouping columns: lineitems1_.productid:6 2269 │ ├── key: (6) 2270 │ ├── fd: ()-->(1-3), (6)-->(1-5,7,17) 2271 │ ├── right-join (hash) 2272 │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null lineitems1_.customerid:4 lineitems1_.ordernumber:5 lineitems1_.productid:6 lineitems1_.quantity:7 li.customerid:8 li.ordernumber:9 column16:16 2273 │ │ ├── fd: ()-->(1-3), (6)-->(4,5,7) 2274 │ │ ├── project 2275 │ │ │ ├── columns: column16:16 li.customerid:8!null li.ordernumber:9!null 2276 │ │ │ ├── inner-join (hash) 2277 │ │ │ │ ├── columns: li.customerid:8!null li.ordernumber:9!null li.productid:10!null li.quantity:11 p.productid:12!null cost:14 2278 │ │ │ │ ├── key: (8,9,12) 2279 │ │ │ │ ├── fd: (8-10)-->(11), (12)-->(14), (10)==(12), (12)==(10) 2280 │ │ │ │ ├── scan li 2281 │ │ │ │ │ ├── columns: li.customerid:8!null li.ordernumber:9!null li.productid:10!null li.quantity:11 2282 │ │ │ │ │ ├── key: (8-10) 2283 │ │ │ │ │ └── fd: (8-10)-->(11) 2284 │ │ │ │ ├── scan p 2285 │ │ │ │ │ ├── columns: p.productid:12!null cost:14 2286 │ │ │ │ │ ├── key: (12) 2287 │ │ │ │ │ └── fd: (12)-->(14) 2288 │ │ │ │ └── filters 2289 │ │ │ │ └── li.productid:10 = p.productid:12 [outer=(10,12), constraints=(/10: (/NULL - ]; /12: (/NULL - ]), fd=(10)==(12), (12)==(10)] 2290 │ │ │ └── projections 2291 │ │ │ └── li.quantity:11 * cost:14 [as=column16:16, outer=(11,14)] 2292 │ │ ├── left-join (merge) 2293 │ │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null lineitems1_.customerid:4 lineitems1_.ordernumber:5 lineitems1_.productid:6 lineitems1_.quantity:7 2294 │ │ │ ├── left ordering: +1,+2 2295 │ │ │ ├── right ordering: +4,+5 2296 │ │ │ ├── key: (6) 2297 │ │ │ ├── fd: ()-->(1-3), (6)-->(4,5,7) 2298 │ │ │ ├── scan order0_ 2299 │ │ │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null 2300 │ │ │ │ ├── constraint: /1/2: [/'c111'/0 - /'c111'/0] 2301 │ │ │ │ ├── cardinality: [0 - 1] 2302 │ │ │ │ ├── key: () 2303 │ │ │ │ └── fd: ()-->(1-3) 2304 │ │ │ ├── scan lineitems1_ 2305 │ │ │ │ ├── columns: lineitems1_.customerid:4!null lineitems1_.ordernumber:5!null lineitems1_.productid:6!null lineitems1_.quantity:7 2306 │ │ │ │ ├── constraint: /4/5/6: [/'c111'/0 - /'c111'/0] 2307 │ │ │ │ ├── key: (6) 2308 │ │ │ │ └── fd: ()-->(4,5), (6)-->(7) 2309 │ │ │ └── filters (true) 2310 │ │ └── filters 2311 │ │ ├── li.customerid:8 = order0_.customerid:1 [outer=(1,8), constraints=(/1: (/NULL - ]; /8: (/NULL - ]), fd=(1)==(8), (8)==(1)] 2312 │ │ └── li.ordernumber:9 = order0_.ordernumber:2 [outer=(2,9), constraints=(/2: (/NULL - ]; /9: (/NULL - ]), fd=(2)==(9), (9)==(2)] 2313 │ └── aggregations 2314 │ ├── sum [as=sum:17, outer=(16)] 2315 │ │ └── column16:16 2316 │ ├── const-agg [as=order0_.customerid:1, outer=(1)] 2317 │ │ └── order0_.customerid:1 2318 │ ├── const-agg [as=order0_.ordernumber:2, outer=(2)] 2319 │ │ └── order0_.ordernumber:2 2320 │ ├── const-agg [as=orderdate:3, outer=(3)] 2321 │ │ └── orderdate:3 2322 │ ├── const-agg [as=lineitems1_.customerid:4, outer=(4)] 2323 │ │ └── lineitems1_.customerid:4 2324 │ ├── const-agg [as=lineitems1_.ordernumber:5, outer=(5)] 2325 │ │ └── lineitems1_.ordernumber:5 2326 │ └── const-agg [as=lineitems1_.quantity:7, outer=(7)] 2327 │ └── lineitems1_.quantity:7 2328 └── projections 2329 └── sum:17 [as=formula105_0_:18, outer=(17)] 2330 2331 opt 2332 SELECT 2333 order0_.customerid AS customer1_10_, 2334 order0_.ordernumber AS ordernum2_10_, 2335 order0_.orderdate AS orderdat3_10_, 2336 ( 2337 SELECT 2338 sum(li.quantity * p.cost) 2339 FROM 2340 lineitem AS li, product AS p 2341 WHERE 2342 li.productid = p.productid 2343 AND li.customerid = order0_.customerid 2344 AND li.ordernumber = order0_.ordernumber 2345 ) 2346 AS formula273_ 2347 FROM 2348 customerorder AS order0_; 2349 ---- 2350 project 2351 ├── columns: customer1_10_:1!null ordernum2_10_:2!null orderdat3_10_:3!null formula273_:14 2352 ├── key: (1,2) 2353 ├── fd: (1,2)-->(3,14) 2354 ├── group-by 2355 │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null sum:13 2356 │ ├── grouping columns: order0_.customerid:1!null order0_.ordernumber:2!null 2357 │ ├── key: (1,2) 2358 │ ├── fd: (1,2)-->(3,13) 2359 │ ├── left-join (hash) 2360 │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null li.customerid:4 li.ordernumber:5 column12:12 2361 │ │ ├── fd: (1,2)-->(3) 2362 │ │ ├── scan order0_ 2363 │ │ │ ├── columns: order0_.customerid:1!null order0_.ordernumber:2!null orderdate:3!null 2364 │ │ │ ├── key: (1,2) 2365 │ │ │ └── fd: (1,2)-->(3) 2366 │ │ ├── project 2367 │ │ │ ├── columns: column12:12 li.customerid:4!null li.ordernumber:5!null 2368 │ │ │ ├── inner-join (hash) 2369 │ │ │ │ ├── columns: li.customerid:4!null li.ordernumber:5!null li.productid:6!null quantity:7 p.productid:8!null cost:10 2370 │ │ │ │ ├── key: (4,5,8) 2371 │ │ │ │ ├── fd: (4-6)-->(7), (8)-->(10), (6)==(8), (8)==(6) 2372 │ │ │ │ ├── scan li 2373 │ │ │ │ │ ├── columns: li.customerid:4!null li.ordernumber:5!null li.productid:6!null quantity:7 2374 │ │ │ │ │ ├── key: (4-6) 2375 │ │ │ │ │ └── fd: (4-6)-->(7) 2376 │ │ │ │ ├── scan p 2377 │ │ │ │ │ ├── columns: p.productid:8!null cost:10 2378 │ │ │ │ │ ├── key: (8) 2379 │ │ │ │ │ └── fd: (8)-->(10) 2380 │ │ │ │ └── filters 2381 │ │ │ │ └── li.productid:6 = p.productid:8 [outer=(6,8), constraints=(/6: (/NULL - ]; /8: (/NULL - ]), fd=(6)==(8), (8)==(6)] 2382 │ │ │ └── projections 2383 │ │ │ └── quantity:7 * cost:10 [as=column12:12, outer=(7,10)] 2384 │ │ └── filters 2385 │ │ ├── li.customerid:4 = order0_.customerid:1 [outer=(1,4), constraints=(/1: (/NULL - ]; /4: (/NULL - ]), fd=(1)==(4), (4)==(1)] 2386 │ │ └── li.ordernumber:5 = order0_.ordernumber:2 [outer=(2,5), constraints=(/2: (/NULL - ]; /5: (/NULL - ]), fd=(2)==(5), (5)==(2)] 2387 │ └── aggregations 2388 │ ├── sum [as=sum:13, outer=(12)] 2389 │ │ └── column12:12 2390 │ └── const-agg [as=orderdate:3, outer=(3)] 2391 │ └── orderdate:3 2392 └── projections 2393 └── sum:13 [as=formula273_:14, outer=(13)] 2394 2395 exec-ddl 2396 drop table customer, customerorder, lineitem, product 2397 ---- 2398 2399 # ------------------------------------------------------------------------------ 2400 # Query #12 2401 # org.hibernate.test.criteria.CriteriaQueryTest 2402 # ------------------------------------------------------------------------------ 2403 exec-ddl 2404 CREATE TABLE student ( 2405 studentid INT8 NOT NULL, 2406 name VARCHAR(255) NOT NULL, 2407 address_city VARCHAR(255), 2408 address_state VARCHAR(255), 2409 preferredcoursecode VARCHAR(255), 2410 PRIMARY KEY (studentid) 2411 ); 2412 ---- 2413 2414 exec-ddl 2415 CREATE TABLE enrolment ( 2416 studentid INT8 NOT NULL, 2417 coursecode VARCHAR(255) NOT NULL, 2418 semester INT2 NOT NULL, 2419 year INT2 NOT NULL, 2420 PRIMARY KEY (studentid, coursecode) 2421 ); 2422 ---- 2423 2424 opt 2425 SELECT 2426 this_.studentid AS studenti1_26_0_, 2427 this_.name AS name2_26_0_, 2428 this_.address_city AS address_3_26_0_, 2429 this_.address_state AS address_4_26_0_, 2430 this_.preferredcoursecode AS preferre5_26_0_ 2431 FROM 2432 student AS this_ 2433 WHERE 2434 EXISTS( 2435 SELECT 2436 enrolment_.studentid AS y0_ 2437 FROM 2438 enrolment AS enrolment_ 2439 WHERE 2440 enrolment_.year 2441 = ( 2442 SELECT 2443 max(maxstudentenrolment_.year) AS y0_ 2444 FROM 2445 enrolment AS maxstudentenrolment_ 2446 WHERE 2447 this_.preferredcoursecode = maxstudentenrolment_.coursecode 2448 ) 2449 ); 2450 ---- 2451 group-by 2452 ├── columns: studenti1_26_0_:1!null name2_26_0_:2!null address_3_26_0_:3 address_4_26_0_:4 preferre5_26_0_:5!null 2453 ├── grouping columns: this_.studentid:1!null 2454 ├── key: (1) 2455 ├── fd: (1)-->(2-5) 2456 ├── select 2457 │ ├── columns: this_.studentid:1!null name:2!null address_city:3 address_state:4 preferredcoursecode:5!null enrolment_.studentid:6!null enrolment_.coursecode:7!null enrolment_.year:9!null max:14!null 2458 │ ├── key: (1,6,7) 2459 │ ├── fd: (1)-->(2-5), (6,7)-->(9), (1,6,7)-->(2-5,9,14), (9)==(14), (14)==(9) 2460 │ ├── group-by 2461 │ │ ├── columns: this_.studentid:1!null name:2!null address_city:3 address_state:4 preferredcoursecode:5!null enrolment_.studentid:6!null enrolment_.coursecode:7!null enrolment_.year:9!null max:14!null 2462 │ │ ├── grouping columns: this_.studentid:1!null enrolment_.studentid:6!null enrolment_.coursecode:7!null 2463 │ │ ├── key: (1,6,7) 2464 │ │ ├── fd: (1)-->(2-5), (6,7)-->(9), (1,6,7)-->(2-5,9,14) 2465 │ │ ├── inner-join (cross) 2466 │ │ │ ├── columns: this_.studentid:1!null name:2!null address_city:3 address_state:4 preferredcoursecode:5!null enrolment_.studentid:6!null enrolment_.coursecode:7!null enrolment_.year:9!null maxstudentenrolment_.coursecode:11!null maxstudentenrolment_.year:13!null 2467 │ │ │ ├── fd: (1)-->(2-5), (6,7)-->(9), (5)==(11), (11)==(5) 2468 │ │ │ ├── inner-join (hash) 2469 │ │ │ │ ├── columns: this_.studentid:1!null name:2!null address_city:3 address_state:4 preferredcoursecode:5!null maxstudentenrolment_.coursecode:11!null maxstudentenrolment_.year:13!null 2470 │ │ │ │ ├── fd: (1)-->(2-5), (5)==(11), (11)==(5) 2471 │ │ │ │ ├── scan maxstudentenrolment_ 2472 │ │ │ │ │ └── columns: maxstudentenrolment_.coursecode:11!null maxstudentenrolment_.year:13!null 2473 │ │ │ │ ├── scan this_ 2474 │ │ │ │ │ ├── columns: this_.studentid:1!null name:2!null address_city:3 address_state:4 preferredcoursecode:5 2475 │ │ │ │ │ ├── key: (1) 2476 │ │ │ │ │ └── fd: (1)-->(2-5) 2477 │ │ │ │ └── filters 2478 │ │ │ │ └── preferredcoursecode:5 = maxstudentenrolment_.coursecode:11 [outer=(5,11), constraints=(/5: (/NULL - ]; /11: (/NULL - ]), fd=(5)==(11), (11)==(5)] 2479 │ │ │ ├── scan enrolment_ 2480 │ │ │ │ ├── columns: enrolment_.studentid:6!null enrolment_.coursecode:7!null enrolment_.year:9!null 2481 │ │ │ │ ├── key: (6,7) 2482 │ │ │ │ └── fd: (6,7)-->(9) 2483 │ │ │ └── filters (true) 2484 │ │ └── aggregations 2485 │ │ ├── max [as=max:14, outer=(13)] 2486 │ │ │ └── maxstudentenrolment_.year:13 2487 │ │ ├── const-agg [as=enrolment_.year:9, outer=(9)] 2488 │ │ │ └── enrolment_.year:9 2489 │ │ ├── const-agg [as=name:2, outer=(2)] 2490 │ │ │ └── name:2 2491 │ │ ├── const-agg [as=address_city:3, outer=(3)] 2492 │ │ │ └── address_city:3 2493 │ │ ├── const-agg [as=address_state:4, outer=(4)] 2494 │ │ │ └── address_state:4 2495 │ │ └── const-agg [as=preferredcoursecode:5, outer=(5)] 2496 │ │ └── preferredcoursecode:5 2497 │ └── filters 2498 │ └── enrolment_.year:9 = max:14 [outer=(9,14), constraints=(/9: (/NULL - ]; /14: (/NULL - ]), fd=(9)==(14), (14)==(9)] 2499 └── aggregations 2500 ├── const-agg [as=name:2, outer=(2)] 2501 │ └── name:2 2502 ├── const-agg [as=address_city:3, outer=(3)] 2503 │ └── address_city:3 2504 ├── const-agg [as=address_state:4, outer=(4)] 2505 │ └── address_state:4 2506 └── const-agg [as=preferredcoursecode:5, outer=(5)] 2507 └── preferredcoursecode:5 2508 2509 exec-ddl 2510 drop table student, enrolment 2511 ---- 2512 2513 # ------------------------------------------------------------------------------ 2514 # Query #13 2515 # org.hibernate.test.subselectfetch.SubselectFetchWithFormulaTest 2516 # TODO(andyk): Need to decorrelate LeftJoin -> Project complex. 2517 # ------------------------------------------------------------------------------ 2518 exec-ddl 2519 CREATE TABLE t_name (id INT4 NOT NULL, c_name VARCHAR(255), PRIMARY KEY (id)); 2520 ---- 2521 2522 opt 2523 SELECT 2524 this_.id AS id1_0_0_, 2525 this_.c_name AS c_name2_0_0_, 2526 (SELECT length(this_.c_name) FROM t_name WHERE this_.id = t_name.id) 2527 AS formula0_0_ 2528 FROM 2529 t_name AS this_; 2530 ---- 2531 project 2532 ├── columns: id1_0_0_:1!null c_name2_0_0_:2 formula0_0_:6 2533 ├── immutable 2534 ├── key: (1) 2535 ├── fd: (1)-->(2), (2)-->(6) 2536 ├── inner-join (merge) 2537 │ ├── columns: this_.id:1!null this_.c_name:2 t_name.id:3!null 2538 │ ├── left ordering: +1 2539 │ ├── right ordering: +3 2540 │ ├── key: (3) 2541 │ ├── fd: (1)-->(2), (1)==(3), (3)==(1) 2542 │ ├── scan this_ 2543 │ │ ├── columns: this_.id:1!null this_.c_name:2 2544 │ │ ├── key: (1) 2545 │ │ ├── fd: (1)-->(2) 2546 │ │ └── ordering: +1 2547 │ ├── scan t_name 2548 │ │ ├── columns: t_name.id:3!null 2549 │ │ ├── key: (3) 2550 │ │ └── ordering: +3 2551 │ └── filters (true) 2552 └── projections 2553 └── length(this_.c_name:2) [as=formula0_0_:6, outer=(2), immutable] 2554 2555 exec-ddl 2556 drop table t_name 2557 ----