sql: Hibernate tests - correlated subqueries #26658

BramGruneir · 2018-06-12T20:34:45Z

This issue is just a dumping ground and my notes from running the hibernate test suite and how it pertains to correlated subqueries.

The overall goal for this issue is to

list common queries that current fail due to their correlatedness
integrate these queries or ones like them into our own testing
have one place to discuss which ones are working and are not

org.hibernate.userguide.collections.UnidirectionalMapTest testLifecycle
Fails due to the following correlated subquery.

create table Person (
  id int8 not null,
  primary key (id)
)
create table Phone (
  id int8 not null,
  "number" varchar(255),
  since timestamp,
  type int4,
  primary key (id)
)
create table phone_register (
  phone_id int8 not null,
  person_id int8 not null,
  primary key (phone_id, person_id)
)
alter table if exists phone_register 
  add constraint UK_tk8a6rlawn3vkedaekw4mnfbl unique (person_id)
alter table if exists phone_register 
  add constraint FKc3jajlx41lw6clbygbw8wm65w 
  foreign key (person_id) 
  references Phone
alter table if exists phone_register 
  add constraint FK6npoomh1rp660o1b55py9ndw4 
  foreign key (phone_id) 
  references Person

select
  phoneregis0_.phone_id as phone_id1_2_0_,
  phoneregis0_.person_id as person_i2_2_0_,
  (
    select a10.since
    from Phone a10
    where a10.id=phoneregis0_.person_id
  ) as formula159_0_,
  unidirecti1_.id as id1_1_1_,
  unidirecti1_."number" as number2_1_1_,
  unidirecti1_.since as since3_1_1_,
  unidirecti1_.type as type4_1_1_
from
  phone_register phoneregis0_
inner join Phone unidirecti1_
  on phoneregis0_.person_id=unidirecti1_.id
where phoneregis0_.phone_id=1;

BramGruneir · 2018-06-13T20:24:34Z

From org.hibernate.userguide.criteria.CriteriaTest:

test_criteria_from_fetch_example
test_criteria_from_join_example
test_criteria_from_multiple_root_example

create table Person (
   id int8 not null,
    address varchar(255),
    createdOn timestamp,
    name varchar(255),
    nickName varchar(255),
    version int4 not null,
    primary key (id)
)

create table Person_addresses (
   Person_id int8 not null,
    addresses varchar(255),
    addresses_KEY varchar(255) not null,
    primary key (Person_id, addresses_KEY)
)

create table Phone (
   id int8 not null,
    phone_number varchar(255),
    phone_type varchar(255),
    person_id int8,
    order_id int4,
    primary key (id)
)
create table phone_call (
   id int8 not null,
    duration int4 not null,
    call_timestamp timestamp,
    phone_id int8,
    primary key (id)
)

create table Partner (
   id int8 not null,
    name varchar(255),
    version int4 not null,
    primary key (id)
)

select
    phone0_.id as id1_6_0_,
    person1_.id as id1_4_1_,
    phone0_.phone_number as phone_nu2_6_0_,
    phone0_.person_id as person_i4_6_0_,
    phone0_.phone_type as phone_ty3_6_0_,
    addresses2_.Person_id as Person_i1_5_0__,
    addresses2_.addresses as addresse2_5_0__,
    addresses2_.addresses_KEY as addresse3_0__,
    person1_.address as address2_4_1_,
    person1_.createdOn as createdO3_4_1_,
    person1_.name as name4_4_1_,
    person1_.nickName as nickName5_4_1_,
    person1_.version as version6_4_1_,
    addresses2_.Person_id as Person_i1_5_0__,
    addresses2_.addresses as addresse2_5_0__,
    addresses2_.addresses_KEY as addresse3_0__ 
from
    Phone phone0_ 
inner join
    Person person1_ 
        on phone0_.person_id=person1_.id 
inner join
    Person_addresses addresses2_ 
        on person1_.id=addresses2_.Person_id 
where
    exists (
        select
            calls3_.id 
        from
            phone_call calls3_ 
        where
            phone0_.id=calls3_.phone_id
    )

select
    phone0_.id as id1_6_,
    phone0_.phone_number as phone_nu2_6_,
    phone0_.person_id as person_i4_6_,
    phone0_.phone_type as phone_ty3_6_ 
from
    Phone phone0_ 
inner join
    Person person1_ 
        on phone0_.person_id=person1_.id 
inner join
    Person_addresses addresses2_ 
        on person1_.id=addresses2_.Person_id 
where
    exists (
        select
            calls3_.id 
        from
            phone_call calls3_ 
        where
            phone0_.id=calls3_.phone_id
    )

select
    person0_.id as id1_4_0_,
    partner1_.id as id1_2_1_,
    person0_.address as address2_4_0_,
    person0_.createdOn as createdO3_4_0_,
    person0_.name as name4_4_0_,
    person0_.nickName as nickName5_4_0_,
    person0_.version as version6_4_0_,
    partner1_.name as name2_2_1_,
    partner1_.version as version3_2_1_ 
from
    Person person0_ cross 
join
    Partner partner1_ 
where
    person0_.address=? 
    and (
        exists (
            select
                phones2_.id 
            from
                Phone phones2_ 
            where
                person0_.id=phones2_.person_id
        )
    ) 
    and (
        partner1_.name like ?
    ) 
    and partner1_.version=0

BramGruneir · 2018-06-13T20:41:22Z

From org.hibernate.userguide.envers.DefaultAuditTest test

create table Customer_AUD (
   id int8 not null,
    REV int4 not null,
    REVTYPE int2,
    created_on timestamp,
    firstName varchar(255),
    lastName varchar(255),
    primary key (id, REV)
)

select
    defaultaud0_.id as id1_1_,
    defaultaud0_.REV as REV2_1_,
    defaultaud0_.REVTYPE as REVTYPE3_1_,
    defaultaud0_.created_on as created_4_1_,
    defaultaud0_.firstName as firstNam5_1_,
    defaultaud0_.lastName as lastName6_1_ 
from
    Customer_AUD defaultaud0_ 
where
    defaultaud0_.REV=(
        select
            max(defaultaud1_.REV) 
        from
            Customer_AUD defaultaud1_ 
        where
            defaultaud1_.REV<=? 
            and defaultaud0_.id=defaultaud1_.id
    ) 
    and defaultaud0_.REVTYPE<>?

BramGruneir · 2018-06-13T20:43:45Z

org.hibernate.userguide.envers.QueryAuditTest test

create table Customer_AUD (
   id int8 not null,
    REV int4 not null,
    REVTYPE int2,
    REVEND int4,
    created_on timestamp,
    firstName varchar(255),
    lastName varchar(255),
    address_id int8,
    primary key (id, REV)
)
select
    queryaudit0_.id as id1_3_,
    queryaudit0_.REV as REV2_3_,
    queryaudit0_.REVTYPE as REVTYPE3_3_,
    queryaudit0_.REVEND as REVEND4_3_,
    queryaudit0_.created_on as created_5_3_,
    queryaudit0_.firstName as firstNam6_3_,
    queryaudit0_.lastName as lastName7_3_,
    queryaudit0_.address_id as address_8_3_ 
from
    Customer_AUD queryaudit0_ 
where
    queryaudit0_.REVTYPE<>? 
    and queryaudit0_.REV=(
        select
            max(queryaudit1_.REV) 
        from
            Customer_AUD queryaudit1_ 
        where
            queryaudit1_.id=queryaudit0_.id
    ) 
order by
    queryaudit0_.REV asc

BramGruneir · 2018-06-13T20:58:00Z

org.hibernate.userguide.hql.HQLTest
- test_hql_all_subquery_comparison_qualifier_example
- test_hql_collection_expressions_example_1
- test_hql_collection_expressions_example_10
- test_hql_collection_expressions_example_2
- test_hql_collection_expressions_example_3
- test_hql_collection_expressions_example_4
- test_hql_collection_expressions_example_5
- test_hql_collection_expressions_example_6
- test_hql_collection_expressions_example_8
- test_hql_collection_expressions_example_9
- test_hql_collection_index_operator_example_3
- test_hql_empty_collection_predicate_example_1
- test_hql_empty_collection_predicate_example_2
- test_hql_group_by_example_4
- test_hql_member_of_collection_predicate_example_1
- test_hql_member_of_collection_predicate_example_2
org.hibernate.jpa.test.criteria.enumcollection.EnumIsMemberTest
- testQueryEnumCollection

create table Phone (
   id int8 not null,
    phone_number varchar(255),
    phone_type varchar(255),
    person_id int8,
    order_id int4,
    primary key (id)
)
create table phone_call (
   id int8 not null,
    duration int4 not null,
    call_timestamp timestamp,
    phone_id int8,
    primary key (id)
)
create table Person (
   id int8 not null,
    address varchar(255),
    createdOn timestamp,
    name varchar(255),
    nickName varchar(255),
    version int4 not null,
    primary key (id)
)
create table Phone_repairTimestamps (
   Phone_id int8 not null,
   repairTimestamps timestamp
)
create table Person_addresses (
    Person_id int8 not null,
    addresses varchar(255),
    addresses_KEY varchar(255) not null,
    primary key (Person_id, addresses_KEY)
)

select
    distinct person2_.id as id1_2_,
    person2_.address as address2_2_,
    person2_.createdOn as createdO3_2_,
    person2_.name as name4_2_,
    person2_.nickName as nickName5_2_,
    person2_.version as version6_2_ 
from
    Phone phone0_ 
inner join
    phone_call calls1_ 
        on phone0_.id=calls1_.phone_id 
inner join
    Person person2_ 
        on phone0_.person_id=person2_.id 
where
    50>all (
        select
            call3_.duration 
        from
            phone_call call3_ 
        where
            call3_.phone_id=phone0_.id
select
    phone0_.id as id1_4_,
    phone0_.phone_number as phone_nu2_4_,
    phone0_.person_id as person_i4_4_,
    phone0_.phone_type as phone_ty3_4_ 
from
    Phone phone0_ 
where
    (
        select
            max(calls1_.id) 
        from
            phone_call calls1_ 
        where
            phone0_.id=calls1_.phone_id
    )=?
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    (
        select
            count(phones1_.person_id) 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )=2
select
    phone0_.id as id1_4_,
    phone0_.phone_number as phone_nu2_4_,
    phone0_.person_id as person_i4_4_,
    phone0_.phone_type as phone_ty3_4_ 
from
    Phone phone0_ 
where
    (
        select
            min(calls1_.id) 
        from
            phone_call calls1_ 
        where
            phone0_.id=calls1_.phone_id
    )=?
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    (
        select
            max(phones1_.order_id) 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )=0
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    ? in (
        select
            phones1_.id 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    ?=some (
        select
            phones1_.id 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    exists (
        select
            phones1_.id 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )
select
    phone0_.id as id1_4_,
    phone0_.phone_number as phone_nu2_4_,
    phone0_.person_id as person_i4_4_,
    phone0_.phone_type as phone_ty3_4_ 
from
    Phone phone0_ 
where
    current_date>all (
        select
            repairtime1_.repairTimestamps 
        from
            Phone_repairTimestamps repairtime1_ 
        where
            phone0_.id=repairtime1_.Phone_id
    )
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    1 in (
        select
            phones1_.order_id 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ cross 
join
    Phone phones2_ 
where
    person0_.id=phones2_.person_id 
    and phones2_.order_id = (
        select
            max(phones1_.order_id) 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    ) 
    and phones2_.phone_type='LAND_LINE'
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    not (exists (select
        phones1_.id 
    from
        Phone phones1_ 
    where
        person0_.id=phones1_.person_id))
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    exists (
        select
            phones1_.id 
        from
            Phone phones1_ 
        where
            person0_.id=phones1_.person_id
    )
select
    phone0_.id as id1_4_,
    phone0_.phone_number as phone_nu2_4_,
    phone0_.person_id as person_i4_4_,
    phone0_.phone_type as phone_ty3_4_ 
from
    Phone phone0_ 
where
    not (exists (select
        calls1_.id 
    from
        phone_call calls1_ 
    where
        phone0_.id=calls1_.phone_id))
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    'Home address' in (
        select
            addresses1_.addresses 
        from
            Person_addresses addresses1_ 
        where
            person0_.id=addresses1_.Person_id
    )
select
    person0_.id as id1_2_,
    person0_.address as address2_2_,
    person0_.createdOn as createdO3_2_,
    person0_.name as name4_2_,
    person0_.nickName as nickName5_2_,
    person0_.version as version6_2_ 
from
    Person person0_ 
where
    'Home address' not in  (
        select
            addresses1_.addresses 
        from
            Person_addresses addresses1_ 
        where
            person0_.id=addresses1_.Person_id
    )

awoods187 · 2018-06-13T21:41:16Z

cc @andy-kimball

BramGruneir · 2018-06-14T18:36:05Z

The following hibernate tests all fail for the same style of correlated subquery:

org.hibernate.jpa.test.criteria.components.ComponentInWhereClauseTest
- testSizeExpressionForTheElementCollectionPropertyOfAComponent
- testSizeExpressionForTheElementCollectionPropertyOfASubComponent
- testSizeExpressionForTheOneToManyPropertyOfAComponent
org.hibernate.jpa.test.criteria.components.EntitySuperclassComponentWithCollectionTest
- testSizeExpressionForTheElementCollectionPropertyOfAComponent
- testSizeExpressionForTheElementCollectionPropertyOfASubComponent
- testSizeExpressionForTheOneToManyPropertyOfAComponent
org.hibernate.jpa.test.criteria.components.MappedSuperclassComponentWithCollectionTest
- testSizeExpressionForTheElementCollectionPropertyOfAComponent
- testSizeExpressionForTheElementCollectionPropertyOfASubComponent
- testSizeExpressionForTheOneToManyPropertyOfAComponent

Here's an example one:

create table EMPLOYEE (
   id int8 not null,
    email varchar(255),
    currentProject_id int8,
    primary key (id)
)
create table Employee_phones (
   Employee_id int8 not null,
    phone_number varchar(255)
)

select
    componenti0_.id as id1_0_,
    componenti0_.email as email2_0_,
    componenti0_.currentProject_id as currentP3_0_ 
from
    EMPLOYEE componenti0_ 
where
    (
        select
            count(phones1_.Employee_id) 
        from
            Employee_phones phones1_ 
        where
            componenti0_.id=phones1_.Employee_id
    )=1

andy-kimball · 2018-06-14T19:25:03Z

Whoops, hit wrong button...
Are you planning to run this suite with the cost-based optimizer enabled?

BramGruneir · 2018-06-14T20:47:07Z

I would love to and I'll do that next. I think a majority of the failures I'm seeing are due to hibernate's use of correlated subqueries. But I bet there will be more failures that these are hiding.

BramGruneir · 2018-06-14T20:54:10Z

This comment is just going to be dumping ground for a list of all failures due to correlated subqueries and I'll keep updating it.

Then, when I rerun the test suite with the cost-based optimizer enabled, I'll check the new failure set against this list.

org.hibernate.jpa.test.criteria.components.ComponentInWhereClauseTest
- testSizeExpressionForTheElementCollectionPropertyOfAComponent
- testSizeExpressionForTheElementCollectionPropertyOfASubComponent
- testSizeExpressionForTheOneToManyPropertyOfAComponent
org.hibernate.jpa.test.criteria.components.EntitySuperclassComponentWithCollectionTest
- testSizeExpressionForTheElementCollectionPropertyOfAComponent
- testSizeExpressionForTheElementCollectionPropertyOfASubComponent
- testSizeExpressionForTheOneToManyPropertyOfAComponent
org.hibernate.jpa.test.criteria.components.MappedSuperclassComponentWithCollectionTest
- testSizeExpressionForTheElementCollectionPropertyOfAComponent
- testSizeExpressionForTheElementCollectionPropertyOfASubComponent
- testSizeExpressionForTheOneToManyPropertyOfAComponent
org.hibernate.jpa.test.criteria.enumcollection.EnumIsMemberTest
- testQueryEnumCollection
org.hibernate.jpa.test.criteria.paths.PluralAttributeExpressionsTest
- testCollectionIsEmptyCriteria
- testCollectionIsEmptyHql
- testCollectionSizeCriteria
- testCollectionSizeHql
- testElementMapSizeCriteria
- testElementMapSizeHql
- testEntityMapIsEmptyCriteria
- testEntityMapIsEmptyHql
- testEntityMapSizeCriteria
- testEntityMapSizeHql
org.hibernate.jpa.test.criteria.subquery.CorrelatedSubqueryTest
- testBasicCorrelation
- testCorrelationExplicitSelectionCorrelation
- testRestrictedCorrelation
- testRestrictedCorrelationNoExplicitSelection
org.hibernate.jpa.test.graphs.EntityGraphTest
- loadIsMemeberQueriedCollection
org.hibernate.jpa.test.graphs.queryhint.QueryHintEntityGraphTest
- testEntityGraphWithCollectionSubquery
org.hibernate.test.annotations.collectionelement.QueryTest
- testMemberOfSyntax
org.hibernate.test.annotations.enumerated.EnumeratedTypeTest
- testSet
org.hibernate.test.annotations.indexcoll.IndexedCollectionTest
- testMapKeyOnManyToMany
- testMapKeyOnManyToManyOnId
- testSortedMap
org.hibernate.test.annotations.indexcoll.MapKeyTest
- testMapKeyOnEmbeddedId
org.hibernate.test.bidi.AuctionTest2
- testLazy
org.hibernate.test.cid.CompositeIdTest
- testCompositeIds
- testMultipleCollectionFetch
- testNonLazyFetch

BramGruneir · 2018-06-18T17:41:30Z

create table Company (
   id int8 not null,
    location_id int8,
    primary key (id)
)
create table Company_Employee (
   Company_id int8 not null,
    employees_id int8 not null,
    primary key (Company_id, employees_id)
)
create table Employee (
   id int8 not null,
    primary key (id)
)
create table Manager (
   id int8 not null,
    primary key (id)
)
create table Location (
   id int8 not null,
    address varchar(255),
    zip int4 not null,
    primary key (id)
)

select
    company0_.id as id1_0_0_,
    location3_.id as id1_8_1_,
    company0_.location_id as location2_0_0_,
    location3_.address as address2_8_1_,
    location3_.zip as zip3_8_1_ 
from
    Company company0_ 
left outer join
    Location location3_ 
        on company0_.location_id=location3_.id 
where
    not (exists (select
        employee2_.id 
    from
        Company_Employee employees1_,
        ( select
            id,
            0 as clazz_ 
        from
            Employee 
        union
        all select
            id,
            1 as clazz_ 
        from
            Manager ) employee2_ 
    where
        company0_.id=employees1_.Company_id 
        and employees1_.employees_id=employee2_.id))

BramGruneir · 2018-06-18T20:30:02Z

from org.hibernate.test.annotations.indexcoll.IndexedCollectionTest testMapKeyOnManyToMany

    create table News (
       news_id int4 not null,
        detail varchar(255),
        title varchar(255),
        primary key (news_id)
    )
    create table Newspaper (
       id int4 not null,
        name varchar(255),
        primary key (id)
    )z
    create table Newspaper_News (
       Newspaper_id int4 not null,
        news_news_id int4 not null,
        primary key (Newspaper_id, news_news_id)
    )

    select
        news0_.Newspaper_id as Newspape1_23_0_,
        news0_.news_news_id as news_new2_23_0_,
        (select
            a0.title 
        from
            News a0 
        where
            a0.news_id=news0_.news_news_id) as formula140_0_,
        news1_.news_id as news_id1_21_1_,
        news1_.detail as detail2_21_1_,
        news1_.title as title3_21_1_ 
    from
        Newspaper_News news0_ 
    inner join
        News news1_ 
            on news0_.news_news_id=news1_.news_id 
    where
        news0_.Newspaper_id=?

BramGruneir · 2018-06-18T20:55:16Z

From org.hibernate.test.annotations.indexcoll.MapKeyTest testMapKeyOnEmbeddedId

create table GenerationGroup (
   id int4 not null,
    age varchar(255),
    culture varchar(255),
    description varchar(255),
    primary key (id)
)
create table GenerationUser (
   id int4 not null,
    primary key (id)
)
create table GenerationUser_GenerationGroup (
   GenerationUser_id int4 not null,
    ref_id int4 not null,
    primary key (GenerationUser_id, ref_id)
)

SELECT ref0_.generationuser_id AS generati1_2_0_
      ,ref0_.ref_id AS ref_id2_2_0_
      ,(SELECT a13.age 
        FROM generationgroup AS a13 
        WHERE a13.id = ref0_.ref_id) AS formula131_0_
      ,(SELECT a15.culture 
        FROM generationgroup AS a15 
        WHERE a15.id = ref0_.ref_id) AS formula132_0_
      ,(SELECT a13.description
        FROM generationgroup AS a13
        WHERE a13.id = ref0_.ref_id) AS formula133_0_
      ,generation1_.id AS id1_0_1_
      ,generation1_.age AS age2_0_1_
      ,generation1_.culture AS culture3_0_1_
      ,generation1_.description AS descript4_0_1_
FROM generationuser_generationgroup AS ref0_
INNER JOIN generationgroup AS generation1_ 
  ON ref0_.ref_id = generation1_.id
WHERE ref0_.generationuser_id = 1;

BramGruneir · 2018-06-18T21:23:39Z

From org.hibernate.test.bidi.AuctionTest2 testLazy

    create table TAuction2 (
       id int8 not null,
        description varchar(255),
        endDatetime timestamp,
        successfulBid int8,
        primary key (id)
    )
    create table TBid2 (
       id int8 not null,
        amount numeric(31, 19),
        createdDatetime timestamp,
        auctionId int8,
        primary key (id)
    )

    select
        bids0_.auctionId as auctionI4_1_0_,
        bids0_.id as id1_1_0_,
        bids0_.id as id1_1_1_,
        bids0_.amount as amount2_1_1_,
        bids0_.createdDatetime as createdD3_1_1_,
        bids0_.auctionId as auctionI4_1_1_,
        exists(select
            a.id 
        from
            TAuction2 a 
        where
            a.successfulBid=bids0_.id) as formula41_1_ 
    from
        TBid2 bids0_ 
    where
        bids0_.auctionId=?

awoods187 · 2018-06-19T17:31:23Z

CC @solongordon here is a listed of correlated subqueries from Hibernate that we can test to make sure either the optimizer or apply srf process correctly

BramGruneir · 2018-06-21T15:15:27Z

@andy-kimball I have rerun the hibernate test suite with the cost based optimizer set to on and from my list of tests above with known correlated subquery failures, each one sadly still fails due to the correlated subquery.

knz · 2018-06-22T11:02:01Z

@BramGruneir what is the action item here exactly?

I seem to understand that the goal of this issue is to:

integrate these queries in our test suite
mark them currently as skipped
create piecemeal improvement requests for the optimizer to support them, and link the corresponding new issues into the test cases created at step 1

If that is the proper understanding, please update the PR description at the top accordingly. Also mark it as meta-issue. Thank you.

BramGruneir · 2018-07-02T15:19:20Z

After running with the optimizer enabled, of the list above, only the following failures remain:
(just note that this is a sampling of all the failures and not an exhaustive list)

~~org.hibernate.jpa.test.criteria.components.ComponentInWhereClauseTest~~
- ~~testSizeExpressionForTheElementCollectionPropertyOfAComponent~~
- ~~testSizeExpressionForTheElementCollectionPropertyOfASubComponent~~
- ~~testSizeExpressionForTheOneToManyPropertyOfAComponent~~
~~org.hibernate.jpa.test.criteria.components.EntitySuperclassComponentWithCollectionTest~~
- ~~testSizeExpressionForTheElementCollectionPropertyOfAComponent~~
- ~~testSizeExpressionForTheElementCollectionPropertyOfASubComponent~~
- ~~testSizeExpressionForTheOneToManyPropertyOfAComponent~~
~~org.hibernate.jpa.test.criteria.components.MappedSuperclassComponentWithCollectionTest~~
- ~~testSizeExpressionForTheElementCollectionPropertyOfAComponent~~
- ~~testSizeExpressionForTheElementCollectionPropertyOfASubComponent~~
- ~~testSizeExpressionForTheOneToManyPropertyOfAComponent~~
~~org.hibernate.jpa.test.criteria.enumcollection.EnumIsMemberTest~~
- ~~testQueryEnumCollection~~
org.hibernate.jpa.test.criteria.paths.PluralAttributeExpressionsTest
- ~~testCollectionIsEmptyCriteria~~
- ~~testCollectionIsEmptyHql~~
- ~~testCollectionSizeCriteria~~
- ~~testCollectionSizeHql~~
- ~~testElementMapSizeCriteria~~
- ~~testElementMapSizeHql~~
- testEntityMapIsEmptyCriteria - fail: ERROR: could not decorrelate subquery
- testEntityMapIsEmptyHql - fail: ERROR: could not decorrelate subquery
- ~~testEntityMapSizeCriteria~~
- ~~testEntityMapSizeHql~~
~~org.hibernate.jpa.test.criteria.subquery.CorrelatedSubqueryTest~~
- ~~testBasicCorrelation~~
- ~~testCorrelationExplicitSelectionCorrelation~~
- ~~testRestrictedCorrelation~~
- ~~testRestrictedCorrelationNoExplicitSelection~~
~~org.hibernate.jpa.test.graphs.EntityGraphTest~~
- ~~loadIsMemeberQueriedCollection~~
org.hibernate.jpa.test.graphs.queryhint.QueryHintEntityGraphTest
- testEntityGraphWithCollectionSubquery - fail: ERROR: could not decorrelate subquery
~~org.hibernate.test.annotations.collectionelement.QueryTest~~
- ~~testMemberOfSyntax~~
~~org.hibernate.test.annotations.enumerated.EnumeratedTypeTest~~
- ~~testSet~~
org.hibernate.test.annotations.indexcoll.IndexedCollectionTest
- testMapKeyOnManyToMany - fail: ERROR: could not decorrelate subquery
- testMapKeyOnManyToManyOnId - fail: ERROR: could not decorrelate subquery
- testSortedMap - fail: ERROR: could not decorrelate subquery
org.hibernate.test.annotations.indexcoll.MapKeyTest
- testMapKeyOnEmbeddedId - fail: ERROR: could not decorrelate subquery
~~org.hibernate.test.bidi.AuctionTest2~~
- ~~testLazy~~
org.hibernate.test.cid.CompositeIdTest
- testCompositeIds - fail: ERROR: could not decorrelate subquery
- testMultipleCollectionFetch - fail: ERROR: could not decorrelate subquery
- testNonLazyFetch - fail: ERROR: could not decorrelate subquery

awoods187 · 2018-07-02T15:36:10Z

Awesome work--thanks for reviewing

knz · 2018-07-02T16:07:33Z

Would it be possible to extract the SQL of the remaining test failures, so that the opt team gets something concrete to chew on (perhaps we can improve the situation further until the release).

BramGruneir · 2018-07-02T16:21:04Z

Examples of each type of failure are already present in this issue. Discussed this with @andy-kimball already offline.

knz · 2018-07-02T16:25:21Z

@BramGruneir if you do not specify the work that needs to be done in this issue, and if your summary of the situation is "andy already knows what needs to be done, we discussed this offline", the issue becomes unactionable and we may as well close it.

If you think there is work to be done, please spell it out in the issue description. If we need to extend our correlation support in one or another direction, please detail here what these directions are.

An "offline discussion" is not adequate for transparency.

andy-kimball · 2018-07-02T16:30:51Z

The action item is that I'm investigating the examples Bram has already included in this issue. Once I've enabled additional cases, we can re-run the suite and see what remains. I don't think we gain anything by posting additional examples in this issue until I've looked at what Bram has posted already.

knz · 2018-07-02T16:34:41Z

Thanks that clarification was good.

BramGruneir · 2018-07-19T15:05:53Z

I reran the hibernate test suite and the following seem to be the only failures that return a could not decorrelate subquery error:

-- org.hibernate.test.cid.CompositeIdTest.html

CREATE TABLE customer (
  customerid VARCHAR(10) NOT NULL,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(200) NOT NULL,
  PRIMARY KEY (customerid)
);

CREATE TABLE customerorder (
  customerid VARCHAR(10) NOT NULL,
  ordernumber INT4 NOT NULL,
  orderdate DATE NOT NULL,
  PRIMARY KEY (customerid, ordernumber)
);

CREATE TABLE lineitem (
  customerid VARCHAR(10) NOT NULL,
  ordernumber INT4 NOT NULL,
  productid VARCHAR(10) NOT NULL,
  quantity INT4,
  PRIMARY KEY (customerid, ordernumber, productid)
);
    
CREATE TABLE product (
  productid VARCHAR(10) NOT NULL,
  description VARCHAR(200) NOT NULL,
  cost NUMERIC(19,2),
  numberavailable INT4,
  PRIMARY KEY (productid)
);    
    
SELECT
  order0_.customerid AS customer1_1_0_,
  order0_.ordernumber AS ordernum2_1_0_,
  order0_.orderdate AS orderdat3_1_0_,
  (
    SELECT
      sum(li.quantity * p.cost)
    FROM
      lineitem AS li, product AS p
    WHERE
      li.productid = p.productid
      AND li.customerid = order0_.customerid
      AND li.ordernumber = order0_.ordernumber
  )
    AS formula101_0_,
  lineitems1_.customerid AS customer1_2_1_,
  lineitems1_.ordernumber AS ordernum2_2_1_,
  lineitems1_.productid AS producti3_2_1_,
  lineitems1_.customerid AS customer1_2_2_,
  lineitems1_.ordernumber AS ordernum2_2_2_,
  lineitems1_.productid AS producti3_2_2_,
  lineitems1_.quantity AS quantity4_2_2_
FROM
  customerorder AS order0_
  LEFT JOIN lineitem AS lineitems1_
  ON
    order0_.customerid = lineitems1_.customerid
    AND order0_.ordernumber = lineitems1_.ordernumber
WHERE
  order0_.customerid = 'c111' AND order0_.ordernumber = 0;

SELECT
  customer0_.customerid AS customer1_0_0_,
  orders1_.customerid AS customer1_1_1_,
  orders1_.ordernumber AS ordernum2_1_1_,
  lineitems2_.customerid AS customer1_2_2_,
  lineitems2_.ordernumber AS ordernum2_2_2_,
  lineitems2_.productid AS producti3_2_2_,
  product3_.productid AS producti1_3_3_,
  customer0_.name AS name2_0_0_,
  customer0_.address AS address3_0_0_,
  orders1_.orderdate AS orderdat3_1_1_,
  (
    SELECT
      sum(li.quantity * p.cost)
    FROM
      lineitem AS li, product AS p
    WHERE
      li.productid = p.productid
      AND li.customerid = orders1_.customerid
      AND li.ordernumber = orders1_.ordernumber
  )
    AS formula103_1_,
  orders1_.customerid AS customer1_1_0__,
  orders1_.ordernumber AS ordernum2_1_0__,
  orders1_.ordernumber AS ordernum2_0__,
  lineitems2_.quantity AS quantity4_2_2_,
  lineitems2_.customerid AS customer1_2_1__,
  lineitems2_.ordernumber AS ordernum2_2_1__,
  lineitems2_.productid AS producti3_2_1__,
  product3_.description AS descript2_3_3_,
  product3_.cost AS cost3_3_3_,
  product3_.numberavailable AS numberav4_3_3_,
  (
    SELECT
      sum(li.quantity)
    FROM
      lineitem AS li
    WHERE
      li.productid = product3_.productid
  )
    AS formula104_3_
FROM
  customer AS customer0_
  LEFT JOIN customerorder AS orders1_
  ON customer0_.customerid = orders1_.customerid
  LEFT JOIN lineitem AS lineitems2_
  ON
    orders1_.customerid = lineitems2_.customerid
    AND orders1_.ordernumber = lineitems2_.ordernumber
  LEFT JOIN product AS product3_ ON lineitems2_.productid = product3_.productid;

SELECT
  order0_.customerid AS customer1_1_0_,
  order0_.ordernumber AS ordernum2_1_0_,
  order0_.orderdate AS orderdat3_1_0_,
  (
    SELECT
      sum(li.quantity * p.cost)
    FROM
      lineitem AS li, product AS p
    WHERE
      li.productid = p.productid
      AND li.customerid = order0_.customerid
      AND li.ordernumber = order0_.ordernumber
  )
    AS formula105_0_,
  lineitems1_.customerid AS customer1_2_1_,
  lineitems1_.ordernumber AS ordernum2_2_1_,
  lineitems1_.productid AS producti3_2_1_,
  lineitems1_.customerid AS customer1_2_2_,
  lineitems1_.ordernumber AS ordernum2_2_2_,
  lineitems1_.productid AS producti3_2_2_,
  lineitems1_.quantity AS quantity4_2_2_
FROM
  customerorder AS order0_
  LEFT JOIN lineitem AS lineitems1_
  ON
    order0_.customerid = lineitems1_.customerid
    AND order0_.ordernumber = lineitems1_.ordernumber
WHERE
  order0_.customerid = 'c111' AND order0_.ordernumber = 0;

-- org.hibernate.test.criteria.CriteriaQueryTest

CREATE TABLE student (
  studentid INT8 NOT NULL,
  name VARCHAR(255) NOT NULL,
  address_city VARCHAR(255),
  address_state VARCHAR(255),
  preferredcoursecode VARCHAR(255),
  PRIMARY KEY (studentid)
);

CREATE TABLE enrolment (
  studentid INT8 NOT NULL,
  coursecode VARCHAR(255) NOT NULL,
  semester INT2 NOT NULL,
  year INT2 NOT NULL,
  PRIMARY KEY (studentid, coursecode)
);

SELECT
  this_.studentid AS studenti1_26_0_,
  this_.name AS name2_26_0_,
  this_.address_city AS address_3_26_0_,
  this_.address_state AS address_4_26_0_,
  this_.preferredcoursecode AS preferre5_26_0_
FROM
  student AS this_
WHERE
  EXISTS(
    SELECT
      enrolment_.studentid AS y0_
    FROM
      enrolment AS enrolment_
    WHERE
      enrolment_.year
      = (
          SELECT
            max(maxstudentenrolment_.year) AS y0_
          FROM
            enrolment AS maxstudentenrolment_
          WHERE
            this_.preferredcoursecode = maxstudentenrolment_.coursecode
        )
  );

-- org.hibernate.test.hql.ASTParserLoadingTest
-- this one query was executed a good 100 times in the test

CREATE TABLE customerorder (
  customerid VARCHAR(10) NOT NULL,
  ordernumber INT4 NOT NULL,
  orderdate DATE NOT NULL,
  PRIMARY KEY (customerid, ordernumber)
);

CREATE TABLE lineitem (
  customerid VARCHAR(10) NOT NULL,
  ordernumber INT4 NOT NULL,
  productid VARCHAR(10) NOT NULL,
  quantity INT4,
  PRIMARY KEY (customerid, ordernumber, productid)
);

SELECT
  order0_.customerid AS customer1_10_,
  order0_.ordernumber AS ordernum2_10_,
  order0_.orderdate AS orderdat3_10_,
  (
    SELECT
      sum(li.quantity * p.cost)
    FROM
      lineitem AS li, product AS p
    WHERE
      li.productid = p.productid
      AND li.customerid = order0_.customerid
      AND li.ordernumber = order0_.ordernumber
  )
    AS formula273_
FROM
  customerorder AS order0_;

-- org.hibernate.test.subselectfetch.SubselectFetchWithFormulaTest

CREATE TABLE t_name (id INT4 NOT NULL, c_name VARCHAR(255), PRIMARY KEY (id));

SELECT
  this_.id AS id1_0_0_,
  this_.c_name AS c_name2_0_0_,
  (SELECT length(this_.c_name) FROM t_name WHERE this_.id = t_name.id)
    AS formula0_0_
FROM
  t_name AS this_;

BramGruneir · 2018-07-25T15:34:31Z

Reran the test suite again, the only one failing correlated subquery now:

-- org.hibernate.test.subselectfetch.SubselectFetchWithFormulaTest

CREATE TABLE t_name (id INT4 NOT NULL, c_name VARCHAR(255), PRIMARY KEY (id));

SELECT
  this_.id AS id1_0_0_,
  this_.c_name AS c_name2_0_0_,
  (SELECT length(this_.c_name) FROM t_name WHERE this_.id = t_name.id)
    AS formula0_0_
FROM
  t_name AS this_;

andy-kimball · 2018-08-24T21:10:53Z

Since we now decorrelate all the hibernate test queries, I'm closing this issue.

BramGruneir added the A-sql-pgcompat Semantic compatibility with PostgreSQL label Jun 12, 2018

BramGruneir mentioned this issue Jun 12, 2018

sql: Hibernate Test Failures #26657

Closed

14 tasks

BramGruneir changed the title ~~sql: Hibernate - org.hibernate.userguide.collections.UnidirectionalMapTest - testLifecycle~~ sql: Hibernate tests - correlated subqueries Jun 13, 2018

andy-kimball self-assigned this Jun 14, 2018

knz added C-investigation Further steps needed to qualify. C-label will change. A-sql-optimizer SQL logical planning and optimizations. A-testing Testing tools and infrastructure labels Jun 14, 2018

BramGruneir added the A-tools-hibernate Issues that pertain to Hibernate integration. label Jun 14, 2018

andy-kimball closed this as completed Jun 14, 2018

andy-kimball reopened this Jun 14, 2018

awoods187 assigned solongordon Jun 19, 2018

BramGruneir added the meta-issue Contains a list of several other issues. label Jun 22, 2018

andy-kimball closed this as completed Aug 24, 2018

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: Hibernate tests - correlated subqueries #26658

sql: Hibernate tests - correlated subqueries #26658

BramGruneir commented Jun 12, 2018 •

edited

Loading

BramGruneir commented Jun 13, 2018

BramGruneir commented Jun 13, 2018

BramGruneir commented Jun 13, 2018

BramGruneir commented Jun 13, 2018 •

edited

Loading

awoods187 commented Jun 13, 2018

BramGruneir commented Jun 14, 2018 •

edited

Loading

andy-kimball commented Jun 14, 2018

BramGruneir commented Jun 14, 2018

BramGruneir commented Jun 14, 2018 •

edited

Loading

BramGruneir commented Jun 18, 2018

BramGruneir commented Jun 18, 2018

BramGruneir commented Jun 18, 2018

BramGruneir commented Jun 18, 2018

awoods187 commented Jun 19, 2018

BramGruneir commented Jun 21, 2018

knz commented Jun 22, 2018

BramGruneir commented Jul 2, 2018

awoods187 commented Jul 2, 2018

knz commented Jul 2, 2018

BramGruneir commented Jul 2, 2018

knz commented Jul 2, 2018

andy-kimball commented Jul 2, 2018

knz commented Jul 2, 2018

BramGruneir commented Jul 19, 2018 •

edited

Loading

BramGruneir commented Jul 25, 2018 •

edited

Loading

andy-kimball commented Aug 24, 2018

sql: Hibernate tests - correlated subqueries #26658

sql: Hibernate tests - correlated subqueries #26658

Comments

BramGruneir commented Jun 12, 2018 • edited Loading

BramGruneir commented Jun 13, 2018

BramGruneir commented Jun 13, 2018

BramGruneir commented Jun 13, 2018

BramGruneir commented Jun 13, 2018 • edited Loading

awoods187 commented Jun 13, 2018

BramGruneir commented Jun 14, 2018 • edited Loading

andy-kimball commented Jun 14, 2018

BramGruneir commented Jun 14, 2018

BramGruneir commented Jun 14, 2018 • edited Loading

BramGruneir commented Jun 18, 2018

BramGruneir commented Jun 18, 2018

BramGruneir commented Jun 18, 2018

BramGruneir commented Jun 18, 2018

awoods187 commented Jun 19, 2018

BramGruneir commented Jun 21, 2018

knz commented Jun 22, 2018

BramGruneir commented Jul 2, 2018

awoods187 commented Jul 2, 2018

knz commented Jul 2, 2018

BramGruneir commented Jul 2, 2018

knz commented Jul 2, 2018

andy-kimball commented Jul 2, 2018

knz commented Jul 2, 2018

BramGruneir commented Jul 19, 2018 • edited Loading

BramGruneir commented Jul 25, 2018 • edited Loading

andy-kimball commented Aug 24, 2018

BramGruneir commented Jun 12, 2018 •

edited

Loading

BramGruneir commented Jun 13, 2018 •

edited

Loading

BramGruneir commented Jun 14, 2018 •

edited

Loading

BramGruneir commented Jun 14, 2018 •

edited

Loading

BramGruneir commented Jul 19, 2018 •

edited

Loading

BramGruneir commented Jul 25, 2018 •

edited

Loading