2.20.7 / 3.5.3 SQL syntax error in WHERE IN - missing null

[petrparolek]

Bug Report

Q	A
Version	2.20.7 / 3.5.3

PR fix: #12254

Summary

The commit 9bf407f breaks it.

Current behavior

Doctrine\DBAL\Exception\SyntaxErrorException #1064 An exception occurred while executing a query: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

SELECT COUNT(*) FROM tags t0 WHERE t0.id IN ()

Expected behavior

SELECT COUNT(*) FROM tags t0 WHERE t0.id IN (NULL)

How to reproduce

<?php declare(strict_types = 1);

namespace App\Domain\Database;

use DateTime;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ItemRepository::class)]
#[ORM\Table(name: 'items')]
class Item
{

	#[ORM\Id]
	#[ORM\Column(type: 'integer')]
	#[ORM\GeneratedValue]
	private int $id;

	/** @var Collection<int, Tag> */
	#[ORM\ManyToMany(targetEntity: Tag::class, inversedBy: 'items')]
	#[ORM\JoinTable(name: 'items_x_tags')]
	#[ORM\JoinColumn(name: 'item_id', referencedColumnName: 'id', onDelete: 'CASCADE')]
	#[ORM\InverseJoinColumn(name: 'tag_id', referencedColumnName: 'id', unique: false, onDelete: 'CASCADE')]
	private Collection $tags;

	public function __construct()
	{
		$this->tags = new ArrayCollection();
	}

	public function getId(): int
	{
		return $this->id;
	}

	/**
	 * @return Collection<int, Tag>
	 */
	public function getTags(): Collection
	{
		return $this->tags;
	}

	public function addTag(Tag $tag): void
	{
		$this->tags[] = $tag;
	}

	public function removeTag(Tag $tag): void
	{
		$this->tags->removeElement($tag);
	}

	/**
	 * @param Collection<int, Tag> $tags
	 */
	public function setTags(Collection|array $tags): void
	{
		$this->tags->clear();

		foreach ($tags as $tag) {
			$this->tags->add($tag);
		}
	}

}

<?php declare(strict_types = 1);

namespace App\Domain\Database;

use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: TagRepository::class)]
#[ORM\Table(name: 'tags')]
class Tag
{

	#[ORM\Id]
	#[ORM\Column(type: 'integer')]
	#[ORM\GeneratedValue]
	private int $id;

	#[ORM\Column(type: 'string', nullable: false)]
	private string $title;

	/** @var Collection<int, Item> */
	#[ORM\ManyToMany(targetEntity: Item::class, mappedBy: 'tags')]
	private Collection $items;

	public function getId(): int
	{
		return $this->id;
	}

	public function getTitle(): string
	{
		return $this->title;
	}

	/**
	 * @return Collection<int, Item>
	 */
	public function getItems(): Collection
	{
		return $this->items;
	}

	public function setTitle(string $title): void
	{
		$this->title = $title;
	}

	public function setItems(mixed $items): void
	{
		$this->items = $items;
	}

}

$item = new \App\Domain\Database\Item();

//$tagsIds = [1,2]; //OK
$tagsIds = []; //SQL syntax error in doctrine/orm 2.20.7 / 3.5.3
$criteria = \Doctrine\Common\Collections\Criteria::create()
	->where(\Doctrine\Common\Collections\Criteria::expr()->in('id', $tagsIds))
	->orderBy(['title' => 'ASC']);

/** @var \Doctrine\Common\Collections\CriteriaCollection<int, \App\Domain\Database\Tag> $return */
$tags = $this->em->getRepository(\App\Domain\Database\Tag::class)->matching($criteria);

$item->setTags($tags);

$em->persist($item);
$em->flush();

[whataboutpereira]

Also broken with empty array in findBy():

$this->findBy([
    'user' => $user,
    'comment' => $reference->getComments()->toArray() ])
;

Generates an empty IN:

SELECT t0.comment_type AS comment_type_1, t0.user_id AS user_id_2, t0.comment_id AS comment_id_3
    FROM comment_notification t0
    WHERE t0.user_id = ? AND t0.comment_id IN ()

[xabbuh]

see #12245 and #12254 for the bugfix

[W0rma]

The bug should have been fixed in version 2.20.8 and 3.5.7.