MySQLDBFunctionSymbolFactory - XSD Cast Functions - Issues

[Thomas Taylor]

Hi all,

I am having a few issues with some new code in the MySQLDBFunctionSymbolFactory related to the `XSD cast functions` commit.

File: /ontop-rdb/src/main/java/it/unibz/inf/ontop/model/term/functionsymbol/db/impl/MySQLDBFunctionSymbolFactory.java

=== Issue #1 === https://github.com/ontop/ontop/pull/784
First, line #335 is missing a space after `REGEXP`:
#335: return String.format("CASE WHEN %1$s NOT REGEXP" + numericPattern +


=== Issue #2 ===
Second, `serializeCheckAndConvertInteger`, line #394 seems wrong:
#394: return String.format("IF(%1$s REGEXP '[^0-9]+$', %1$s RLIKE(if(1=1,')','a')) , " +
1. %1$s RLIKE(if(1=1,')','a'))
2. %1$s RLIKE(if(TRUE,')','a'))
3. %1$s RLIKE(')')

-- Problem 1: Why is RLIKE used instead of REGEXP? According to MySQL they are synonyms, and RLIKE isn't used anywhere else.
-- Problem 2: RLIKE syntax is `exp RLIKE pattern`, or `REGEXP_LIKE(expr, pattern [, format])`, what is the point of the extra parentheses?
-- Problem 3: %1$s RLIKE(')') -- results in MySQL Regex error: 'unmatched closing parenthesis at offset 0'

#384-398:
// SIGNED as a datatype cast truncates scale. This workaround addresses the issue.
@Override
protected String serializeCheckAndConvertInteger(ImmutableList<? extends ImmutableTerm> terms,
             Function<ImmutableTerm, String> termConverter, TermFactory termFactory) {
  String term = termConverter.apply(terms.get(0));
  if (isMySQLVersion8OrAbove()) {
    return String.format("IF(%1$s REGEXP '[^0-9]+$', NULL , " +
        "FLOOR(ABS(CAST(%1$s AS DECIMAL(60,30))))  * SIGN(CAST(%1$s AS DECIMAL(60,30)))) ",
      term);
  } else {
    return String.format("IF(%1$s REGEXP '[^0-9]+$', %1$s RLIKE(if(1=1,')','a')) , " +
        "FLOOR(ABS(CAST(%1$s AS DECIMAL(60,30)))) * SIGN(CAST(%1$s AS DECIMAL(60,30)))) ",
      term);
  }
}

=== Issue #3 ===
Finally, if I connect to MariaDB 10.7.8 (docker mariadb:10) using mysql-connector-j:8.0.33, the database version string is: "5.5.5-10.7.8-MariaDB-1:10.7.8+maria~ubu2004"
This causes `isMySQLVersion8OrAbove` to return `false`, because `5` is not greater than `7`.


#322-328:
// Cast and regex differ in MySQL version 8 and above vs. previous versions
private boolean isMySQLVersion8OrAbove() {
return databaseInfoSupplier.getDatabaseVersion().isPresent() &&
databaseInfoSupplier.getDatabaseVersion()
.map(s -> Integer.parseInt(s.substring(0, s.indexOf("."))))
.filter(s -> s > 7 ).isPresent();
}

[Benjamin Cogrel]

Hi Thomas,

Thanks for reporting the issue, Albulen is having a look. Here is his draft PR: https://github.com/ontop/ontop/pull/787 .

Best,

Benjamin

--
Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ontop4obda/dc7e6b1d-ccf9-4a44-a45b-631d1cd07884n%40googlegroups.com.