> suppose you don't push the grouping and have repeated year month entries from ts that match in t. This should produce a count of at least two. If you push the grouping, then there would just be 1 row and the count would be off.
Yes, of course, you're right.
> we know the join cardinality and that the year/month combinations are unique (however if they were unique then there isn't really a reason to push the grouping)
The more I think about my use case, I'm trying to use the translator for something it's not: a means to layer semantics on the query because the client - an MDX server - doesn't understand the underlying semantics of the data. What I really need is a layer that sits before the planner to massage the SQL taking into account the client's limitations. This might be a perfect opportunity to try out TEIID-3369. I haven't explored the API yet but if I can introduce hints, I'm pretty sure I can achieve what I want in a cleaner way.
Thanks Mark for converting this into a forum posting.
Yes it may make more sense to handle any semantic mismatch above us rather than trying to get the engine to compensate.