WHEN NOT MATCHED BY SOURCE - does not work with a direct table as source

[nLeonardt95]

“WHEN NOT MATCHED BY SOURCE” was introduced with ticket #6681.
This has worked quite well so far.
However, I have now noticed that “WHEN NOT MATCHED BY SOURCE” behaves differently if the source is a stored procedure than if you specify derived-table as the source.

For “WHEN NOT MATCHED BY TARGET”, stored procedure and derived-table behave in the same way.

Here's an example, it may not make much sense, but you can see the difference in a nutshell.

Test-Table

CREATE GENERATOR TEST_ID_GEN;

CREATE TABLE TEST (
    ID        INTEGER NOT NULL,
    TYP       INTEGER,
    CATEGORY  INTEGER
);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);

SET TERM ^ ;
CREATE OR ALTER TRIGGER TEST_BI0 FOR TEST
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
  begin
    new.id = gen_id( test_id_gen, 1);
  end
end^

SET TERM ; ^

Test-Data

INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (1, 1, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (2, 1, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (3, 1, 20);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (4, 1, 20);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (5, 1, 40);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (6, 2, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (7, 2, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (8, 2, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (9, 2, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (10, 2, 10);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (11, 2, 20);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (12, 2, 20);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (13, 2, 30);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (14, 2, 60);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (15, 2, 60);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (16, 2, 60);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (17, 2, 30);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (18, 2, 30);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (19, 2, 30);
INSERT INTO TEST (ID, TYP, CATEGORY) VALUES (20, 2, 30);

Test-Statement

execute block (
    X_SOURCE_TYP type of column TEST.TYP = :SOURCE_TYP,
    X_TARGET_TYP type of column TEST.TYP = :TARGET_TYP)
as
    declare procedure SOURCEDATA
    returns (
        ID       type of column TEST.ID,
        TYP      type of column TEST.TYP,
        CATEGORY type of column TEST.CATEGORY)
    as
    begin
      for select T.ID,
                 T.TYP,
                 T.CATEGORY
      from TEST T
      where T.TYP = :X_SOURCE_TYP
      into :ID,
           :TYP,
           :CATEGORY
      do
      begin
        suspend;
      end
    end

    declare variable WITHSTOREDPROCEDURE boolean;
begin
  --  :WITHSTOREDPROCEDURE = true; -- "when not matched by source" does not work
  :WITHSTOREDPROCEDURE = false; -- works as expected
  if ( :WITHSTOREDPROCEDURE) then
  begin
    -- "when not matched by source" does not work
    merge into TEST T
    using SOURCEDATA S
    on T.TYP = :X_TARGET_TYP and
       T.CATEGORY = S.CATEGORY
    when not matched by target then
        insert ( TYP,
                CATEGORY)
        values ( :X_TARGET_TYP,
                S.CATEGORY);

    merge into TEST T
    using SOURCEDATA S
    on T.CATEGORY = S.CATEGORY
    when not matched by source and T.TYP = :X_TARGET_TYP then delete;
  end
  else
  begin
    -- works as expected
    merge into TEST T
    using ( select T.ID,
                   T.TYP,
                   T.CATEGORY
            from TEST T
            where T.TYP = :X_SOURCE_TYP) S
    on T.TYP = :X_TARGET_TYP and
       T.CATEGORY = S.CATEGORY
    when not matched by target then
        insert ( TYP,
                CATEGORY)
        values ( :X_TARGET_TYP,
                S.CATEGORY);

    merge into TEST T
    using ( select T.ID,
                   T.TYP,
                   T.CATEGORY
            from TEST T
            where T.TYP = :X_SOURCE_TYP) S
    on T.CATEGORY = S.CATEGORY
    when not matched by source and T.TYP = :X_TARGET_TYP then delete;
  end
end

[nLeonardt95]

For your information, I have tested it with Firebird 5.0.1.1469

[asfernandes]

What means "when not matched by source" does not work? Does not work how?
Actually your test do not work at all trying to create duplicate primary key values.

[asfernandes]

I see there is a problem, but not related with stored procedures.
The problem I see is with USING <table>, while USING (select ...) does work.

[nLeonardt95]

What means "when not matched by source" does not work? Does not work how? Actually your test do not work at all trying to create duplicate primary key values.

The problem with USING <stored procedure>is that it does not make a delete, while USING (select ...) execute the delete.

[asfernandes]

Please test fix in next v5 snapshot (or an artifact from https://github.com/FirebirdSQL/firebird/actions/runs/10428291197 when available).

[pavel-zotov]

Have a question about final result in one of cases. Sent report to Adriano, 18.08.2024 11:40. Waiting for reply.

[pavel-zotov]

Test currently checks only 5.0.2.
FB 6.x not yet has fix, waiting for front-porting.

[nLeonardt95]

Please test fix in next v5 snapshot (or an artifact from https://github.com/FirebirdSQL/firebird/actions/runs/10428291197 when available).

Looks good at first, the results are the same with USING (select...) and USING <stored procedure>.
It is just significantly slower with USING <stored procedure>.

My goal with the internal procedure is simply that I can harmonize the select-statement because I need it twice within the execute block.

Although my stored procedure returns the same as the select-statement.
In the example, you will not notice this.
Unfortunately, my specific use case is a little too complex to illustrate in an example.

Here are the results - maybe they will help.

USING <stored procedure>

Query
------------------------------------------------
execute block (
    X_QUELLE_WIRKSTOFF type of column WIRK_TIERART.NR_INHALTSSTOFF = :QUELLE_WIRKSTOFF,
    X_ZIEL_WIRKSTOFF   type of column WIRK_TIERART.NR_INHALTSSTOFF = :ZIEL_WIRKSTOFF,
    X_QUELLE_WERKNR    type of column WIRK_TIERART.WERKNR = :QUELLE_WERKNR,
    X_ZIEL_WERKNR      type of column WIRK_TIERART.WERKNR = :ZIEL_WERKNR,
    X_QUELLE_MANDANT   type of column WIRK_TIERART.MANDANT = :QUELLE_MANDANT,
    X_ZIEL_MANDANT     type of column WIRK_TIERART.MANDANT = :ZIEL_MANDANT)
as

declare procedure QUELLEDATEN
    returns (
        NR_TIERART      type of column WIRK_TIERART.NR_TIERART,
        NR_PRODUKTTYP   type of column WIRK_TIERART.NR_PRODUKTTYP,
        ANZEIGE         type of column WIRK_TIERART.ANZEIGE,
        DRUCK           type of column WIRK_TIERART.DRUCK,
        NR_INHALTSSTOFF type of column WIRK_TIERART.NR_INHALTSSTOFF)
    as
    begin
      for select WT.NR_TIERART,
                 WT.NR_PRODUKTTYP,
                 WT.ANZEIGE,
                 WT.DRUCK,
                 WT.NR_INHALTSSTOFF
      from WIRK_TIERART WT
      where WT.WERKNR = :X_QUELLE_WERKNR and
            WT.MANDANT = :X_QUELLE_MANDANT and
            WT.NR_INHALTSSTOFF = :X_QUELLE_WIRKSTOFF
      into :NR_TIERART,
           :NR_PRODUKTTYP,
           :ANZEIGE,
           :DRUCK,
           :NR_INHALTSSTOFF
      do
      begin
        suspend;
      end
    end


begin
  merge into WIRK_TIERART ZIEL
  using QUELLEDATEN QUELLE
  on ZIEL.WERKNR = :X_ZIEL_WERKNR and
     ZIEL.MANDANT = :X_ZIEL_MANDANT and
     ZIEL.NR_TIERART = QUELLE.NR_TIERART and
     ZIEL.NR_PRODUKTTYP = QUELLE.NR_PRODUKTTYP and
     ZIEL.NR_INHALTSSTOFF = :X_ZIEL_WIRKSTOFF
  when not matched by source and ZIEL.WERKNR = :X_ZIEL_WERKNR and ZIEL.MANDANT = :X_ZIEL_MANDANT and ZIEL.NR_INHALTSSTOFF = :X_QUELLE_WIRKSTOFF then delete;

  merge into WIRK_TIERART ZIEL
  using QUELLEDATEN QUELLE
  on ZIEL.WERKNR = :X_ZIEL_WERKNR and
     ZIEL.MANDANT = :X_ZIEL_MANDANT and
     ZIEL.NR_TIERART = QUELLE.NR_TIERART and
     ZIEL.NR_PRODUKTTYP = QUELLE.NR_PRODUKTTYP and
     ZIEL.NR_INHALTSSTOFF = :X_ZIEL_WIRKSTOFF

  when matched and ( ( coalesce( ZIEL.ANZEIGE, false) <> coalesce( QUELLE.ANZEIGE, false)) or ( coalesce( ZIEL.DRUCK, false) <> coalesce( QUELLE.DRUCK, false))) then
      update set ZIEL.ANZEIGE = QUELLE.ANZEIGE,
                 ZIEL.DRUCK = QUELLE.DRUCK

  when not matched by target then
      insert ( WERKNR,
              MANDANT,
              NR_INHALTSSTOFF,
              NR_TIERART,
              NR_PRODUKTTYP,
              ANZEIGE,
              DRUCK)
      values ( :X_ZIEL_WERKNR,
              :X_ZIEL_MANDANT,
              :X_ZIEL_WIRKSTOFF,
              QUELLE.NR_TIERART,
              QUELLE.NR_PRODUKTTYP,
              QUELLE.ANZEIGE,
              QUELLE.DRUCK);
end

Plan
------------------------------------------------
-- line 41, column 3
PLAN JOIN (ZIEL INDEX (WIRK_TIERART_FK3, WIRK_TIERART_PK), QUELLE NATURAL)
-- line 50, column 3
PLAN JOIN (QUELLE NATURAL, ZIEL INDEX (WIRK_TIERART_PK))

Query Time
------------------------------------------------
Prepare       : 0,00 ms
Execute       : 57.422,00 ms
Avg fetch time: 0,00 ms

Memory
------------------------------------------------
Current: 4.977.940.144
Max    : 4.977.943.568
Buffers: 150.000

Operations
------------------------------------------------
Read   : 0
Writes : 0
Fetches: 3.202.246
Marks  : 86


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUKTTYPDATEN                |         0 |        11 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|TIERARTDATEN                   |         0 |        11 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|WIRKSTOFF                      |         0 |        11 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|WIRK_TIERART                   |         0 |   1710826 |           0 |      12 |       0 |      11 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

USING (select...)

Query
------------------------------------------------
execute block (
    X_QUELLE_WIRKSTOFF type of column WIRK_TIERART.NR_INHALTSSTOFF = :QUELLE_WIRKSTOFF,
    X_ZIEL_WIRKSTOFF   type of column WIRK_TIERART.NR_INHALTSSTOFF = :ZIEL_WIRKSTOFF,
    X_QUELLE_WERKNR    type of column WIRK_TIERART.WERKNR = :QUELLE_WERKNR,
    X_ZIEL_WERKNR      type of column WIRK_TIERART.WERKNR = :ZIEL_WERKNR,
    X_QUELLE_MANDANT   type of column WIRK_TIERART.MANDANT = :QUELLE_MANDANT,
    X_ZIEL_MANDANT     type of column WIRK_TIERART.MANDANT = :ZIEL_MANDANT)
as
begin
  merge into WIRK_TIERART ZIEL
  using ( select WT.NR_TIERART,
                 WT.NR_PRODUKTTYP,
                 WT.ANZEIGE,
                 WT.DRUCK,
                 WT.NR_INHALTSSTOFF
          from WIRK_TIERART WT
          where WT.WERKNR = :X_QUELLE_WERKNR and
                WT.MANDANT = :X_QUELLE_MANDANT and
                WT.NR_INHALTSSTOFF = :X_QUELLE_WIRKSTOFF) QUELLE
  on ZIEL.WERKNR = :X_ZIEL_WERKNR and
     ZIEL.MANDANT = :X_ZIEL_MANDANT and
     ZIEL.NR_TIERART = QUELLE.NR_TIERART and
     ZIEL.NR_PRODUKTTYP = QUELLE.NR_PRODUKTTYP and
     ZIEL.NR_INHALTSSTOFF = :X_ZIEL_WIRKSTOFF
  when not matched by source and ZIEL.WERKNR = :X_ZIEL_WERKNR and ZIEL.MANDANT = :X_ZIEL_MANDANT and ZIEL.NR_INHALTSSTOFF = :X_QUELLE_WIRKSTOFF then delete;

  merge into WIRK_TIERART ZIEL
  using ( select WT.NR_TIERART,
                 WT.NR_PRODUKTTYP,
                 WT.ANZEIGE,
                 WT.DRUCK,
                 WT.NR_INHALTSSTOFF
          from WIRK_TIERART WT
          where WT.WERKNR = :X_QUELLE_WERKNR and
                WT.MANDANT = :X_QUELLE_MANDANT and
                WT.NR_INHALTSSTOFF = :X_QUELLE_WIRKSTOFF) QUELLE
  on ZIEL.WERKNR = :X_ZIEL_WERKNR and
     ZIEL.MANDANT = :X_ZIEL_MANDANT and
     ZIEL.NR_TIERART = QUELLE.NR_TIERART and
     ZIEL.NR_PRODUKTTYP = QUELLE.NR_PRODUKTTYP and
     ZIEL.NR_INHALTSSTOFF = :X_ZIEL_WIRKSTOFF

  when matched and ( ( coalesce( ZIEL.ANZEIGE, false) <> coalesce( QUELLE.ANZEIGE, false)) or ( coalesce( ZIEL.DRUCK, false) <> coalesce( QUELLE.DRUCK, false))) then
      update set ZIEL.ANZEIGE = QUELLE.ANZEIGE,
                 ZIEL.DRUCK = QUELLE.DRUCK

  when not matched by target then
      insert ( WERKNR,
              MANDANT,
              NR_INHALTSSTOFF,
              NR_TIERART,
              NR_PRODUKTTYP,
              ANZEIGE,
              DRUCK)
      values ( :X_ZIEL_WERKNR,
              :X_ZIEL_MANDANT,
              :X_ZIEL_WIRKSTOFF,
              QUELLE.NR_TIERART,
              QUELLE.NR_PRODUKTTYP,
              QUELLE.ANZEIGE,
              QUELLE.DRUCK);
end

Plan
------------------------------------------------
-- line 10, column 3
PLAN JOIN (ZIEL INDEX (WIRK_TIERART_FK3, WIRK_TIERART_PK), QUELLE WT INDEX (WIRK_TIERART_PK))
-- line 27, column 3
PLAN JOIN (QUELLE WT INDEX (WIRK_TIERART_FK3, WIRK_TIERART_PK), ZIEL INDEX (WIRK_TIERART_PK))

Query Time
------------------------------------------------
Prepare       : 0,00 ms
Execute       : 110,00 ms
Avg fetch time: 0,00 ms

Memory
------------------------------------------------
Current: 4.977.922.480
Max    : 4.977.943.568
Buffers: 150.000

Operations
------------------------------------------------
Read   : 0
Writes : 0
Fetches: 22.044
Marks  : 86


Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUKTTYPDATEN                |         0 |        11 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|TIERARTDATEN                   |         0 |        11 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|WIRKSTOFF                      |         0 |        11 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|WIRK_TIERART                   |         0 |      5215 |           0 |      12 |       0 |      11 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

[nLeonardt95]

I need the select twice because with a merge into where when not matched by target and when not matched by source is used, it is significantly slower because of the full join that is then made.
However, if I separate this into one merge into ... when not matched by target and one merge into ... when not matched by source then it is significantly faster.
Or is there another way to use when not matched by target and when not matched by source together more efficiently?