Open Gemineye opened 8 months ago
Can confirm, we have the same issue here with >=2.17.
Removing the fetch="EAGER"
from a ManyToMany with a composite key on both sides (yeah, i know, old code) prevents the exception, but tanks performance. Currently working around this by locking the doctrine/orm
version to ^2.0,<2.17.0
, which works for now, but is ofc not desirable.
Can either of you try to provide a failing functional test or maybe even investigate the issue?
@derrabus I did some debugging, switching between 2.16.x and 2.18.x and re-running the failing test cases, trying to find the point where they diverge. I am not sure if thats the correct path, since changes seem to be quite fundamental around eager loading of collections at that point.
For me, it looks like with 2.16.x eager fetching of these collections does not really trigger at all in the few test cases that break with 2.17.
With 2.17, one of our test fails when doing $repo->findAll()
of an entity that has a OneToMany
with fetch=EAGER
, the others fail when calling refresh
on such an entity.
I think this here should looks suspicious to me:
We have an entity LineItem
with a composite key, lets call them order_id
and position
.
There is another entity LineItemError
which is joined (OneToMany) via order_id
and position
.
order_id
is an integer, position
is a string.
When doing a $repo->findAll()
we go from EntityRepository::findAll
to ::findBy
to BasicEntityPersister::loadAll
, AbstractHydrator::hydrateAll
, SimpleObjectHydrator::hydrateAllData
, UnitOfWork::triggerEagerLoad
, UnitOfWork::eagerLoadCollections
, and back at BasicEntityPersister::loadAll
(this time for the collection.
The SQL it generate here looks something like this:
SELECT [...],, t0.position AS position_2, t0.order_id AS order_id_3, [...], t0.order_id AS order_id_8, t0.position AS position_9 FROM [...]line_item_error t0 WHERE t0.order_id IN (?) AND t0.position IN (?)
Which... I am not entirely sure about. Maybe its getting late, but that looks like it could find the wrong entries. Maybe we set something up incorrectly? Or it may fix it in a later step?
The $criteria
has an array { "lineItem": array<LineItem> }
with 3 LineItems
(or, in this case, for some reason, two proxies and one "real" LineItem. Guess thats related to things the test does).
From that expandParameters
collects this $params
: [ 0 => [ 1, '1', 1, '2', 1, '3' ] ]
So for me, that looks like it mixes both keys, interweaving them. [ $order_id[0] , $position[0], $order_id[1], $position[1], ... ]
. Not sure if thats supposed to happen, with that query I would have assumed a result that looks more like [0 => [1, 1, 1], 1 => ['1', '2', '3'] ]
.
$types
has [101, 102]
(ignore the "valueName", thats phpstorm being confused)
When then going into executeQuery
the SQL\Parser
tries to patch in the parameters and cannot find values for the second parameter (since we only gave it one list of values, not two).
Soooo either we f'ed up big time (more than we know!) and setup the OneToMany join totally incorrect, without noticing it for a very long time, or something about the new changes since 2.17.0 breaks our broken use-case.
I am aware that what we do here is not exactly "best practice". But in my defense: it kind up worked up until know ;)
I hope to find some more time tomorrow and come up with a minimal reproducer.
Can you create a functional test that reproduces your scenario?
I'll give it a try to build a functional test here that triggers this. If that does not work out I already have a "minmal" reproducer with ~160 lines of php (including doctrine bootstrap) that reproduces the issue just fine. I can push/share that if it helps.
But will try to build a functional test first, that would be the best cast.
@derrabus I created https://github.com/doctrine/orm/pull/11289 At least locally for me that triggers the exception. Please let me know if you need something more, or if I did something wrong with this.
Thank you. Your test is failing although it's a different error message than the one you've described.
The error message seem to depend on the php version (not sure why it would):
on <=7.2 we get a generic DBAL\Exception
"Undefined offset: 2" (https://github.com/doctrine/orm/actions/runs/8002637962/job/21857153118?pr=11289)
on >=7.3 we get the "expected": Doctrine\DBAL\ArrayParameters\Exception\MissingPositionalParameter: Positional parameter at index 1 does not have a bound value.
( https://github.com/doctrine/orm/actions/runs/8002637962/job/21857153428?pr=11289 )
Same on 8.3: https://github.com/doctrine/orm/actions/runs/8002637962/job/21857155103?pr=11289
That's probably due to DBAL 2 vs. DBAL 3. 🙂
I have the same error in 2.18.1 with PHP 8.3.3 for OneToMany
class Asset
{
#[Id]
#[Column(type: 'bigint', options: ['unsigned' => true])]
private int $assetId;
#[Id]
#[Column(type: 'bigint', options: ['unsigned' => true])]
private int $productId;
#[OneToMany(mappedBy: 'asset', targetEntity: AssetContent::class, cascade: ["persist", "remove"], fetch: "EAGER")]
private Collection $content;
}
class AssetContent
{
// ..
#[ManyToOne(targetEntity: Asset::class, inversedBy: 'content')]
#[JoinColumn(name:"asset_id", referencedColumnName:"asset_id", nullable: false, onDelete: 'CASCADE')]
#[JoinColumn(name:"product_id", referencedColumnName:"product_id", nullable: false, onDelete: 'CASCADE')]
private Asset $asset;
}
when I call
$em->find(Asset::class, ['assetId' => $assetId, 'productId' => $productId]);
So, I played around a bit more, and debugged a few scenarios.
First, no surprise, but the same issue triggers if you manually try to load the associated entities: (using the test case in https://github.com/doctrine/orm/pull/11289 as base for the example here)
// remove the fetch="EAGER" from RootEntity::$secondLevel before this
$x = $this->_em->getRepository(RootEntity::class)->findAll()[0];
$r2 = $this->_em->getRepository(SecondLevel::class)->findBy(['root' => [$x]]);
This is because this does exactly the same as Doctrine itself would do on a EAGER fetch.
So, I think (and maybe I am late in understanding this and stating the obvious) the root problem here is, that the query doctrine generate here to load the association does not support fetching the associated entities for more than one owning entity.
Meaing ->findBy(['root' => $x])
works, but ->findBy(['root' => [$x]])
(which is what eagerLoadCollections
calls) does not.
The query that generates looks like this:
SELECT t0.id AS id_1, t0.upperId AS upperId_2, t0.other_key AS other_key_3, t0.other_key AS other_key_4, t0.upper_id AS upper_id_5 FROM eager_composite_join_second_level t0 WHERE t0.other_key IN (?) AND t0.upper_id IN (?)
Which will never work. Even if expandParameters -> getValues / getIndividualValue
would split the two used keys up in two params (which it does not), the result would be unsound.
I think it could potentially return incorrect results:
a IN (1, 2) AND b in (3, 4)
is not the same as (a, b) IN ((1, 3), (2, 4))
, right? But I am not sure if the latter is valid in all (or even any) SQL dialect.
A naive sledgehammer approach of "fixing" this would be, to, in eagerLoadCollections
fall back to loadOneToManyCollection
/ loadManyToManyCollection
if $targetEntity
isIdentifierComposite
? But I do not claim to understand even the basics of doctrine internals.
Just putting that here in case it helps anyone.
Moral of the story: do not use composite identifiers / join columns ;)
With the merge of #11289 an the release of 2.19.2 I think this bug is fixed (at least in our test suite) and this ticket can be closed?
Thanks a lot @derrabus and @beberlei
@greg0ire The update does not seem to fix our error :(
/**
* @var Collection<int, PharmacyServiceDescription>
* @ORM\OneToMany(targetEntity="MeinDAV\SelfService\Domain\Model\PharmacyServiceDescription", mappedBy="serviceId", fetch="EAGER")
*/
protected Collection $serviceDescriptions;
Without "fetch="EAGER"" it works.
Bug Report
Summary
We have a ManyToOne Relation to an entity with a composite primary key. Since Version 2.17 with fetch="EAGER" we get the error:
Doctrine\DBAL\ArrayParameters\Exception\MissingPositionalParameter: Positional parameter at index 1 does not have a bound value.
Current behavior
How to reproduce
Relation definition:
Expected behavior
No Exception should occur.