Uploaded image for project: 'Ignite'
  1. Ignite
  2. IGNITE-14906

SQL GROUP BY Column not found issue

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 2.10
    • None
    • cache, sql, thin client
    • None
    • Ignite server nodes on linux docker containers.

      Application executed in linux docker container.

      Windows 10 pro : DBeaver, VSCode

      My application language : Scala with Apache Ignite Java official library

    • Missclick : Duplicate of IGNITE-14905
    • Docs Required, Release Notes Required

    Description

      I have a query that I want to execute through SQL API.

      I have no issue when I run my Query with DBeaver (via Ignite thin client).
      But as soon as I use it in my code with SQL API, it throws an error saying that column permission_id is not found.

      More stranger thing, if I pause execution of my code (which generates an instance of client node in my cluster) at the SqlFieldsQuery execution step, the same error can be thrown by DBeaver sometimes (not 100% accurate)

      However, I figured out this error only occurs when I use the GROUP BY clause and some aggregation functions such as GROUP_CONCAT. As soon as I remove those, it works like a charm. I suspect some bug with H2 and distributed caches.

      Here is my SQL query :
      SELECT app_id, app_label, app_version, app_universal_id, app_status, app_manifest_url, app_store_url, app_created_at, app_updated_at,
      CONCAT_WS('||', permission_id, GROUP_CONCAT(content SEPARATOR ';'), code, language_id, label, GROUP_CONCAT(text_id SEPARATOR ';')) AS info_data, 'PERMISSION_LANG_VARIANT' AS type_data
      FROM (
          SELECT APP.id AS app_id, APP.label AS app_label, PERMISSION.id AS permission_id, TEXT.content, LANG.code, TEXT.language_id, LANG.label, TEXT.id AS text_id, APP.version AS app_version, APP.app_universal_id AS app_universal_id, APP.status AS app_status, APP.manifest_url AS app_manifest_url, APP.store_url AS app_store_url, APP.created_at AS app_created_at, APP.updated_at AS app_updated_at
          FROM FUSION.APPLICATION AS APP
          INNER JOIN FUSION.PERMISSION AS PERMISSION ON APP.id = PERMISSION.application_id
          INNER JOIN FUSION.TEXT AS TEXT ON TEXT.id = PERMISSION.label_text_id
          INNER JOIN FUSION.LANGUAGE AS LANG ON TEXT.language_id = LANG.id
          UNION ALL
          SELECT APP.id AS app_id, APP.label AS app_label, PERMISSION.id AS permission_id, TEXT.content, LANG.code, TEXT.language_id, LANG.label, TEXT.id AS text_id, APP.version AS app_version, APP.app_universal_id AS app_universal_id, APP.status AS app_status, APP.manifest_url AS app_manifest_url, APP.store_url AS app_store_url, APP.created_at AS app_created_at, APP.updated_at AS app_updated_at
          FROM FUSION.APPLICATION AS APP
          INNER JOIN FUSION.PERMISSION AS PERMISSION ON APP.id = PERMISSION.application_id
          INNER JOIN FUSION.TEXT AS TEXT ON TEXT.id = PERMISSION.description_text_id
          INNER JOIN FUSION.LANGUAGE AS LANG ON TEXT.language_id = LANG.id
      )
      GROUP BY permission_id, language_id

      Here is the code I use to execute:
      var igniteQuery = new SqlFieldsQuery(queryString)
      var query = igniteCache.query(igniteQuery)
      query.getAll()

      Attachments

        Activity

          People

            Unassigned Unassigned
            TurboGofre Thibaud Faurie
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: