SMS2 Mapping with the word, "cube"

Support Bug
1

I am mapping from a Postgres DB using a VG. The SMS2 mapping file looks like:

MAPPING 
FROM SQL
{ select id, commodity, teus, cube, ...
         from schema.tableName
}
TO
{ ... }
WHERE
{ ... }

When saving I get the attached "Failed to update" error.

FailedToUpdate
FailedToUpdate918×252 42 KB

It appears that the word "cube" which defines a volumetric measure is the problem.

Andrea

2

Cube is a reserved keyword in postgres. Try enclosing your column name in double quotes.

3

If I enclose the word in double quotes, how do I reference it in the TO { } or WHERE { } clauses??

Also, the following works in PSQL:
select cube from schemaName:tableName LIMIT 10;
with no problem.

I do not own the database and cannot dictate the column names.

Andrea

4

Can you send the SQL query that Stardog produces?

5

No because I can't get the VG to save in Studio.

Andrea

6

Can you include the error from stardog.log?

7

Here you go:

Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered "cube" at line 117, column 42.
Was expecting one of:
    "ABS" ...
    "ARRAY" ...
    "AVG" ...
    "CARDINALITY" ...
    "CASE" ...
    "CAST" ...
    "CEIL" ...
	...
    <UNICODE_QUOTED_IDENTIFIER> ...

        at org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:31029) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:30846) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectExpression(SqlParserImpl.java:7675) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectItem(SqlParserImpl.java:6900) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectList(SqlParserImpl.java:6887) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:3649) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:590) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:13201) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:12678) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:464) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:3557) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:3595) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:194) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:160) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:187) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:201) ~[calcite-core-1.21.0.jar:1.21.0]
        at org.apache.calcite.tools.Planner.parse(Planner.java:50) ~[calcite-core-1.21.0.jar:1.21.0]
        at com.complexible.stardog.virtual.vega.calcite.RelSqlParser.parseToRelNode(RelSqlParser.java:33) ~[stardog-virtual-core-7.3.2.jar:?]
        at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.lambda$parseOrGenerateMappings$1(RdbmsMappings.java:90) ~[stardog-virtual-core-7.3.2.jar:?]
        at com.complexible.stardog.virtual.vega.rdbms.RdbmsMappings.buildTriplesMapsFromSms2Mapping(RdbmsMappings.java:190) ~[stardog-virtual-core-7.3.2.jar:?]
        ... 26 more
8

You can still use the variable corresponding to the field in the mapping target.

9

Wow! Cool! That worked.

Andrea

10

You’re able to save the vg now?