Is rag_soc case insensitive in the database? Unfortunately that would cause the different results as the collation is different. This is similar to [TEIID-4129] Wrong results with cross model join on 2 varchar fields - JBoss Issue Tracker While there are workarounds, they aren't great. I would like to add more logic around the case insensitive flag in our metadata (which requires detection logic on import for each source), but at the very least I need to add a sanity check in the join processing to detect when the expected ordering is not honored.
Yes, the ag mysql table is "utf8_general_ci", and the field ag.rag_soc is VARCHAR
The left joined table fields are
- ag.id_utente_inserimento is INT
- personale_interno.idanagrafica is "latin1_swedish_ci" INT
Here the join is done on integer ID codes, not on varchars, that is the ORDER BY...
But there is something more weird that happens when I am not using ORDER BY UCASE(rag_soc)
without that UCASE, look at what happens, changing the OFFSET
... and so on.
The last three records are always repeated, while the previous 17 change (it seems) as expected...
As said, using UCASE for sorting resulst seems to solve the issue (I need to keep an eye on this)
but I cannot understand if it is just a collation issue in the join, in the sort, in both... or else?
> but I cannot understand if it is just a collation issue in the join, in the sort, in both... or else?
What does the query plan look like? We should be pushing the sum of the the limit/offset to the left hand side using the rag_soc column for the ordering (since it's a left outer join we won't need more than those rows), then performing the join in Teiid possibly sorting on the join column.
Here is the server.log (zipped) output after issuing in SquirrelSQL:
"SET SHOWPLAN DEBUG;
SELECT id, rag_soc, pi, cf, cod_ass
LEFT JOIN apifdbgesp.anagrafica as personale_interno ON ag.id_utente_inserimento = personale_interno.idanagrafica
ORDER BY (rag_soc)
is this what you asked for?
qp_1.zip 2.1 KB
Yes that confirms the plan I was describing and means that yes you can get the results that you describe. To prevent this you can use upper/lower, disable order by pushdown, or use the translator settings described in the other issue - but you would also need to set org.teiid.requireTeiidCollation=true