7 Replies Latest reply on Oct 20, 2017 1:41 PM by shawkins

    Set operations and parenthesis

    mike_higgins

      Using Teiid 9.2.2 with postgresql, I issue a query structured like this:

       

      (a

      UNION ALL

      b

      UNION ALL

      c)

      INTERSECT

      d

       

      All queries are from a single database.

      This query does not give back the expected result.  In the plan, I see the query printed with no parenthesis around the unions:

      a UNION ALL b UNION ALL c INTERSECT d

       

      which in the case of Postgresql (or Teiid, for that matter) is interpreted as:

       

      a UNION ALL b UNION ALL (c INTERSECT d)

       

      I did try changing the 'union all' to 'union' (which makes more sense) but it had the same result (except for duplicate removal of course).

      I don't see any references to this type of problem in the issues or forum.  is there any setting that I could do to make sure the

      parens are preserved?  Or am I doing something else wrong that you can think of?

        • 1. Re: Set operations and parenthesis
          rareddy

          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

            In the query plan there are no parens; I'll log an issue.

            • 3. Re: Set operations and parenthesis
              shawkins

              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

                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

                  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

                    Thanks, good work as usual.

                    • 7. Re: Set operations and parenthesis
                      shawkins

                      Thanks for highlighting this issue.