Insert from subquery with VS and GROUP BY fails

Detail name	Value
Changelog Number	17252
Type	Bug
Status	Resolved
Affected Versions	Exasol 7.1.0, Exasol 8.0.0
Fix Versions	Exasol 7.1.21, Exasol 8.21.0
Resolution Date	2023-06-16

Description

An INSERT statement does not insert data if the following conditions are met

• Inserting data from a subquery that contains a virtual schema.
• The subquery contains a GROUP BY.
• The database pushes the complete subquery down to the remote database.

Preparation

CREATE SCHEMA SCHEMA_NATIVE;
CREATE OR REPLACE TABLE t(id INT);
INSERT INTO t VALUES 1,2,3;
CREATE OR REPLACE TABLE SCHEMA_NATIVE.ot(x CHAR(2), b CHAR(3));


--/
CREATE OR REPLACE JAVA ADAPTER SCRIPT JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jvmoption -Xms64m -Xmx64m;
%jar /buckets/testing/jdbc-adapter/virtualschema-jdbc-adapter.jar;
/

CREATE OR REPLACE CONNECTION C1 TO ...;

CREATE VIRTUAL SCHEMA VS_SCHEMA USING SCHEMA_NATIVE.JDBC_ADAPTER WITH
CONNECTION_NAME='C1' SCHEMA_NAME='SCHEMA_NATIVE'; 

Example

-- Does not insert data:
INSERT INTO SCHEMA_NATIVE.ot(x) SELECT MAX(id) FROM VS_SCHEMA.t GROUP BY id;
-- Does not insert data:
INSERT INTO SCHEMA_NATIVE.ot(x) SELECT DISTINCT id FROM VS_SCHEMA.t;
-- Does not insert data:
INSERT INTO SCHEMA_NATIVE.ot(x) SELECT MAX(id) FROM VS_SCHEMA.t;

 

Workaround

Put the subquery with the GROUP BY into another subquery that does not contain a GROUP BY.

INSERT INTO SCHEMA_NATIVE.ot(x) SELECT * FROM (SELECT MAX(id) FROM VS_SCHEMA.t GROUP BY id);

INSERT INTO SCHEMA_NATIVE.ot(x) SELECT * FROM (SELECT DISTINCT id FROM VS_SCHEMA.t);

INSERT INTO SCHEMA_NATIVE.ot(x) SELECT * FROM (SELECT MAX(id) FROM VS_SCHEMA.t);

Fix

Those queries insert data as expected.