@ManyToMany: Select null from

Hibernate ORM
1

We observed strange SQLs being generated with the following pattern in an @ManyToMany relationship:
select null from …

The following test code can be used to reproduce this (based on the ORMUnitTestCase class in the test-case-template-hibernate-orm-7 project):

package org.hibernate.bugs;

import java.io.Serializable;
import java.util.List;

import org.hibernate.annotations.CacheConcurrencyStrategy;
import org.hibernate.cfg.AvailableSettings;
import org.hibernate.testing.orm.junit.DomainModel;
import org.hibernate.testing.orm.junit.ServiceRegistry;
import org.hibernate.testing.orm.junit.SessionFactory;
import org.hibernate.testing.orm.junit.SessionFactoryScope;
import org.hibernate.testing.orm.junit.Setting;
import org.junit.jupiter.api.Test;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;

/**
 * This template demonstrates how to develop a test case for Hibernate ORM, using its built-in unit test framework.
 * Although ORMStandaloneTestCase is perfectly acceptable as a reproducer, usage of this class is much preferred.
 * Since we nearly always include a regression test with bug fixes, providing your reproducer using this method
 * simplifies the process.
 * <p>
 * What's even better?  Fork hibernate-orm itself, add your test case directly to a module's unit tests, then
 * submit it as a PR!
 */
@DomainModel(
    annotatedClasses = {ORMUnitTestCase.TestRight.class,ORMUnitTestCase.TestUser.class
				// Add your entities here.
				// Foo.class,
				// Bar.class
		},
		// If you use *.hbm.xml mappings, instead of annotations, add the mappings here.
		xmlMappings = {
				// "org/hibernate/test/Foo.hbm.xml",
				// "org/hibernate/test/Bar.hbm.xml"
		}
)
@ServiceRegistry(
		// Add in any settings that are specific to your test.  See resources/hibernate.properties for the defaults.
		settings = {
				// For your own convenience to see generated queries:
        @Setting(name = AvailableSettings.SHOW_SQL, value = "true"),
        @Setting(name = AvailableSettings.FORMAT_SQL, value = "false"),
				// @Setting( name = AvailableSettings.GENERATE_STATISTICS, value = "true" ),

				// Add your own settings that are a part of your quarkus configuration:
				// @Setting( name = AvailableSettings.SOME_CONFIGURATION_PROPERTY, value = "SOME_VALUE" ),
		}
)
@SessionFactory
class ORMUnitTestCase {

	// Add your tests, using standard JUnit 5.
    @Test
    void selectNullTest(SessionFactoryScope scope) throws Exception
    {
      scope.inTransaction(session -> {
        // A) Create  test data
        TestRight r1 = new TestRight(1);
        session.persist(r1);

        TestRight r2 = new TestRight(2);
        session.persist(r2);

        TestUser u1 = new TestUser();
        u1.setName("TestUser");
        u1.setTestRights(List.of(r1, r2));
        session.persist(u1);
        session.flush();

        // B) Set existing TestRights (managed entities) --> OK (NO select null...)
        r1 = session.find(TestRight.class, 1);
        r2 = session.find(TestRight.class, 2);
        u1.setTestRights(List.of(r1, r2));
        session.flush();

        // C) Set existing TestRights (transient entities) --> nOK (select null...)
        System.out.println("START: Testing with transient entities");
        r1 = new TestRight(1);
        r2 = new TestRight(2);
        u1.setTestRights(List.of(r1, r2));
        session.flush();
        System.out.println("FINISH: Testing with transient entities");
      });
    }

    @Entity
    public class TestUser implements Serializable
    {
      private static final long serialVersionUID = 1L;

      @Id
      @GeneratedValue
      private int             userid;

      private String          name;

      @ManyToMany
      @JoinTable(name = "TESTUSER_TESTRIGHT", joinColumns = {@JoinColumn(name = "USERID")}, inverseJoinColumns = {
          @JoinColumn(name = "RIGHTID")})
      @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.TRANSACTIONAL)
      private List<TestRight> rechte;

      public int getUserid()
      {
        return userid;
      }

      public void setUserid(int userid)
      {
        this.userid = userid;
      }

      public String getName()
      {
        return name;
      }

      public void setName(String name)
      {
        this.name = name;
      }

      public List<TestRight> getRechte()
      {
        return rechte;
      }

      public void setTestRights(List<TestRight> rechte)
      {
        this.rechte = rechte;
      }
    }

    @Entity
    public class TestRight implements Serializable
    {
      private static final long serialVersionUID = 1L;

      @Id
      private int             rightid;

      public TestRight(int rightid)
      {
        super();
        this.rightid = rightid;
      }

      public int getRightid()
      {
        return rightid;
      }

      public void setRightid(int rightid)
      {
        this.rightid = rightid;
      }
    }
}

With the current Hibernate version (7.1.1.Final), the following statements are generated in block (C):
select null from ORMUnitTestCase$TestRight tr1_0 where tr1_0.rightid=?
select null from ORMUnitTestCase$TestRight tr1_0 where tr1_0.rightid=?

In older versions (6.6.0.Alpha1 and below, as well as two exceptions, 7.0.0.Alpha and 7.0.0.Alpha), these statements are NOT generated. We noticed these statements because they subsequently lead to an exception in our JDBC driver. Although this is primarily an issue for the JDBC driver, other DB systems may also experience problems here.

Finally, I am interested in:

  • Why are additional ‘select null from …’ statements generated since version 6.6.0.CR1?
  • Is this it a bug? Or is it due to the questionable syntax (creating entities via constructor)? Or do the statements serve a functional purpose?
2

You are adding detached entities to a many-to-many collection, so Hibernate ORM for one reason or another might have the need to check if the rows exist.
Since the entity you’re adding has just an id and nothing else, there is no state that needs to be queried, apart from understanding whether the row exists, hence the select null from .... For what it’s worth, it could also be select 1 from ..., but for some reason we opted to select null.

To avoid such queries, don’t use detached entities and instead use entity proxies like so:

        r1 = session.getReference(TestRight.class, 1);
        r2 = session.getReference(TestRight.class, 2);
        u1.setTestRights(List.of(r1, r2));
        session.flush();