There is a UNION between two queries. One of them is a simple lookup, it returns a row only if it exists.
Another one is a query from a data change delta table from the results of inner MERGE command that tries to merge a temporary table with the one row (VALUES ?) into the target table. Because only the WHEN NOT MATCHED clause was used, it will insert the row only if it was not found. This row will be also returned. If such row already exists it will be matched and result will be empty. https://h2database.com/html/grammar.html#data_change_delta_table https://h2database.com/html/commands.html#merge_using So either first or second query return a row. This command is not thread-safe, if row will be inserted concurrently by another transaction, the row will not be returned. You can swap these two queries for more safety. (WITH S(NAME) AS (VALUES ?) SELECT id FROM FINAL TABLE( MERGE INTO QuestionTypes T USING S ON T.name = S.NAME WHEN NOT MATCHED THEN INSERT (name) VALUES (S.NAME) ) UNION SELECT id FROM QuestionTypes JOIN S ON QuestionTypes.name = S.NAME ) -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a9712c86-d299-41d9-98bf-38aaa6951122%40googlegroups.com.
