Issue Details (XML | Word | Printable)

Key: CORE-6280
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Mark Rotteveel
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

MERGE statement loses parameters in WHEN (NOT) MATCHED clause that will never be matched, crashes server in some situations

Created: 09/Apr/20 06:49 PM   Updated: 20/Apr/20 10:39 AM
Component/s: Engine
Affects Version/s: 4.0 Beta 1, 3.0.5
Fix Version/s: 4.0 Beta 2, 3.0.6

Environment:
Firebird-3.0.5.33220-0_x64 on Windows 10
Firebird-4.0.0.1876-0_x64 on Windows 10
Issue Links:
Duplicate
 

QA Status: Done successfully


 Description  « Hide
In a MERGE statement with a WHEN (NOT) MATCHED clause that will never be matched, the parameters defined in that clause are lost. Depending on the exact statement, preparing the statement can crash the server.

Using table:

create table t (i int not null primary key, j int);

For example:

EXECUTE block
AS
BEGIN
EXECUTE STATEMENT ('merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete') (2, 1);
END

will result in error "Input parameters mismatch". Preparing this MERGE statement (using Jaybird), will result in a statement described with a single parameter (see also JDBC-621).

Replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 2 parameters).

Adding another clause (eg "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)") and executing with 2 parameters yields an error "SQLDA error; Wrong number of parameters (expected 3, got 2)", but using three parameters will again yield "Input parameters mismatch". Attempts to prepare that MERGE statement with Jaybird will result in a crash of Firebird.

Here again, replacing "when matched then update set j = ?" with the equivalent "when matched and 1 = 1 then update set j = ?", will work (and in Jaybird correctly describe with 3 parameters).

It looks like Firebird detects that the second WHEN MATCHED clause will never be used, and doesn't allocate a parameter for it (or at least not correctly).

Example (Java 11) program to let Jaybird 4 crash the Firebird 3.0.5 server:

public class MergeCrash {
    public static void main(String[] args) throws SQLException {
        try (var connection = DriverManager.getConnection("jdbc:firebird://localhost/C:/Data/Db/FB3TESTDATABASE.FDB",
                "sysdba", "masterkey");
             var pstmt = connection.prepareStatement("merge into t using (select 1 x from rdb$database) on 1 = 1 \n" +
                     "when matched then update set j = ? \n" +
                     "when matched and i = ? then delete \n" +
                     "WHEN NOT MATCHED THEN INSERT (i, j) values (1, ?)")) {
            var pmd = pstmt.getParameterMetaData();
            System.out.println(pmd.getParameterCount());
        }
    }
}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 09/Apr/20 09:37 PM
When you say about potential to crash, do you have any evidence or it's just speculation?

Mark Rotteveel added a comment - 10/Apr/20 06:59 AM
I was able to crash the server repeatedly by executing above Java program against a database with the example table t. I don't have a dump at this time, I will see if I can produce a dump this weekend.

Mark Rotteveel added a comment - 11/Apr/20 09:35 AM
Full dump with Firebird-3.0.5.33220-0_x64_pdb debug build: https://www.dropbox.com/s/aijoghosu1m958u/firebird.exe.11728.7z?dl=0

Adriano dos Santos Fernandes added a comment - 14/Apr/20 04:24 PM
Mark, please test fix done in master.

Adriano dos Santos Fernandes added a comment - 15/Apr/20 11:38 AM
Pavel, you can have a crash in isql if you previously run "set sqlda_display on;".

Pavel Zotov added a comment - 15/Apr/20 12:57 PM
> you can have a crash in isql if you previously run "set sqlda_display on;".

I've tried - no success.
========
recreate table t (i int not null primary key, j int);
commit;

set sqlda_display on;
set term ^;
execute block as
    declare s varchar(4000);
begin
    s = '
            merge into t
            using (select 1 x from rdb$database) on 1 = 1
            when matched then
                    update set j = ?
            when matched and i = ? then
                    delete
            when not matched then
                    insert (i, j) values (1, ?)
    ';
    execute statement ( s ) (2, 1);
end
^

INPUT message field count: 0

OUTPUT message field count: 0
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-Wrong number of parameters (expected 3, got 2)
-At block line: 14, col: 5
After line 7 in file c6280.sql
set term ^;

show version;
ISQL Version: WI-T4.0.0.1871 Firebird 4.0 Beta 1
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-T4.0.0.1871 Firebird 4.0 Beta 1"
Firebird/Windows/AMD/Intel/x64 (remote server), version "WI-T4.0.0.1871 Firebird 4.0 Beta 1/tcp (Image-PC1)/P16:C"
Firebird/Windows/AMD/Intel/x64 (remote interface), version "WI-T4.0.0.1871 Firebird 4.0 Beta 1/tcp (Image-PC1)/P16:C"
on disk structure version 13.0
======

Mark Rotteveel added a comment - 15/Apr/20 01:08 PM - edited
I'm reopening this issue because it will also need to be backported to 3.0.6.

Tested it on the 4.0.0.1881 x64 build from Appveyor and verified it works, and I can execute the merge statement, and it behaves as expected.


Mark Rotteveel added a comment - 15/Apr/20 01:21 PM
@Pavel you can reproduce it in ISQL using:

set sqlda_display on;
merge into t using (select 1 x from rdb$database) on 1 = 1
when matched then update set j = ?
when matched and i = ? then delete
when not matched then insert (i, j) values (1, ?);

C:\DevSoft\Firebird\Firebird-3.0.5.33220-0_x64>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect 'localhost:c:/data/db/fb3testdatabase.fdb' user sysdba password 'masterkey';
Database: 'localhost:c:/data/db/fb3testdatabase.fdb', User: SYSDBA
SQL> set sqlda_display on;
SQL> merge into t using (select 1 x from rdb$database) on 1 = 1
CON> when matched then update set j = ?
CON> when matched and i = ? then delete
CON> when not matched then insert (i, j) values (1, ?);
Statement failed, SQLSTATE = 08006
Error reading data from the connection.
SQL>

Pavel Zotov added a comment - 15/Apr/20 01:32 PM
Yes, this way really produces crash. Update state will be corrected now.

Mark Rotteveel added a comment - 18/Apr/20 01:03 PM
Tested with 3.0.6.33288 works ok.

@Pavel: I can't close as I don't have those rights.