UPDATE statement for joined table inheritance requires CREATE TEMPORARY TABLE permissions

I've set up ORM mapping for two entities:

/**
 * @ORM\Entity()
 * @ORM\Table(name="token")
 * @ORM\InheritanceType("JOINED")
 *
 * @ORM\DiscriminatorColumn(name="type", type="string")
 * @ORM\DiscriminatorMap({
 *     "simple": "AppBundle\Entity\SimpleToken"
 * })
 */
abstract class AbstractToken
{
  /**
   * @ORM\Id()
   * @ORM\GeneratedValue(strategy="AUTO")
   */
  protected $id;

  /**
   * @ORM\Column(type="boolean")
   */
  protected $isEnabled;
}

and a second one:

/**
 * @ORM\Entity()
 * @ORM\Table(name="simple_token")
 */
class SimpleToken extends AbstractToken
{
  /**
   * @ORM\Column(type="string")
   */
  protected $userName;
}

The token with some user name (let it be test_user for an example) exists in database and $userName is a unique column. Now if I execute update statement through \Doctrine\ORM\QueryBuilder like this:

$em->createQueryBuilder()
            ->update(Token::class, 't')
            ->set('t.isEnabled', 0)
            ->where('t.userName = :userName')
            ->setParameters(
                new ArrayCollection(
                    [
                        new Parameter('userName', 'test_user'),
                    ]
                )
            )
            ->getQuery()
            ->execute()
        ;

The database user running the statement doesn't have a "CREATE TEMPORARY TABLE" permission. When the query is trying to execute it fails with an exception: "An exception occurred while executing 'CREATE TEMPORARY TABLE token_id_tmp (id BIGINT NOT NULL)'". So my question is why does the UPDATE statement try to create temporary table? Please, avoid using DDL statements in DML commands, such as in Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor

The biggest problem is whenever you're using inheritance, specifically Class Table Inheritance (CTI - http://martinfowler.com/eaaCatalog/classTableInheritance.html) we need to hold all IDs we are removing or updating temporarily, so we can traverse through the inheritance properly.

There is an alternative though: we hold all IDs in memory, and we remove/update from each table in the hierarchy, but some drivers (well, mainly PDO) prevent to bind more than 1000 values (that would be in your where clause) in a statement, which would invalidate the approach.

I'm open to hear other generic approaches you would suggest me to handle these scenarios. But until you can come up with a better solution, this issue will remain closed.

PS: Don't bother looking at other ORMs. They all do the same approach we do here. =\

doctrine / orm

UPDATE statement for joined table inheritance requires CREATE TEMPORARY TABLE permissions #5782