Overriding collate in a query, possible?

[m.ardito]

Many of my "source" mysql table have case insensitive collation by default but, where needed, I can request collation override using a syntax like

SELECT* FROM ag WHERE ag.name LIKE '%è%'  COLLATE utf8_bin

Now I need to be able to do the same accessing that data from Teiid vdb model....

is it possible, and how?

Marco

[shawkins]

This is something that we need in some form, but haven't yet pursued.  All you can do currently is prevent the pushdown of selected sorts if there is an expectation that the collation matters - see [TEIID-4129] Wrong results with cross model join on 2 varchar fields - JBoss Issue Tracker

There are two possible feature paths here.  The first would be to allow a collate clause in Teiid, which would likely reference JRE locales - that would need pushdown consideration and thus matching to source collation names / clauses.  The other would be to more broadly enforce Teiid collation expectation rather than just to prevent pushdown.  How broadly are you looking for collate support?

[m.ardito]

Steven Hawkins ha scritto:

This is something that we need in some form, but haven't yet pursued.  All you can do currently is prevent the pushdown of selected sorts if there is an expectation that the collation matters - see [TEIID-4129] Wrong results with cross model join on 2 varchar fields - JBoss Issue Tracker

There are two possible feature paths here.  ....  How broadly are you looking for collate support?

Really small need, I think, in my case, and atm just for searching field contents purposes, like in my above post example.

Not a big deal. But it would be handy, even if just for this purpose.

If I understand the above issue, no, I always avoided to join tables on alphanumeric fields. That is a solution looking for a trouble, imho..

But I can understand that Teiid can push you to try the weirdest things, as it mostly always work well...

Thanks,

Marco