Hello! I think you can union a select over a temporary table of one row. such as
select * from table (id bigint = ?, ...) However, maybe you should just re-write your upsert with select and then insert/update. You're not gaining any more guarantees by using MERGE, as far as I know. Regards, -- Ilya Kasnacheev чт, 23 апр. 2020 г. в 00:56, Courtney Robinson <courtney.robin...@hypi.io>: > 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 >
