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

        Catalina Wei created issue -
        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.
        Catalina Wei made changes -
        Field Original Value New Value
        Fix Version/s 1.2.0 [ 12313102 ]
        Assignee Catalina Wei [ fancy ]
        Resolution Fixed [ 1 ]
        Status Open [ 1 ] Resolved [ 5 ]
        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.
        Donald Woods made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Resolved Resolved
        8h 54m 1 Catalina Wei 22/May/08 15:58
        Resolved Resolved Closed Closed
        656d 2h 34m 1 Donald Woods 09/Mar/10 18:32

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development