You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Courtney Robinson <co...@hypi.io> on 2020/04/22 21:55:42 UTC
SQL MERGE INTO with SELECT UNION
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
Re: SQL MERGE INTO with SELECT UNION
Posted by Courtney Robinson <co...@hypi.io>.
Hi Illya,
I did end up just doing select then insert/update in the end to work around
this.
I didn't try selecting over a temp. table though.
There are no additional guarantees as far as I know either but there used
to be a performance impact.
We're migrating functionality from using cache.put/putAll ourselves and
from our benchmarks of doing cache.get then cache.put there was a
difference that ranged from about 10 to 15% slower than when we changed to
a design that ensured we could always do cache.put without cache.get.
We assumed the same to be true with SQL so that's what motivated using
merge in the first place since we'd incur parsing once and fewer network
round trips...that was the theory anyway, we never got as far as
benchmarking to know for sure with our workload because we never got the
MERGE query working.
Regards,
Courtney Robinson
Founder and CEO, Hypi
Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>
https://hypi.io
On Fri, Apr 24, 2020 at 12:56 PM Ilya Kasnacheev <il...@gmail.com>
wrote:
> 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.robinson@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
>>
>
Re: SQL MERGE INTO with SELECT UNION
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <co...@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
>