[shawkins]

> As far as I'm aware, I can't override translator properties in Teiid designer so I tried changing the vdb.xml file that is generated by Teiid Designer. To do so, I unpack it, edit vdb.xml and repack the vdb.

You should be able to in Designer as well.  There should be translator overrides panel in the vdb editor.

> Because the documentation doesn't seem to mention where I should put the translator override in the vdb.xml file, I just put it in as a child of the vdb tag

There is VDB Definition - Teiid 8.9 (draft) - Project Documentation Editor

And you can also refer to the xsd if needed: teiid/vdb-deployer.xsd at master · teiid/teiid · GitHub

We'll also update the translator page to give more context.

[bpiepers]

This is slightly embarrassing but we may have found the problem. The columns that we are trying to join are both nvarchar columns but in one table the whitespaces are indeed trimmed while in the other table the whitespaces seem to contain a character that is not visible in any client but makes the join in JDV fail. This is at least what we suspect may be the problem. We'll first investigate if we can make these two columns contain exactly the same and will get back to this post as soon as I have more information.

[shawkins]

This can be a slightly tricky situation.  At the db level using fixed character string types will effectively ignore trailing white space.  When pulling those values via JDBC, the padding will remain.  Then as Java strings (which is effectively a varchar type) the trailing whitespace becomes meaningful.  There are a couple of mechanisms that we have for this.  First there is the translator trimStrings option, which will use an rtrim in the source queries when dealing with a fixed character string (char/nchar type).  There is also the system property org.teiid.padSpace that will change our string comparison to ignore trailing white space.  And you can always manually insert trim/rtrim into the join and other queries where appropriate.

[bpiepers]

Thanks Steven. I already tried using the trimString option which didn't yet lead to a chance of behavior but will certainly also take the other mechanisms into consideration. Thanks again for your support.

[shawkins]

If they are both nvarchar, then trim strings won't have an effect as it's only looking to trim the fixed length types char/nchar.  With nvarchar trailing whitespace would be considered meaningful even at the database level when comparing two nvarchars in a non pad space collation.  So it may be appropriate to look at updating the join with explicit trims.