Using * to specify columns on tables with columns of identical names results in one of the columns being suppressed

[joncard1]

I will attach the class files used to create the tables, but the following command resulted in a result set with only the columns ID and Name:

INSERT INTO SQLUser.Table1 (Name) VALUES ('Name 1')

INSERT INTO SQLUser.Table2 (Name) Values ('Name 2')

SELECT * FROM SQLUser.Table1 CROSS JOIN SQLUser.Table2

I expected either the columns "ID", "Name", "ID", "Name" as in the Management Portal, or "SQLUser.Table1.ID", "SQLUser.Table1.Name", "SQLUser.Table2.ID", "SQLUser.Table2.Name". All 4 columns should display.

[joncard1]

Table2.cls.txt
Table1.cls.txt

GitHub doesn't support the .cls type, so I renamed the files.

[gjsjohnmurray]

The problem lies in the AppendResultSet method of %Api.Atelier.v1, which uses the colName property of each %SQL.StatementColumn in the %GetMetaData() of the query result when constructing the JSON response to the /action/query request, without prefixing it with the schemaName and tableName.

Please raise this with InterSystems WRC. It will require a server-side change, probably a new API endpoint in order not to break existing users of the v1//action/query endpoint who may rely on receiving unqualified column names.

[joncard1]

I can do that. First thing in the morning. Thanks.

[sduncan01]

I've reproduced the behavior John mentioned independently of the SQLTools Driver (using the Query method of the Source Code File REST API) and reported it to InterSystems development. @joncard1, I will send you details on this.

[bdeboe]

A change to the server-side API (Atelier API v6) to allow result set columns to be returned positionally rather than by name is on its way to 2023.1, due for preview release in the next month or so. This can be taken advantage of on the SQLTools side to address this issue.

CC @isc-bsaviano

[isc-bsaviano]

Atelier API v6 adds support for a new positional query parameter. If it is passed and the value is 1, the response body will have the following format:

{
    "status": {
        "errors": [],
        "summary": ""
    },
    "console": [],
    "result": [
        {
            "columns": [
                {
                    "name": "Name",
                    "type": "VARCHAR(4096)"
                }
            ],
            "content": [
                [
                    "%CSP.UI.Portal.Dialog.SQLTuneTablesOutput"
                ]
            ]
        }
    ]
}

[gjsjohnmurray]

A dev build of the VSIX containing the fix for this is now available in the zip at https://github.com/intersystems-community/sqltools-intersystems-driver/suites/34862507128/artifacts/2649129169. The fix only works when the target server is 2023.1+

Once the fix has been confirmed and the PR merged, expect a new version of the driver to appear on Marketplace.