-
1. Re: Set operations and parenthesis
rareddy Oct 19, 2017 5:11 PM (in response to mike_higgins)Did you check what Teiid is sending to the source? You can enable query plan or look at command logging to see what is the query being sent to Postgres. If that query does not have parens then you might want to log JIRA issue for it.
-
2. Re: Set operations and parenthesis
mike_higgins Oct 19, 2017 5:19 PM (in response to rareddy)In the query plan there are no parens; I'll log an issue.
-
3. Re: Set operations and parenthesis
shawkins Oct 19, 2017 6:49 PM (in response to mike_higgins)I can confirm that this is a bug with the sql to string logic at both the engine and translator layers. Once this is logged I'll have it resolved quickly.
-
4. Re: Set operations and parenthesis
mike_higgins Oct 20, 2017 1:11 PM (in response to shawkins)Thank you for the quick attention to the issue (https://issues.jboss.org/browse/TEIID-5109)!
I noticed that one of the test cases mentioned that some parens were not necessary because of the precedence of intersect. However, if your eventual destination is Oracle, union and intersect have the same precedence, so parens would be needed there. It makes me wonder if full parenthesization is needed for Oracle to make sure that the query is correct in every case. I did not notice this bug when using Oracle, because it's (broken) precedence rules make the query execute correctly...
-
5. Re: Set operations and parenthesis
shawkins Oct 20, 2017 1:34 PM (in response to mike_higgins)The toString logic exists at both the engine and the translator layer. The engine layer sql is Teiid specific, so the parens in that case are not strictly necessary. However for consistency that code matches the translator layer, where as you point out they are needed by a source like Oracle.
> It makes me wonder if full parenthesization is needed for Oracle to make sure that the query is correct in every case.
I think the current logic captures the necessary cases. As long as there isn't some source with a weird behavior like higher precedence for union or intersect, this should be ok.
user queries "(a union b) intersect c", "a union (b intersect c)", or "a intersect (b union c)" will retain their parens.
"(a intersect b) union c" would loose it's parens but that is fine with higher or equal intersect precedence
-
6. Re: Set operations and parenthesis
mike_higgins Oct 20, 2017 1:36 PM (in response to shawkins)Thanks, good work as usual.
-
7. Re: Set operations and parenthesis
shawkins Oct 20, 2017 1:41 PM (in response to mike_higgins)Thanks for highlighting this issue.