Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2259

SQL generation error for the JPQL IN statement

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.2.0
    • Fix Version/s: None
    • Component/s: query, sql
    • Labels:
      None

      Description

      Dear OpenJPA Team,

      We have created a JPQL query for finding all (newest) "GuidedMaintenance" entity who contains at least one "MaintenanceModule" entity from a MaintenanceModule list parameter :
      =========================
      SELECT M FROM GUIDEDMAINTENANCE AS M WHERE (M.previous IS NULL AND M.next IS NULL) OR (M.previous IS NOT NULL AND M.next IS NULL) AND M.modules IN (?1)

      The Java query snippet :
      =========================
      TypedQuery<GuidedMaintenance> query = em.createNamedQuery("guidedmaintenance.contains.module", GuidedMaintenance.class);
      query.setParameter(1, Arrays.asList(modules)); // ModuleMaintenance[] modules, white one entity

      We obtains this SQL exception :
      =========================
      Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Syntax error: Encountered "NULL" at line 1, column 1369.

      {SELECT t0.ID, t0.DTYPE, t0.ATTRIBUTION, t0.COMPULSORY, t0.DESCRIPTION, t0.FREQUENCY, t0.KEEPORDER, t2.ID, t2.LABEL, t0.TITLE, t0.TYPE, t0.WORKABLE, t4.ID, t3.ID, t4.EMAIL, t4.ENABLE, t4.NAME, t4.SURNAME, t4.TELEPHONE, t3.ETP, t3.ETPMAINT, t3.LOGIN, t3.PASSWORD, t3.QLQLEVEL, t3.QLXLEVEL, t3.STOCKLEVEL, t5.ID, t5.DTYPE, t5.ATTRIBUTION, t5.COMPULSORY, t5.DESCRIPTION, t5.FREQUENCY, t5.KEEPORDER, t5.TITLE, t5.TYPE, t5.WORKABLE, t5.TOLERANCE, t5.TRACABILITY, t6.ID, t6.DTYPE, t6.ATTRIBUTION, t6.COMPULSORY, t6.DESCRIPTION, t6.FREQUENCY, t6.KEEPORDER, t6.TITLE, t6.TYPE, t6.WORKABLE, t6.TOLERANCE, t6.TRACABILITY, t0.TOLERANCE, t0.TRACABILITY FROM "CMP-MAINT".MAINTENANCES t0 LEFT OUTER JOIN "CMP-MAINT".MAINTENANCES_MODULEMAINTENANCES t1 ON t0.ID = t1.MAINTENANCE_ID LEFT OUTER JOIN QUALINAX.ROLES t2 ON t0.REQUIRED_ID = t2.ID INNER JOIN QUALINAX.INTERNALUSERS t3 ON t0.EDITOR_ID = t3.ID LEFT OUTER JOIN "CMP-MAINT".MAINTENANCES t5 ON t0.ID = t5.PREVIOUS_ID LEFT OUTER JOIN "CMP-MAINT".MAINTENANCES t6 ON t0.PREVIOUS_ID = t6.ID INNER JOIN QUALINAX.USERS t4 ON t3.ID = t4.ID WHERE (t0.PREVIOUS_ID IS NULL AND 0 = (SELECT COUNT(*) FROM "CMP-MAINT".MAINTENANCES WHERE "CMP-MAINT".MAINTENANCES.PREVIOUS_ID = t0.ID) OR t0.PREVIOUS_ID IS NOT NULL AND 0 = (SELECT COUNT(*) FROM "CMP-MAINT".MAINTENANCES WHERE "CMP-MAINT".MAINTENANCES.PREVIOUS_ID = t0.ID) AND t1.MODULES_ID IN (NULL)) AND t0.DTYPE = ? AND (t5.DTYPE IS NULL OR t5.DTYPE IN (?)) AND (t6.DTYPE IS NULL OR t6.DTYPE IN (?))}

      [code=30000, state=42X01]

      I think it's a bug because our modules list isn't empty and we obtains SQL statement "t1.MODULES_ID IN (NULL)".

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              gchauvet Guillaume Chauvet
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: