Virtual Graph SQL query parser is fairly broken (SQL SERVER)

I've been fighting the sql query parser for virtual graphs all day 5hrs+ now.

I've setup my .properties file so that:

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql.functions=charindex,substring

And submitting the following query:

foo:{ID}\/address a schema:address;
       schema:addressLocality "{City}";
       schema:addressRegion "{Region}";
       schema:addressCountry "{Country}";
       schema:postalCode "{Zip}";
       sm:map [
       sm:query """
       select
         ID
         ,City
         ,Zip
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(SUBSTRING(StateProvID , 0 , CHARINDEX('_',StateProvID)   ))
           ELSE ''
           END
         as Country
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(SUBSTRING(StateProvID , CHARINDEX('_',StateProvID)  + 1 , LEN(StateProvID)- CHARINDEX('_',StateProvID)))
           ELSE ''
           END
         as Region
       from foo
       WHERE baz = 'TRUE'
       """
       ].       

And I get this error:

com.complexible.stardog.cli.CliException: Unable to parse R2RML view query 
       [[            
       ...      
       ]]: 
       From line 8, column 21 to line 8, column 86: 
       Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<VARCHAR(8)> FROM <INTEGER> FOR <VARCHAR(1)>)'. 
       Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)' 'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)' 'SUBSTRING(<VARCHAR> FROM <INTEGER>)' 'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)' 'SUBSTRING(<BINARY> FROM <INTEGER>)' 'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)' 'SUBSTRING(<VARBINARY> FROM <INTEGER>)' 'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
       at com.complexible.stardog.cli.impl.ConnectionCommand.call(ConnectionCommand.java:73)

But I've got SubString added to sql.functions in my .properties file..?!

Ok.. I'll use a different function..
Properties file:

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql.functions=charindex,substring,LEFT,RIGHT

but now..

ERROR 2017-07-27 15:20:49,865 [XNIO-1 task-1] com.complexible.stardog.virtual.DefaultVirtualGraphRegistry:_add(201): Cannot initialize virtual graph 0ee4daa6-30ae-490f-8399-befb4e3080fd
java.lang.IllegalArgumentException: Unable to parse R2RML view query [[
            select 
         ID
,City
,Zip
, CASE WHEN CHARINDEX('_',StateProvID) > 0
       THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1  ))
       ELSE ''
       END
       as Country
       , CASE WHEN CHARINDEX('_',StateProvID) > 0
       THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
       ELSE ''
       END
       as Region
       from foo
       WHERE baz = 'TRUE'
        ]]: Encountered "( LEFT" at line 8, column 20
	at com.complexible.stardog.virtual.vega.VegaTriplesMap.parseViewQuery(VegaTriplesMap.java:190) ~[stardog-virtual-core-5.0.1.jar:?]

I did have "LEFT" in the .properties file... which the docs say:

sql.functions
A comma-separated list of SQL function names to register with the parser. If an R2RML view (using rr:sqlQuery) fails to parse, this option can be set to allow use of non-standard functions.

So that's not working..

So finally I decide to just dump all the data after its processed into a temp table...

    select 
         ID
,City
,Zip
, CASE WHEN CHARINDEX('_',StateProvID) > 0
       THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1  ))
       ELSE ''
       END
       as Country
       , CASE WHEN CHARINDEX('_',StateProvID) > 0
       THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
       ELSE ''
       END
       as Region

  INTO ##MY_SHARED_TEMP_TABLE

       from foo
       WHERE baz = 'TRUE'

And then I change my virtual graph query to pull from the temp table. But that's not working either!

ERROR 2017-07-27 15:02:44,619 [XNIO-1 task-55] com.complexible.stardog.virtual.DefaultVirtualGraphRegistry:_add(201): Cannot initialize virtual graph d360f0ff-eee7-4b4b-b527-e78c79a01bf1
java.lang.IllegalArgumentException: Unable to parse R2RML view query [[
            select 
             ID
            ,City
            ,Zip
            ,Country
            ,Region
            from ##MY_SHARED_TEMP_TABLE 
        ]]: 
   Lexical error at line 8, column 14.  Encountered: "#" (35), after : ""
	at com.complexible.stardog.virtual.vega.VegaTriplesMap.parseViewQuery(VegaTriplesMap.java:190) ~[stardog-virtual-core-5.0.1.jar:?]

I've been burning a lot of time on this and I can't understand why it wont just send my query as is to the jdbc driver and insists on rewriting it.

Other variations I've tried continue to generate invalid sql which causes the jdbc driver to throw and error, or it simply won't get passed the stardog sql parser.

eg: Invalid SQL sent to the JDBC driver

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near '('.
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
       at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
       at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
       at com.complexible.stardog.virtual.VirtualGraphIterator.nextMapping(VirtualGraphIterator.java:121)
       ... 52 more
       WARN  2017-07-27 14:56:38,126 [XNIO-1 task-38] com.complexible.common.rdf.rio.RDFStreamProcessor:setException(587):
       Error during loading Memory stream (? triples):
       Unable to execute query: SELECT ... CASE WHEN "charindex"('_', "StateProvID") > 0 ...
       FROM "dbo"."foo"
       WHERE "bar" = 'TRUE'
       com.complexible.stardog.plan.eval.operator.OperatorException:
       Unable to execute query: SELECT ... CASE WHEN "charindex"('_', "StateProvID") > 0 ...
       FROM "dbo"."foo"
       WHERE "bar" = 'TRUE'
       at com.complexible.stardog.virtual.VirtualGraphIterator.computeNext(VirtualGraphIterator.java:228) ~[stardog-virtual-core-5.0.1.jar:?]


It looks like you materializing using virtual import. You can set query.translation=legacy in your properties file to treat the query as an opaque string. It’s import for federation to understand the query in the mapping so we can build an efficient SQL query. With materialization, we execute the queries in the same form as expressed in the mappings.

I’m not seeing the complete query with invalid syntax. Do you have the entire query?

Jess

1 Like

CharIndex is a function and its being double quoted.

Which is why the jdbc driver is complaining about :

I'll try the legacy behavior, thanks for the suggestion!

I see. Thanks for pointing it out. We generally quote identifiers and quoting function names works fine in other databases. I’ll create a new ticket for this and get it fixed shortly.

Jess

Awesome, thanks!

Also I just tried the legacy behavior and am now getting this error:

./stardog-admin virtual import foos ~/project/foobar.properties ~/project/virtual.allfoo.ttl -v
java.lang.IllegalArgumentException: There was an error analyzing the following mappings. Please correct the issue(s) to continue.
Error in mapping with id: mapping-1227121384#645594a4ec0f4c9a8cd737e71c81f847 
 Description: Error in identifying column name "Region", please check the query source in the mappings.
Possible reasons:
1. The name is ambiguous, or
2. The name is not defined in the database schema.
{fooID=t0, view_1.fooID=t0, City=t1, view_1.City=t1, Zip=t2, view_1.Zip=t2, END=t3, view_1.END=t3} 
Mapping: [
            select 
         fooID
         ,City
         ,Zip
         ,StateProvID
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1  ))
           ELSE ''
           END
         as Country
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
           ELSE ''
           END
         as Region
       from foo rcr
       WHERE IsClaimed = 'TRUE'
         ==> [http://schema.org/addressRegion(URI("http://foobar.com/bax/{}/address",fooID),Region)]]

The detailed stack trace for the error is:
com.complexible.stardog.cli.CliException: java.lang.IllegalArgumentException: There was an error analyzing the following mappings. Please correct the issue(s) to continue.
Error in mapping with id: mapping-1227121384#645594a4ec0f4c9a8cd737e71c81f847 
 Description: Error in identifying column name "Region", please check the query source in the mappings.
Possible reasons:
1. The name is ambiguous, or
2. The name is not defined in the database schema.
{fooID=t0, view_1.fooID=t0, City=t1, view_1.City=t1, Zip=t2, view_1.Zip=t2, END=t3, view_1.END=t3} 
Mapping: [
            select 
         fooID
         ,City
         ,Zip
         ,StateProvID
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1  ))
           ELSE ''
           END
         as Country
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
           ELSE ''
           END
         as Region
       from foo rcr
       WHERE IsClaimed = 'TRUE'
         ==> [http://schema.org/addressRegion(URI("http://foobar.com/bax/{}/address",fooID),Region)]]

	at com.complexible.stardog.cli.impl.ConnectionCommand.call(ConnectionCommand.java:73)
	at com.complexible.stardog.cli.CLIBase.execute(CLIBase.java:55)
	at com.complexible.stardog.cli.admin.CLI.main(CLI.java:182)
Caused by: com.complexible.stardog.StardogException: java.lang.IllegalArgumentException: There was an error analyzing the following mappings. Please correct the issue(s) to continue.
Error in mapping with id: mapping-1227121384#645594a4ec0f4c9a8cd737e71c81f847 
 Description: Error in identifying column name "Region", please check the query source in the mappings.
Possible reasons:
1. The name is ambiguous, or
2. The name is not defined in the database schema.
{fooID=t0, view_1.fooID=t0, City=t1, view_1.City=t1, Zip=t2, view_1.Zip=t2, END=t3, view_1.END=t3} 
Mapping: [
            select 
         fooID
         ,City
         ,Zip
         ,StateProvID
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(LEFT(StateProvID , CHARINDEX('_',StateProvID) -1  ))
           ELSE ''
           END
         as Country
         , CASE WHEN CHARINDEX('_',StateProvID) > 0
         THEN UPPER(RIGHT(StateProvID , LEN(StateProvID) - CHARINDEX('_',StateProvID)))
           ELSE ''
           END
         as Region
       from foo rcr
       WHERE IsClaimed = 'TRUE'
         ==> [http://schema.org/addressRegion(URI("http://foobar.com/bax/{}/address",fooID),Region)]]

	at com.complexible.stardog.protocols.http.client.BaseHttpClient.checkResponseCode(BaseHttpClient.java:492)
	at com.complexible.stardog.protocols.http.client.BaseHttpClient.execute(BaseHttpClient.java:324)
	at com.complexible.stardog.protocols.http.client.BaseHttpClient.executeHttpPost(BaseHttpClient.java:662)
	at com.complexible.stardog.protocols.http.client.BaseHttpClient.executeHttpPost(BaseHttpClient.java:637)
	at com.complexible.stardog.protocols.http.client.HttpVirtualGraphAdminClientImpl.addOrUpdate(HttpVirtualGraphAdminClientImpl.java:233)
	at com.complexible.stardog.protocols.http.client.HttpVirtualGraphAdminClientImpl.addGraph(HttpVirtualGraphAdminClientImpl.java:202)
	at com.complexible.stardog.protocols.http.client.HttpVirtualGraphAdminClientImpl.addGraph(HttpVirtualGraphAdminClientImpl.java:51)
	at com.complexible.stardog.protocols.http.client.AbstractVirtualGraphAdminConnection.addGraph(AbstractVirtualGraphAdminConnection.java:86)
	at com.complexible.stardog.virtual.cli.VirtualGraphImport.execute(VirtualGraphImport.java:135)
	at com.complexible.stardog.cli.impl.ConnectionCommand.call(ConnectionCommand.java:61)
	... 2 more

Can you send the error from the log file?

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.