Thibaud Faurie created IGNITE-14905:
---------------------------------------
Summary: SQL GROUP BY Column not found issue
Key: IGNITE-14905
URL: https://issues.apache.org/jira/browse/IGNITE-14905
Project: Ignite
Issue Type: Bug
Components: cache, sql, thin client
Affects Versions: 2.10
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
Reporter: Thibaud Faurie
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()
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
