Rdb2RdfTest dg0016 Failure: VARBINARY is not properly converted to xsd:hexBinary

[thomasjtaylor]

Description

Using the same Rdb2RdfTest dg0016 from #558

Using MySQL 5. There seems to be a problem converting the VARBINARY column to hexBinary. Instead of base64-encoding the binary data as a hex string, it looks like the binary data is returned unencoded. https://www.w3.org/TR/xmlschema-2/#hexBinary

SQL CREATE/INSERT

CREATE TABLE "Patient" (
"ID" INTEGER,
"FirstName" VARCHAR(50),
"LastName" VARCHAR(50),
"Sex" VARCHAR(6),
"Weight" REAL,
"Height" FLOAT,
"BirthDate" DATE,
"EntranceDate" TIMESTAMP,
"PaidInAdvance" BOOLEAN,
"Photo" VARBINARY(200),
PRIMARY KEY ("ID")
);

INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") 
VALUES (10,'Monica','Geller','female',80.25,1.65,'1981-10-10','2009-10-10 12:12:22',FALSE,
X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFEBFC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082');

SQLPPMapping

[PrefixDeclaration]
rdf:		http://www.w3.org/1999/02/22-rdf-syntax-ns#
rdfs:		http://www.w3.org/2000/01/rdf-schema#
owl:		http://www.w3.org/2002/07/owl#
xsd:		http://www.w3.org/2001/XMLSchema#
obda:		https://w3id.org/obda/vocabulary#

[MappingDeclaration] @collection [[
mappingId	MAPPING-ID1
target		<http://example.com/base/Patient/ID={ID}> a <http://example.com/base/Patient> ; <http://example.com/base/Patient#ID> {ID}^^xsd:integer ; <http://example.com/base/Patient#FirstName> {FirstName}^^xsd:string ; <http://example.com/base/Patient#LastName> {LastName}^^xsd:string ; <http://example.com/base/Patient#Sex> {Sex}^^xsd:string ; <http://example.com/base/Patient#Weight> {Weight}^^xsd:double ; <http://example.com/base/Patient#Height> {Height}^^xsd:double ; <http://example.com/base/Patient#BirthDate> {BirthDate}^^xsd:date ; <http://example.com/base/Patient#EntranceDate> {EntranceDate}^^xsd:dateTime ; <http://example.com/base/Patient#PaidInAdvance> {PaidInAdvance}^^xsd:boolean ; <http://example.com/base/Patient#Photo> {Photo}^^xsd:hexBinary . 
source		SELECT * FROM `Patient`
]]

SQL Translated Query

New query after the dialect-specific extra normalization:

During query translation, the VARBINARY column results in this statement:

   CONSTRUCT [ID1m20, v30, v49, v9] [v9/"7"^^BIGINT, v30/"http://example.com/base/Patient#Photo"^^TEXT, v49/VARBINARYToTEXT(Photo1m9)]
      FILTER IS_NOT_NULL(Photo1m9)
         EXTENSIONAL `Patient`(0:ID1m20,9:Photo1m9)

SQL Query (other columns omitted for clarity)

SELECT v23.`ID1m20` AS `ID1m20`, v23.`v30` AS `v30`, v23.`v49` AS `v49`, v23.`v9` AS `v9`
FROM (SELECT v1.`ID` AS `ID1m20`, 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AS `v30`, 'http://example.com/base/Patient' AS `v49`, 0 AS `v9`
FROM `Patient` v1
UNION ALL 
SELECT v19.`ID` AS `ID1m20`, 'http://example.com/base/Patient#Photo' AS `v30`, CAST(v19.`Photo` AS CHAR CHARACTER SET utf8) AS `v49`, 7 AS `v9`
FROM `Patient` v19
WHERE v19.`Photo` IS NOT NULL
) v23

Expected:

<http://example.com/base/Patient/ID=10> <http://example.com/base/Patient#Photo> "89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFEBFC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082"^^<http://www.w3.org/2001/XMLSchema#hexBinary> .

Actual:

<http://example.com/base/Patient/ID=10> <http://example.com/base/Patient#Photo> "?PNG\r\n\n ***BINARY DATA*** IEND?B`?"^^<http://www.w3.org/2001/XMLSchema#hexBinary> .

Possible Fix: (only tested for MySQL)

Wrap VARBINARY columns with HEX( ... ) Original:

SELECT v19.`ID` AS `ID1m20`, 'http://example.com/base/Patient#Photo' AS `v30`, CAST(v19.`Photo` AS CHAR CHARACTER SET utf8) AS `v49`, 7 AS `v9`

Fixed:

SELECT v19.`ID` AS `ID1m20`, 'http://example.com/base/Patient#Photo' AS `v30`, CAST(HEX(v19.`Photo`) AS CHAR CHARACTER SET utf8) AS `v49`, 7 AS `v9`

Versions

Ontop: 4.2.1 (Maven Central) Mysql: mysql:5 (Docker Hub) Driver: mysql-connector-java:8.0.30

Additional Information

I believe this test passes with H2.

It appears to be in the VARBINARYToTEXT substitution, but I have been unable to track down its implementation in the code.