"SQL query" module and Postgre query: I get "Syntax error" for a query that on PostgreSQL server works

[MarGraz]

Describe the bug

I'm using a PostgreSQL database and have created a query to retrieve users stored in it. The query works when executed directly in pgAdmin, but fails when used in the Orchard Core SQL Query module.

This is the query that works when executed on the PostgreSQL server:

SELECT 
    UI."DocumentId",
    UI."UserId",
    UI."NormalizedUserName",
    UI."NormalizedEmail",
    STRING_AGG(UBRNI."RoleName", ',') AS "RoleNames",
    STRING_AGG(UBRNID."UserByRoleNameIndexId"::text, ',') AS "RoleIds"
FROM 
    public."UserIndex" UI
JOIN 
    public."UserByRoleNameIndex_Document" UBRNID ON UI."DocumentId" = UBRNID."DocumentId"
JOIN 
    public."UserByRoleNameIndex" UBRNI ON UBRNID."UserByRoleNameIndexId" = UBRNI."Id"
GROUP BY 
    UI."DocumentId",
    UI."UserId",
    UI."NormalizedUserName",
    UI."NormalizedEmail"

This is the query used in Orchard Core's SQL Query module.
Notice that the public. prefix is omitted as it's unnecessary in Orchard Core:

SELECT 
    UI."DocumentId",
    UI."UserId",
    UI."NormalizedUserName",
    UI."NormalizedEmail",
    STRING_AGG(UBRNI."RoleName", ',') AS RoleNames,
    STRING_AGG(UBRNID."UserByRoleNameIndexId"::text, ',') AS RoleIds
FROM 
    "UserIndex" UI
JOIN 
    "UserByRoleNameIndex_Document" UBRNID ON UI."DocumentId" = UBRNID."DocumentId"
JOIN 
    "UserByRoleNameIndex" UBRNI ON UBRNID."UserByRoleNameIndexId" = UBRNI."Id"
GROUP BY 
    UI."DocumentId",
    UI."UserId",
    UI."NormalizedUserName",
    UI."NormalizedEmail"

Below the schema I used:

{
    "type": "object",
    "properties": { 
        "UserId" : {
            "type": "string"
        },
        "NormalizedUserName" : {
            "type": "string"
        },
        "NormalizedEmail" : {
            "type": "string"
        },
        "RoleNames" : {
            "type": "string"
        },
        "RoleIds" : {
            "type": "string"
        }
    }
}

When running this query in the Queries > All queries page, I receive a syntax error at line 6:

Syntax error, expected: ., (, +, -, *, /, %, &, |, ^, =, >, <, >=, <=, <>, !=, !<, !>, AND, OR, LIKE, NOT LIKE, NOT, BETWEEN, IN, ,, ), GROUP, HAVING, ORDER, LIMIT, OFFSET, UNION, ;, WITH, SELECT, INNER, LEFT, RIGHT, JOIN, WHERE at line:6, col:45

After casting line 6 into text: STRING_AGG(UBRNI."RoleName"::text, ',') AS RoleNames,, it then indicates an error on line 5:

Syntax error, expected: ., (, +, -, *, /, %, &, |, ^, =, >, <, >=, <=, <>, !=, !<, !>, AND, OR, LIKE, NOT LIKE, NOT, BETWEEN, IN, ,, ), GROUP, HAVING, ORDER, LIMIT, OFFSET, UNION, ;, WITH, SELECT, INNER, LEFT, RIGHT, JOIN, WHERE at line:5, col:31

Adding ::text cast to line 5 leads to an error on line 4. After adding ::text also to the other rows, I went till the point where it tells me there's an error on line 1.

It seems there's a bug with how Orchard Core processes the query.

To Reproduce

Steps to reproduce the behavior:

1. Having Orchard Core with PostgreSQL installed;
2. Enable the Orchard Core Queries and SQL Queries features;
3. Adding the query and the schema above and save;
4. Execute the query from the query list.

Expected behavior

The query that operates correctly on the PostgreSQL server should also function properly in Orchard Core's All queries page.

[lampersky]

@MarGraz this :: casting operator is a PostgreSQL thing, and it is not supported by grammar (and parser), all other db engines are using CAST() function, unfortunately grammar was extended to allow us to use some functions, but currently cast(column as type) is not supported, only calling functions like this function(column1, column2, ...) is allowed

as a workaround, you could write your own postgresql functions and use them in your query:

[pseudocode]

• function cast2text(column) { return CAST (column AS text); }
• function cast2int(column) { return CAST (column AS int); }

[MarGraz]

@lampersky, thank you. I will try using functions and update this discussion.

Do you know if there are plans to implement the CAST() function in the future? Because using SQL Server, I didn't have this problem (the cast was not necessary), but with PostgreSQL, I need to cast.

Thank you

[sebastienros]

Might be something of value to add a CAST() function in the generic dialect, then convert it to specific ones.