Unexpected Exception with ORDER BY clause with UNION

[YuanchengJiang]

CrateDB version

5.4.3

CrateDB setup information

create table union_test (c_0 timestamp, c_1 int, c_2 float, c_3 varchar(16));
insert into union_test values (946702800000, 8, 3.0, 'Goo');
insert into union_test values (946688400000, 9, 7.0, 'Boo');
insert into union_test values (946692000000, 2, 4.0, 'Foo');
insert into union_test values (946695600000, 6, 8.0, 'Boo');
insert into union_test values (946699200000, 7, 6.0, 'Coo');

Problem description

Query SELECT count(1) from union_test ORDER BY 1 UNION ALL SELECT count(1) from union_test alerts SQLParseException[line 1:44: mismatched input 'UNION' expecting {<EOF>, ';'}] while SELECT count(1) from union_test UNION ALL SELECT count(1) from union_test; (without ORDER BY 1) works.

Note: SELECT count(1) from union_test ORDER BY 1; is a valid query.

Steps to Reproduce

Run:

SELECT count(1) from union_test ORDER BY 1 UNION ALL SELECT count(1) from union_test;

Actual Result

cr> SELECT count(1) from union_test ORDER BY 1 UNION ALL SELECT count(1) from union_test;
SQLParseException[line 1:44: mismatched input 'UNION' expecting {<EOF>, ';'}]

io.crate.exceptions.SQLParseException: line 1:44: mismatched input 'UNION' expecting {<EOF>, ';'}
	at io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:163)
	at io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:152)
	at io.crate.rest.action.SqlHttpHandler.sendResponse(SqlHttpHandler.java:163)
	at io.crate.rest.action.SqlHttpHandler.lambda$channelRead0$0(SqlHttpHandler.java:119)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:863)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenCompleteStage(CompletableFuture.java:887)
	at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:2357)
	at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:117)
	at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:81)
	at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.crate.protocols.http.HttpBlobHandler.channelRead0(HttpBlobHandler.java:166)
	at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.crate.auth.HttpAuthUpstreamHandler.handleHttpChunk(HttpAuthUpstreamHandler.java:135)
	at io.crate.auth.HttpAuthUpstreamHandler.channelRead0(HttpAuthUpstreamHandler.java:84)
	at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:318)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
	at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
	at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
	at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:800)
	at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:499)
	at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:397)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at java.base/java.lang.Thread.run(Thread.java:1623)
Caused by: io.crate.sql.parser.ParsingException: line 1:44: mismatched input 'UNION' expecting {<EOF>, ';'}
	at io.crate.sql.parser.SqlParser$1.syntaxError(SqlParser.java:58)
	at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
	at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:327)
	at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139)
	at io.crate.sql.parser.antlr.v4.SqlBaseParser.singleStatement(SqlBaseParser.java:437)
	at io.crate.sql.parser.SqlParser.invokeParser(SqlParser.java:132)
	at io.crate.sql.parser.SqlParser.generateStatement(SqlParser.java:99)
	at io.crate.sql.parser.SqlParser.createStatement(SqlParser.java:76)
	at io.crate.action.sql.Session.parse(Session.java:308)
	at io.crate.rest.action.SqlHttpHandler.executeSimpleRequest(SqlHttpHandler.java:236)
	at io.crate.rest.action.SqlHttpHandler.handleSQLRequest(SqlHttpHandler.java:207)
	at io.crate.rest.action.SqlHttpHandler.channelRead0(SqlHttpHandler.java:116)
	... 43 more
Caused by: org.antlr.v4.runtime.InputMismatchException
	at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:270)
	at io.crate.sql.parser.antlr.v4.SqlBaseParser.singleStatement(SqlBaseParser.java:422)
	... 50 more

Expected Result

+----------+
| count(*) |
+----------+
|        5 |
|        5 |
+----------+

[matriv]

Hi @YuanchengJiang,
Thx for working with CrateDB and reporting issues!

In this case though, ORDER BY cannot be used in the 1st query unless this is wrapped into a subselect like this:

SELECT * FROM (SELECT count(1) from union_test ORDER BY 1) as ut UNION ALL SELECT count(1) from union_test;

PostgreSQL also rejects the query:

matriv=> SELECT count(1) from union_test ORDER BY 1 UNION ALL SELECT count(1) from union_test;
ERROR:  syntax error at or near "UNION"
LINE 1: SELECT count(1) from union_test ORDER BY 1 UNION ALL SELECT ...
                                                   ^

This comes from SQL spec which defines that ORDER BY can only be used at end of the UNION and applies to all relations involved.

Therefore I'm closing this issue, but feel free to come back with any question/clarification request on this.

[YuanchengJiang]

I see. Thanks. Another workaround is to wrap both queries: (SELECT count(1) from union_test ORDER BY 1) UNION ALL (SELECT count(1) from union_test); but it seems CrateDB does not support bracket at the first char.

[matriv]

You can do it like this:

SELECT * FROM (SELECT count(1) from union_test ORDER BY 1) t1 UNION ALL SELECT * FROM (SELECT count(1) from union_test ORDER BY 1 desc) t2;

We always force for an alias (t1 &t2 in this case) when you use subqueries.