You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Andrey Khitrin (Jira)" <ji...@apache.org> on 2023/04/21 12:46:00 UTC
[jira] [Updated] (IGNITE-19341) SQL: SUBSTRING function does not support NULL values (try 2)

     [ https://issues.apache.org/jira/browse/IGNITE-19341?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Andrey Khitrin updated IGNITE-19341:
------------------------------------
    Description: 
ANSI99 SQL specification ("6.18 <string value function>") says the following:
{code:java}
3) If <character substring function> is specified, then:
  a) Let C be the value of the <character value expression>, ..., and let S be the value of the <start position>.
  b) If <string length> is specified, then let L be the value of <string length> ...
  c) If either C, S, or L is the null value, then the result of the <character substring function> is the null value.
{code}
So, we should expect the following behavior:
{code:sql}
SUBSTRING('text' FROM 1 FOR NULL) -> NULL
SUBSTRING('text' FROM NULL FOR 2) -> NULL
SUBSTRING(NULL FROM 1 FOR 2) -> NULL
{code}

Instead, we got errors for these queries:
{code:sql}
sql-cli> SELECT SUBSTRING('text' FROM 1 FOR NULL);
SQL query execution error
Exception while executing query [query=SELECT SUBSTRING('text' FROM 1 FOR NULL);]. Error message:From line 1, column 8
to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <INTEGER> FOR <NULL>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'

sql-cli> SELECT SUBSTRING('text' FROM NULL FOR 2);
SQL query execution error
Exception while executing query [query=SELECT SUBSTRING('text' FROM NULL FOR 2);]. Error message:From line 1, column 8
to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <NULL> FOR <INTEGER>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
{code}

Only such request works fine:
{code:sql}
sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2);
╔═════════╗
║ EXPR$0  ║
╠═════════╣
║ null    ║
╚═════════╝
{code}

  was:
ANSI99 SQL specification ("6.18 <string value function>") says the following:
{code:java}
3) If <character substring function> is specified, then:
  a) Let C be the value of the <character value expression>, ..., and let S be the value of the <start position>.
  b) If <string length> is specified, then let L be the value of <string length> ...
  c) If either C, S, or L is the null value, then the result of the <character substring function> is the null value.
{code}
So, we should expect the following behavior:
{code:sql}
SUBSTRING('text' FROM 1 FOR NULL) -> NULL
SUBSTRING('text' FROM NULL FOR 2) -> NULL
SUBSTRING(NULL FROM 1 FOR 2) -> NULL
{code}
Instead, we got errors for these queries:
{code:sql}
sql-cli> SELECT SUBSTRING('text' FROM 1 FOR NULL);
SQL query execution error
Exception while executing query [query=SELECT SUBSTRING('text' FROM 1 FOR NULL);]. Error message:From line 1, column 8 to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <INTEGER> FOR <NULL>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'

sql-cli> SELECT SUBSTRING('text' FROM NULL FOR 2);
SQL query execution error
Exception while executing query [query=SELECT SUBSTRING('text' FROM NULL FOR 2);]. Error message:From line 1, column 8 to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <NULL> FOR <INTEGER>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER>)'
'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'

sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2);
  (client freezes, no answer at all)
{code}

*Important*: the last query causes {{NullPointerException}} within DB:
{code:java}
2022-12-30 07:32:46:210 +0000 [WARNING][node1-srv-worker-1][ClientInboundMessageHandler] Error processing client request
java.lang.NullPointerException
        at org.apache.ignite.internal.sql.SqlColumnTypeConverter.columnTypeToClass(SqlColumnTypeConverter.java:42)
        at org.apache.ignite.client.handler.JdbcQueryCursorHandlerImpl.createColumnMetadata(JdbcQueryCursorHandlerImpl.java:162)
        at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
        at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655)
        at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
        at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
        at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
        at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
        at org.apache.ignite.client.handler.JdbcQueryCursorHandlerImpl.queryMetadataAsync(JdbcQueryCursorHandlerImpl.java:133)
        at org.apache.ignite.client.handler.requests.jdbc.ClientJdbcQueryMetadataRequest.process(ClientJdbcQueryMetadataRequest.java:47)
        at org.apache.ignite.client.handler.ClientInboundMessageHandler.processOperation(ClientInboundMessageHandler.java:454)
        at org.apache.ignite.client.handler.ClientInboundMessageHandler.processOperation(ClientInboundMessageHandler.java:336)
        at org.apache.ignite.client.handler.ClientInboundMessageHandler.channelRead(ClientInboundMessageHandler.java:187)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:327)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:299)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
        at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
        at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:722)
        at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:658)
        at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:584)
        at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:496)
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:995)
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:829)
{code}


> SQL: SUBSTRING function does not support NULL values (try 2)
> ------------------------------------------------------------
>
>                 Key: IGNITE-19341
>                 URL: https://issues.apache.org/jira/browse/IGNITE-19341
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 3.0.0-beta1
>            Reporter: Andrey Khitrin
>            Assignee: Andrey Mashenkov
>            Priority: Major
>              Labels: ignite-3, sql
>             Fix For: 3.0.0-beta2
>
>
> ANSI99 SQL specification ("6.18 <string value function>") says the following:
> {code:java}
> 3) If <character substring function> is specified, then:
>   a) Let C be the value of the <character value expression>, ..., and let S be the value of the <start position>.
>   b) If <string length> is specified, then let L be the value of <string length> ...
>   c) If either C, S, or L is the null value, then the result of the <character substring function> is the null value.
> {code}
> So, we should expect the following behavior:
> {code:sql}
> SUBSTRING('text' FROM 1 FOR NULL) -> NULL
> SUBSTRING('text' FROM NULL FOR 2) -> NULL
> SUBSTRING(NULL FROM 1 FOR 2) -> NULL
> {code}
> Instead, we got errors for these queries:
> {code:sql}
> sql-cli> SELECT SUBSTRING('text' FROM 1 FOR NULL);
> SQL query execution error
> Exception while executing query [query=SELECT SUBSTRING('text' FROM 1 FOR NULL);]. Error message:From line 1, column 8
> to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <INTEGER> FOR <NULL>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
> 'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
> 'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
> 'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
> 'SUBSTRING(<BINARY> FROM <INTEGER>)'
> 'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
> 'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
> 'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
> sql-cli> SELECT SUBSTRING('text' FROM NULL FOR 2);
> SQL query execution error
> Exception while executing query [query=SELECT SUBSTRING('text' FROM NULL FOR 2);]. Error message:From line 1, column 8
> to line 1, column 40: Cannot apply 'SUBSTRING' to arguments of type 'SUBSTRING(<CHAR(4)> FROM <NULL> FOR <INTEGER>)'. Supported form(s): 'SUBSTRING(<CHAR> FROM <INTEGER>)'
> 'SUBSTRING(<CHAR> FROM <INTEGER> FOR <INTEGER>)'
> 'SUBSTRING(<VARCHAR> FROM <INTEGER>)'
> 'SUBSTRING(<VARCHAR> FROM <INTEGER> FOR <INTEGER>)'
> 'SUBSTRING(<BINARY> FROM <INTEGER>)'
> 'SUBSTRING(<BINARY> FROM <INTEGER> FOR <INTEGER>)'
> 'SUBSTRING(<VARBINARY> FROM <INTEGER>)'
> 'SUBSTRING(<VARBINARY> FROM <INTEGER> FOR <INTEGER>)'
> {code}
> Only such request works fine:
> {code:sql}
> sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2);
> ╔═════════╗
> ║ EXPR$0  ║
> ╠═════════╣
> ║ null    ║
> ╚═════════╝
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)