Merge: Bad Error Message in case of invalid syntax

Details

Detail name Value
Changelog Number 11714
Type Bug
Status Open
Affected Versions Exasol 6.2.0, Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.29.0, Exasol 2025.1.0, Exasol 2025.2.0

Background

The MERGE command allows updating multiple columns within the update set clause.
The columns have to be separated by ','.

MERGE INTO a 
USING (SELECT * FROM b) b 
ON (a.a1 = b.b1 AND a.a2 = b.b2)
WHEN MATCHED THEN 
UPDATE SET 
    a.a3 = b.b3, 
    a.a4 = b.b4
;

Description

If a MERGE statement contains invalid syntax (boolean operators) in the UPDATE SET clause, then the error message is either missing or misleading in the folllowing cases:

  • If the table referenced in the USING clause is empty:
  • If the table references in the USING clause is NOT empty:
  • the MERGE statement will not throw any error message
  • the MERGE statement will throw a misleading error message 
[Code: 0, SQL State: 42000]  operands in AND must be a boolean expression

 

Preparation:

CREATE TABLE a
(
    a1 dec(12), 
    a2 dec(15),
    a3 dec(12),
    a4 dec(15)
);
CREATE TABLE b
(
    b1 dec(12),
    b2 dec(15),
    b3 dec(12),
    b4 dec(15)
);

INSERT INTO a VALUES (1, 1, 12, 13);

-- Run the below INSERT statement to reproduce the misleading error message
-- INSERT INTO b VALUES (1, 1, 15, 16);

Example:

MERGE INTO a 
USING (select * from b) b 
ON (a.a1 = b.b1 AND a.a2 = b.b2)
WHEN MATCHED THEN 
UPDATE SET 
    a.a3 = b.b3 OR
    a.a4 = b.b4
;

In both cases, replacing the ',' character with boolean expressions (eg. OR, AND) in the UPDATE SET clause is a syntax error and an appropriate error message should be thrown.

Workaround

There is no workaround.

Fix

The described syntax error is detected in these cases.
In addition, a syntax error message is thrown indicating that an incorrect separator character was used in the UPDATE SET clause.