Hopefully this is easy to follow....
On a One-To-Many association
Source entity field & column : integer
Target entity field & column: string why ? cos we a multi-use Single Table Inheritance entity as the Target and thus it may contain referential identifiers as strings or integers. Don't site that as the flaw here, hear me out.
When fetching the X-ToMany collection, the BasicEntityPersister (functions getOneToManyStatement and getManyToManyStatement) builds the collection fetch SQL using the target's column, but then parameterises the query using the Source field information and Source Class' metadata.
You end up with a query like
select blah,blah,blah FROM Target WHERE referenceId = 1 AND referenceType IN ('event')
Note the referenceId equality on an integer, not a string.
Now, we have this target table's referenceId column indexed, but the MySQL query optimiser does not make use of the index because we have requested equality on an integer and not a string literal.
The result is a massively slow query against a large dataset (> 2million rows). Replay the query with a string literal
select blah,blah,blah FROM Target WHERE referenceId = '1' AND referenceType IN ('event')
and the index is used and the result instantaneous. (Note: are using MySQL 5.6 .... but I do not see this in any way as a database issue when it's the constructed query that is infact letting us down here)
Now, going back to the BasicEntityPersister, if it's building the SQL to extract things from the Target table/entity, then is should (imo) parameterise with respect to the Target Entity's column and type..... seems logical to me.
If it did that, then the query would be constructed correctly with a string literal, the database utilise it's indexing and everything's happy.
Would you consider this a bug ?
Should I put up a Pull Request for a fix ?
Or is there something I am not considering ? and is it infact correct for the BasicEntityPersister to be using the Source class' field def'n and metadata to parameterise the query with an integer in this case ? If so, please explain why.
Hopefully this is easy to follow....
On a One-To-Many association
Source entity field & column : integer
Target entity field & column: string why ? cos we a multi-use Single Table Inheritance entity as the Target and thus it may contain referential identifiers as strings or integers. Don't site that as the flaw here, hear me out.
When fetching the X-ToMany collection, the BasicEntityPersister (functions getOneToManyStatement and getManyToManyStatement) builds the collection fetch SQL using the target's column, but then parameterises the query using the Source field information and Source Class' metadata.
You end up with a query like
select blah,blah,blah FROM Target WHERE referenceId = 1 AND referenceType IN ('event')Note the referenceId equality on an integer, not a string.
Now, we have this target table's referenceId column indexed, but the MySQL query optimiser does not make use of the index because we have requested equality on an integer and not a string literal.
The result is a massively slow query against a large dataset (> 2million rows). Replay the query with a string literal
select blah,blah,blah FROM Target WHERE referenceId = '1' AND referenceType IN ('event')and the index is used and the result instantaneous. (Note: are using MySQL 5.6 .... but I do not see this in any way as a database issue when it's the constructed query that is infact letting us down here)
Now, going back to the BasicEntityPersister, if it's building the SQL to extract things from the Target table/entity, then is should (imo) parameterise with respect to the Target Entity's column and type..... seems logical to me.
If it did that, then the query would be constructed correctly with a string literal, the database utilise it's indexing and everything's happy.
Would you consider this a bug ?
Should I put up a Pull Request for a fix ?
Or is there something I am not considering ? and is it infact correct for the BasicEntityPersister to be using the Source class' field def'n and metadata to parameterise the query with an integer in this case ? If so, please explain why.