OpenJPA
  1. OpenJPA
  2. OPENJPA-607

Extraneous "( )" on DISTINCT, ALL, ANY results in SQL syntax errors on some database systems

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.2.0
    • Component/s: None
    • Labels:
      None

      Description

      In testing various back-end database systems such as Sybase, Informix. there are cases where the extra pair of () resulted in SQL Syntax error:
      For example,
      1. in generating ALL, ANY subquery, there is extraneous pair of "( )" that are tolerated by most systems such as DB2, Oracle but not Sybase.
      WHERE (t0.DEPT_DEPTNO = ANY ((SELECT t1.deptno FROM DeptBean t1 WHERE (t1.deptno = ? )))

      2.In the case of DISTINCT function, Informix reports syntax error when argument/s of the DISTINCT is/are parenthesized:
      SELECT COUNT(DISTINCT (t1.name)) FROM EmpBean t1

        Activity

        Hide
        Michael Dick added a comment -

        Hi Catalina,

        What's the benefit of leaving the parenthesis in? If they're truly extraneous I would think that the default would be to not use them unless they're needed.

        The code changes provided affect ALL, DISTINCT and ANY, but does the same problem occur with other UnaryOps?

        Show
        Michael Dick added a comment - Hi Catalina, What's the benefit of leaving the parenthesis in? If they're truly extraneous I would think that the default would be to not use them unless they're needed. The code changes provided affect ALL, DISTINCT and ANY, but does the same problem occur with other UnaryOps?
        Hide
        Catalina Wei added a comment -

        This problem is fixed under r659027.

        Show
        Catalina Wei added a comment - This problem is fixed under r659027.
        Hide
        Catalina Wei added a comment -

        case 1. ALL and ANY :
        apply to subquery only. OpenJPA always pushdown subquery in enclosing parenthesis.
        ALL or ANY operator puts additional pair of parenthesis.
        The fix is to not putting in unneeded pair, because Sybase reports syntax
        error for ANY or ALL subquery having the extra enclosing parenthesis.
        case 2. Distinct function
        first, do not confuse this DISTINCT function with SELECT DISTINCT.
        SELECT DISTINCT will not got through the Distinct.class code.
        The Distinct function is only associated with a COUNT function.
        It is safe that given the fact OpenJpa only pushdown COUNT(DISTINCT one-item),
        we can enclose one-item with or without parenthesis.
        The fix is not to enclose one-time to get around syntax error reported by Informix.

        So far, I have verified the fix against DB2, Oracle, Sybase, SQLServer and Informix.

        Help needed to verify other back-end systems.

        Show
        Catalina Wei added a comment - case 1. ALL and ANY : apply to subquery only. OpenJPA always pushdown subquery in enclosing parenthesis. ALL or ANY operator puts additional pair of parenthesis. The fix is to not putting in unneeded pair, because Sybase reports syntax error for ANY or ALL subquery having the extra enclosing parenthesis. case 2. Distinct function first, do not confuse this DISTINCT function with SELECT DISTINCT. SELECT DISTINCT will not got through the Distinct.class code. The Distinct function is only associated with a COUNT function. It is safe that given the fact OpenJpa only pushdown COUNT(DISTINCT one-item), we can enclose one-item with or without parenthesis. The fix is not to enclose one-time to get around syntax error reported by Informix. So far, I have verified the fix against DB2, Oracle, Sybase, SQLServer and Informix. Help needed to verify other back-end systems.

          People

          • Assignee:
            Catalina Wei
            Reporter:
            Catalina Wei
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development