My aim is to perform an upsert. Originally, my query was just doing a MERGE INTO with no UNION. Unfortunately Ignite if a row already exists, Ignite DOES NOT merge, it replaces the row. So any columns from the old row that are not included in the new MERGE will be set to NULL at the end of the operation. Looking around I found http://apache-ignite-users.70518.x6.nabble.com/INSERT-and-MERGE-statements-td28685.html which suggests this is intended behaviour and not a bug.
So I thought one way to do this with SQL is by doing a MERGE SELECT where the first SELECT gets the existing row and any columns not being updated are taken from the existing row. If no row matches the first select then nothing will be inserted (that's why I need the union) so the second SELECT is a list of literals of the columns currently being modified. In effect I'm doing an IF first SELECT take its data else use these literals. Ignite also doesn't support the MERGE USING syntax in H2 http://www.h2database.com/html/commands.html#merge_using so I thought this might work. Using the MERGE SELECT UNION I can't get Ignite to parse the second select IFF the fields are placeholders i.e. ? In > *MERGE* *INTO* hypi_store_App(hypi_id,hypi_instanceId,hypi_created, > hypi_updated,hypi_createdBy,hypi_instance,hypi_app,hypi_release, > hypi_publisherRealm,hypi_publisherApp,hypi_publisherRelease,hypi_impl)( > *SELECT* ?,?,(IFNULL(*SELECT* hypi_created *FROM* hypi_store_App *WHERE* > hypi_instanceId = ? *AND* hypi_id = ?, > *CURRENT_TIMESTAMP*())),?,?,?,?,?,?,?,?,? > *FROM* hypi_store_App r *WHERE* hypi_id = ? *AND* hypi_instanceId = ? > > *UNION**SELECT* 'a','a','a','a','a','a','a','a','a','a','a','a' > -- SELECT ?,?,?,?,?,?,?,?,?,?,?,? > -- SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? > ); The query is parsed successfully if I use literals as in *SELECT* 'a','a', 'a','a','a','a','a','a','a','a','a','a' but SELECT ?,?,?,?,?,?,?,?,?,?,?,? will fail, same for the longer version above. The error is Failed to parse query. Unknown data type: "?, ?" as in SQL Error [1001] [42000]: Failed to parse query. Unknown data type: "?, ?"; > SQL statement: > MERGE INTO > hypi_store_App(hypi_id,hypi_instanceId,hypi_created,hypi_updated,hypi_createdBy,hypi_instance,hypi_app,hypi_release,hypi_publisherRealm,hypi_publisherApp,hypi_publisherRelease,hypi_impl)( > SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, > CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? FROM hypi_store_App r WHERE hypi_id > = ? AND hypi_instanceId = ? > UNION > -- SELECT 'a','a','a','a','a','a','a','a','a','a','a','a' > SELECT ?,?,?,?,?,?,?,?,?,?,?,? > --SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? > ) [50004-197] the full stack trace from the server is below. Any suggestions? Is my query really not valid? Or is there another way to achieve a real merge instead of a replace without making multiple queries from the client? 2020-04-22 22:37:04.764 ERROR 52149 --- [ctor-#256%hypi%] > o.a.i.i.p.odbc.jdbc.JdbcRequestHandler : Failed to execute SQL query > [reqId=53, req=JdbcQueryExecuteRequest [schemaName=PUBLIC, pageSize=1024, > maxRows=200, sqlQry=MERGE INTO > hypi_store_App(hypi_id,hypi_instanceId,hypi_created,hypi_updated,hypi_createdBy,hypi_instance,hypi_app,hypi_release,hypi_publisherRealm,hypi_publisherApp,hypi_publisherRelease,hypi_impl)( > SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, > CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? FROM hypi_store_App r WHERE hypi_id > = ? AND hypi_instanceId = ? > UNION > -- SELECT 'a','a','a','a','a','a','a','a','a','a','a','a' > SELECT ?,?,?,?,?,?,?,?,?,?,?,? > --SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? > ), args=Object[] [], stmtType=ANY_STATEMENT_TYPE, autoCommit=true, > partResReq=false, super=JdbcRequest [type=2, reqId=53]]] > > org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to > parse query. Unknown data type: "?, ?"; SQL statement: > MERGE INTO > hypi_store_App(hypi_id,hypi_instanceId,hypi_created,hypi_updated,hypi_createdBy,hypi_instance,hypi_app,hypi_release,hypi_publisherRealm,hypi_publisherApp,hypi_publisherRelease,hypi_impl)( > SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, > CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? FROM hypi_store_App r WHERE hypi_id > = ? AND hypi_instanceId = ? > UNION > -- SELECT 'a','a','a','a','a','a','a','a','a','a','a','a' > SELECT ?,?,?,?,?,?,?,?,?,?,?,? > --SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? > ) [50004-197] > at > org.apache.ignite.internal.processors.query.h2.QueryParser.parseH2(QueryParser.java:582) > at > org.apache.ignite.internal.processors.query.h2.QueryParser.parse0(QueryParser.java:210) > at > org.apache.ignite.internal.processors.query.h2.QueryParser.parse(QueryParser.java:131) > at > org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1060) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402) > at > org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396) > at > org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2354) > at > org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:615) > at > org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.doHandle(JdbcRequestHandler.java:310) > at > org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:247) > at > org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:195) > at > org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:49) > at > org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279) > at > org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109) > at > org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97) > at > org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:120) > at > org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70) > at > java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) > at > java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) > at java.base/java.lang.Thread.run(Thread.java:834) > Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "?, ?"; SQL > statement: > MERGE INTO > hypi_store_App(hypi_id,hypi_instanceId,hypi_created,hypi_updated,hypi_createdBy,hypi_instance,hypi_app,hypi_release,hypi_publisherRealm,hypi_publisherApp,hypi_publisherRelease,hypi_impl)( > SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, > CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? FROM hypi_store_App r WHERE hypi_id > = ? AND hypi_instanceId = ? > UNION > -- SELECT 'a','a','a','a','a','a','a','a','a','a','a','a' > SELECT ?,?,?,?,?,?,?,?,?,?,?,? > --SELECT ?,?,(IFNULL(SELECT hypi_created FROM hypi_store_App WHERE > hypi_instanceId = ? AND hypi_id = ?, CURRENT_TIMESTAMP())),?,?,?,?,?,?,?,?,? > ) [50004-197] > at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) > at org.h2.message.DbException.get(DbException.java:179) > at org.h2.message.DbException.get(DbException.java:155) > at org.h2.value.Value.getHigherOrder(Value.java:370) > at org.h2.command.dml.SelectUnion.prepare(SelectUnion.java:348) > at org.h2.command.dml.Merge.prepare(Merge.java:283) > at org.h2.command.Parser.prepareCommand(Parser.java:283) > at org.h2.engine.Session.prepareLocal(Session.java:611) > at org.h2.engine.Session.prepareCommand(Session.java:549) > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247) > at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76) > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:694) > at > org.apache.ignite.internal.processors.query.h2.ConnectionManager.prepareStatementNoCache(ConnectionManager.java:363) > at > org.apache.ignite.internal.processors.query.h2.QueryParser.parseH2(QueryParser.java:345) > ... 24 common frames omitted > Regards, Courtney Robinson Founder and CEO, Hypi Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io> https://hypi.io
