Add integration tests for mapped documents

The Elasticsearch "High level REST client" used in integration tests is deprecated. We should replace it with the new "Java API" which has fewer dependencies.

See migration guide for details.

Both ElasticSearch and Exasol support LOG10 but it seems virtual-schema-common-java does not map it.

Elastic tables with "." in the name get properly translated to "/" on read and are visible in the virtual schema table view, however they cannot be queried, since the "/"is not translated back.

Add integration tests for Literal capabilities

Summary

Loop with Unreachable Exit Condition ('Infinite Loop') vulnerability in Apache Commons Compress.This issue affects Apache Commons Compress: from 1.3 through 1.25.0.

Users are recommended to upgrade to version 1.26.0 which fixes the issue.

CVE: CVE-2024-25710
CWE: CWE-835

References

• https://ossindex.sonatype.org/vulnerability/CVE-2024-25710?component-type=maven&component-name=org.apache.commons%2Fcommons-compress&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
• http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2024-25710
• https://lists.apache.org/thread/cz8qkcwphy4cx8gltn932ln51cbtq6kf

Currently, after creating the VS we can't use nested fields in a Exasol query, as the nested fields are separated by dot(.), and Exasol SQL dialect does not let using dots(.) as quoted parameters.

For example, if we have a "book" index, with an "author" field, which has a "name" field, this will be mapped as "author.name" in Exasol but when executing the SELECT "author.name" FROM VirtualSchema.books query, Exasol will complain.

For this, we need to map the nested fields using a different character instead of a dot(.)

ElasticSearch supports TRIM, LTRIM and RTRIM but only the version with one parameter, as the default version of Exasol's. It does not support Exasol's version with 2 parameters.

We should check out if we can adapt this.

https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/rtrim.htm#RTRIM

https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-string.html#sql-functions-string-rtrim

Some queries using the virtual schema fail when converting numeric types, e.g.

SELECT FLOOR("FIELD") FROM VIRTUAL_SCHEMA."index";
SELECT TRUNC(123.4567, 2) FROM VIRTUAL_SCHEMA."index";

These queries fail with the following error message:

ETL-1299: Failed to create transformator for column=0 (starting from 0 for selected columns) [ETL-1202: Not implemented - Transformation for this combination of column types is not possible in this version. A solution for this problem can be perhaps the conversion in another datatype in the database. Otherwise please contact support for additional information]

This is a bug in Exasol 7.1.17.

Once this is fixed in Exasol we need to adapt the tests to expect correct results again.

See failing build: https://github.com/exasol/elasticsearch-virtual-schema/runs/3751354246?check_suite_focus=true

Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.1.0:audit (default-cli) on project elasticsearch-virtual-schema: Detected 1 vulnerable components:
Error:    org.elasticsearch:elasticsearch:jar:7.13.4:test; https://ossindex.sonatype.org/component/pkg:maven/org.elasticsearch/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1
Error:      * [CVE-2021-22147] Elasticsearch before 7.14.0 did not apply document and field level security to s... (6.5); https://ossindex.sonatype.org/vulnerability/a52f2ab6-086b-4285-a7a1-78ecdc6404ba?component-type=maven&component-name=org.elasticsearch.elasticsearch&utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1

Add integration tests for Scalar functions

ElasticSearch supports INTERVAL capabilities (https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html) but some extra adaptation is needed as it does not work out of the box.

Duplicate of exasol/virtual-schema-common-java#188. Needs to be fixed there.

6 Vulnerabilities

• io.netty:netty-common:jar:4.1.72.Final in test
  • CVE-2022-24823, severity CWE-378: Creation of Temporary File With Insecure Permissions (5.5)
• io.netty:netty-handler:jar:4.1.72.Final in test
  • sonatype-2020-0026: 1 vulnerability (6.5)
• org.apache.xmlrpc:xmlrpc-common:jar:3.1.3 in test
  • CVE-2016-5003, severity CWE-502: Deserialization of Untrusted Data (9.8)
  • CVE-2016-5002, severity CWE-611: Improper Restriction of XML External Entity Reference ('XXE') (7.8)
• com.fasterxml.jackson.dataformat:jackson-dataformat-cbor:jar:2.10.4 in test
  • CVE-2020-28491, severity CWE-770: Allocation of Resources Without Limits or Throttling (7.5)
• commons-codec:commons-codec:jar:1.11 in test
  • sonatype-2012-0050: 1 vulnerability (5.3)
• org.apache.xmlrpc:xmlrpc-client:jar:3.1.3 in test
  • CVE-2016-5004, severity CWE-400: Uncontrolled Resource Consumption ('Resource Exhaustion') (6.5)

1 Broken link

[✖] https://ossindex.sonatype.org/vulnerability/a52f2ab6-086b-4285-a7a1-78ecdc6404ba?component-type=maven&component-name=org.elasticsearch.elasticsearch&utm_source=ossindex-client&utm_medium=integration&utm_content=1.1.1 → Status: 404

Can probably be fixed by updating project-keeper to latest version which makes broken_links_checker.yml ignore links to opensource.org as they have blocked broken_links_checker.yml due too many requests.

Situation

We need to support nanosecond timestamp resolution.

ElasticSearch does not have a timestamp type as such. But it has a date type that can store time and coincidentally one that goes down to nanoseconds:
https://www.elastic.co/guide/en/elasticsearch/reference/current/date_nanos.html

Acceptance Criteria

• An integration test against Exasol 8 proves that TS(9) is supported

• org.eclipse.parsson:parsson:jar:1.1.1 in test
  • CVE-2023-4043, severity CWE-20: Improper Input Validation (7.5)

Vulnerability is typically introduced via transitive test dependency exasol-test-containers:

\- com.exasol:exasol-testcontainers:jar:6.6.2:test
   \- com.exasol:bucketfs-java:jar:3.1.0:test
      \- org.eclipse.parsson:parsson:jar:1.1.1:test

Hey Guys,

just would like to start with your new ES adapter (great idea!) but unfortunately the adapter didn't work on my site. If followed these instructions

https://github.com/exasol/elasticsearch-virtual-schema/blob/main/doc/dialects/elasticsearch_sql_user_guide.md

and add the latestes jars to bucket fs in the default folder. When I run

curl http://r:[email protected]:1234/default

the files are there:
virtual-schema-dist-8.0.0-elasticsearch-1.0.0.jar
x-pack-sql-jdbc-7.12.0.jar

Afterwards I've created the adapter by

CREATE OR REPLACE JAVA ADAPTER SCRIPT ELASTIC.ES_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/bfsdefault/default/virtual-schema-dist-8.0.0-elasticsearch-1.0.0.jar;
%jar /buckets/bfsdefault/default/x-pack-sql-jdbc-7.12.0.jar;
/

and of course I create the connection with

CREATE OR REPLACE CONNECTION ELASTIC_CONNECTION TO 'jdbc:es://http://192.168.178.29:9205/';;

But when I try to create the VS with

CREATE VIRTUAL SCHEMA VIRTUAL_SCHEMA_TEST USING ELASTIC.ES_ADAPTER WITH CONNECTION_NAME = 'ELASTIC_CONNECTION' SCHEMA_NAME = 'ELASTIC';

I receive the above error. Here is the full stack trace:

SQL-Fehler [22002]: VM error:
java.lang.IllegalArgumentException: Unknown Virtual Schema Adapter "UNKNOWN" requested. Currently registered Virtual Schema Adapter factories: "ES"
Stack trace:
com.exasol.adapter.AdapterRegistry.getAdapterForName(AdapterRegistry.java:76)
com.exasol.adapter.RequestDispatcher.findResponsibleAdapter(RequestDispatcher.java:62)
com.exasol.adapter.RequestDispatcher.executeAdapterCall(RequestDispatcher.java:51)
com.exasol.adapter.RequestDispatcher.adapterCall(RequestDispatcher.java:41)
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.base/java.lang.reflect.Method.invoke(Method.java:566)
com.exasol.ExaWrapper.runSingleCall(ExaWrapper.java:95) (Session: 1696051372399491389)

Did I skip a step or is it a bug? I've used the community edition 6.2.4. I would really like to work with this feature. Thanx in advance.

ElasticSearchSQL supports expressions in the GROUP BY clause.

The problem is that the default visitor rewrites the binary operators wrapping the expression in brackets, so a query like

SELECT "a" + 1 FROM ESVS."table" GROUP BY "a" + 1

is rewritten to

SELECT "a" + 1 FROM ESVS."table" GROUP BY ( "a" + 1 )

and this fails in the ES database as the brackets are not supported.

Add integration tests for Aggregate Functions

ElasticSearch supports CHAR but not CHR.

We should add this to the adapter.

Update dependencies to use enhanced Datatype Detection For Result Sets from virtual-schemas-common-jdbc

Summary

Allocation of Resources Without Limits or Throttling vulnerability in Apache Commons Compress.This issue affects Apache Commons Compress: from 1.21 before 1.26.

Users are recommended to upgrade to version 1.26, which fixes the issue.

CVE: CVE-2024-26308
CWE: CWE-770

References

• https://ossindex.sonatype.org/vulnerability/CVE-2024-26308?component-type=maven&component-name=org.apache.commons%2Fcommons-compress&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
• http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2024-26308
• https://lists.apache.org/thread/ch5yo2d21p7vlqrhll9b17otbyq4npfg
• https://www.openwall.com/lists/oss-security/2024/02/19/2

At the moment, to fully set-up the virtual schema there are several steps to follow, that might be error prone as they must be done manually in a specific order.

We should automate this.

Perhaps we can take a look at the Java self-contained app feature, and use the bucketfs-java library.

Since 2023-06-02 for version 8.18.1 of Exasol database a Docker image is available on Dockerhub.

The current ticket therefore requests to update the integration tests of VSES to use version 8.18.1 as latest default version.

Please note sibling-tickets for all JDBC-based virtual schemas.

Virtual-schema-shared-integration-tests provide generic integration tests.
Refactor this repository to use that library.

Exasol 7.1.17 returns wrong values for EXTRACT SECOND FROM:

SELECT EXTRACT(SECOND FROM CAST('2018-02-19 10:23:27' AS TIMESTAMP))
FROM VIRTUAL_SCHEMA."index";

This returns 0.027 instead of the expected value 27.

Also see integration test testExtractSecond():

assertExtract("SECOND").withValues("2018-02-19T10:23:27Z").withResult(27).verify()

This is a bug in Exasol 7.1.17.

Currently the expected value is set to 0.027 to make the test pass. Once a new DB release with a fix is available we need to upgrade and adapt the test case.

See also #60 and #64.

Elastic search supports natural logarithm function but as LOG.

We should add a translation from LN to LOG in order to support it.

The CI build runs with two Exasol versions. To simplify maintenance we should think about if this is required and remove the matrix build if possible.

Rename error codes from VS-ES to VSES

This project uses log4j versions potentially affected by CVE-2021-44228:

[INFO]    org.apache.logging.log4j:log4j-api:jar:2.11.1:test

Dependency tree:

[INFO] +- org.elasticsearch.client:elasticsearch-rest-high-level-client:jar:7.15.0:test
[INFO] |  +- org.elasticsearch:elasticsearch:jar:7.15.0:test
[INFO] |  |  +- org.elasticsearch:elasticsearch-core:jar:7.15.0:test
[INFO] |  |  +- org.elasticsearch:elasticsearch-secure-sm:jar:7.15.0:test
[INFO] |  |  +- org.elasticsearch:elasticsearch-x-content:jar:7.15.0:test
[INFO] |  |  |  +- org.yaml:snakeyaml:jar:1.26:test
[INFO] |  |  |  +- com.fasterxml.jackson.core:jackson-core:jar:2.10.4:test
[INFO] |  |  |  +- com.fasterxml.jackson.dataformat:jackson-dataformat-smile:jar:2.10.4:test
[INFO] |  |  |  +- com.fasterxml.jackson.dataformat:jackson-dataformat-yaml:jar:2.10.4:test
[INFO] |  |  |  \- com.fasterxml.jackson.dataformat:jackson-dataformat-cbor:jar:2.10.4:test
[INFO] |  |  +- org.elasticsearch:elasticsearch-geo:jar:7.15.0:test
[INFO] |  |  +- org.apache.lucene:lucene-core:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-analyzers-common:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-backward-codecs:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-grouping:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-highlighter:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-join:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-memory:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-misc:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-queries:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-queryparser:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-sandbox:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-spatial-extras:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-spatial3d:jar:8.9.0:test
[INFO] |  |  +- org.apache.lucene:lucene-suggest:jar:8.9.0:test
[INFO] |  |  +- org.elasticsearch:elasticsearch-cli:jar:7.15.0:test
[INFO] |  |  |  \- net.sf.jopt-simple:jopt-simple:jar:5.0.2:test
[INFO] |  |  +- com.carrotsearch:hppc:jar:0.8.1:test
[INFO] |  |  +- org.lz4:lz4-java:jar:1.8.0:test
[INFO] |  |  +- joda-time:joda-time:jar:2.10.10:test
[INFO] |  |  +- com.tdunning:t-digest:jar:3.2:test
[INFO] |  |  +- org.hdrhistogram:HdrHistogram:jar:2.1.9:test
[INFO] |  |  +- org.apache.logging.log4j:log4j-api:jar:2.11.1:test    <=====
[INFO] |  |  +- org.elasticsearch:jna:jar:5.7.0-1:test
[INFO] |  |  \- org.elasticsearch:elasticsearch-plugin-classloader:jar:7.15.0:test

Integration tests currently use Elasticsearch 7.17.5. Upgrading server and JDBC driver to the latest 8 release (currently 8.3.3), integration tests start failing because the JDBC driver returns slightly different values than before.

Until this is implemented we recommend using Elasticsearch version 7.17.5 for both the server and the JDBC driver.

ElasticSearch supports DIV but as the / operator.

We should add this translation.

https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/div.htm#DIV
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-operators-math.html#sql-operators-divide

ElasticSearch supports LOCATE, but not with a negative position as Exasol does.

Perhaps we can write an adaptation for it.

https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/locate.htm#Locate

https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-string.html#sql-functions-string-locate

ElasticSearch supports SECOND but only the 1 parameter version, which is the default one used in Exasol.

It does not support the 2 parameter version.

https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/second.htm#SECOND
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html#sql-functions-datetime-second