Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-586

foreign key alias sub-field query causes "missing FROM-clause" error

    XMLWordPrintable

Details

    • CP: sprint 112
    • 3
    • Core: Platform
    • R1 2021 Bug Fix

    Description

      When this foreign key entry in schema.json is used:

      {
  "fieldName": "copyrightTracking.copyrightStatusId",
  "tableAlias": "copyrightStatus",
  "targetTable": "coursereserves_copyrightstates",
  "targetTableAlias": "copyrightStatus",
  "tOps": "ADD"
}

      And you run this CQL query: copyrightStatus.name==cc
      Then you get this error:

      query with count: ErrorMessage(fields=[(Severity, ERROR), (V, ERROR), (SQLSTATE, 42P01), (Message, missing FROM-clause entry for table "copyrighttracking"), (Where, PL/pgSQL function count_estimate_default(text) line 2 at EXECUTE
), (p, 146), (Internal Query, SELECT COUNT(*) FROM (SELECT * FROM diku_mod_courses.coursereserves_reserves WHERE (courseListingId='08b782b1-9792-42d3-97c9-e21ba59be085') AND (coursereserves_reserves.copyrightTracking.copyrightStatusId IN
  ( SELECT id FROM coursereserves_copyrightstates WHERE lower(f_unaccent(coursereserves_copyrightstates.jsonb->>'name')) LIKE lower(f_unaccent('cc')))) LIMIT 1000) x)

      coursereserves_reserves.copyrightTracking.copyrightStatusId should be
      coursereserves_reserves.copyrightTracking_copyrightStatusId

      RMB needs to replace . by _ in the fieldName when generating SQL.

      Reported by kurt with example in https://github.com/folio-org/mod-courses/commit/dcb0dd914dc17bcf00b66f57ddcb0c989feae2ec

      TestRail: Results

        Attachments

          Issue Links

            blocks

            Bug - A problem which impairs or prevents the functions of the product. MODCR-57 Copyright filter in Courses App --> Reserves pane returns error message when value selected

            • P2 - normal priority level, must be included in the current development cycle
            • Closed

            Task - A task that needs to be done. RMB-825 Release RMB 32.2.1

            • TBD - To be determined
            • Closed
            is cloned by

            Bug - A problem which impairs or prevents the functions of the product. RMB-830 foreign key non-alias sub-field query causes "missing FROM-clause" error

            • P2 - normal priority level, must be included in the current development cycle
            • Closed
            is duplicated by

            Bug - A problem which impairs or prevents the functions of the product. RMB-585 Invalid SQL generated for coursereserves module

            • TBD - To be determined
            • Closed

            Activity

              People

                mikhail.fokanov Mikhail Fokanov
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases