[IGNITE-14905] SQL GROUP BY Column not found issue

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.10
• Component/s: cache, sql, thin client

Environment

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

• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Thibaud Faurie
• Created: 16/Jun/21 00:18
• Updated: 16/Jun/21 00:18

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()