Exception parsing query that casts ROW

[hdeyo]

We are using JSqlParser (version 4.1) to parse queries from Amazon's Athena service. Many queries include a cast to a formatted ROW. Here is a simple example of this:

SELECT CAST(ROW(dataid, value, calcMark) AS ROW(datapointid CHAR, value CHAR, calcMark CHAR)) AS datapoints

This throws the following exception:

com.morningstar.neti.sqlparse.SqlParseException: Failed to parse sql: 'SELECT CAST(ROW(dataid, value, calcMark) AS ROW(datapointid CHAR, value CHAR, calcMark CHAR)) AS datapoints'
net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "ROW" "ROW"
at line 1, column 45.

Was expecting one of:

"BINARY"
"BIT"
"CHAR"
"CHARACTER"
"DOUBLE"
"INTERVAL"
"JSON"
"SET"
"SIGNED"
"UNSIGNED"
"XML"
<DT_ZONE>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<S_IDENTIFIER>

Is this currently supported by the parser? If not, could it be added?

Thanks.

[manticore-projects]

Thank you for your report.

SELECT CAST(ROW(dataid, value, calcMark) AS ROW(datapointid CHAR, value CHAR, calcMark CHAR)) AS datapoints

Is this currently supported by the parser?

I think, it is not supported yet.

If not, could it be added?

Definitely, but only when you would like to point me on the specification of this particular syntax (the BNF).

Cheers

[wumpz]

A row constructur is already supported. The problem is the target row. This is not yet supported.

This works

SELECT CAST(ROW(dataid, value, calcMark) AS testcol) AS datapoints

The cast expression (line 4012) at the moment only supports data types. Here a RowConstructor should be added as well.

[manticore-projects]

Heads-up: I got CAST(ROW(dataid, value, calcMark) AS ROW(datapointid CHAR, value CHAR, calcMark CHAR)) working -- at the cost of breaking literally everything else.

Would be back soonest when everything else works again.

[manticore-projects]

Please give it a try and stress test it and let me know any failures.
I do not use this particular construct myself and so lack the practical experience here.