subject:"\[jira\] \[Commented\] \(IGNITE\-18488\) SQL\: SUBSTRING function does not support NULL values"

[jira] [Commented] (IGNITE-18488) SQL: SUBSTRING function does not support NULL values

2023-04-21 Thread Andrey Khitrin (Jira)



[ 
https://issues.apache.org/jira/browse/IGNITE-18488?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714984#comment-17714984
 ] 

Andrey Khitrin commented on IGNITE-18488:
-

I've made a clone of this issue with updated description (IGNITE-19341). Hope 
it helps.

> SQL: SUBSTRING function does not support NULL values
> 
>
> Key: IGNITE-18488
> URL: https://issues.apache.org/jira/browse/IGNITE-18488
> 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
>
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> ANSI99 SQL specification ("6.18 ") says the following:
> {code:java}
> 3) If  is specified, then:
>   a) Let C be the value of the , ..., and let S 
> be the value of the .
>   b) If  is specified, then let L be the value of  length> ...
>   c) If either C, S, or L is the null value, then the result of the 
>  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( FROM  
> FOR )'. Supported form(s): 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 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( FROM  FOR 
> )'. Supported form(s): 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 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 + 
> [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)
>

[jira] [Commented] (IGNITE-18488) SQL: SUBSTRING function does not support NULL values

2023-04-19 Thread Andrey Khitrin (Jira)



[ 
https://issues.apache.org/jira/browse/IGNITE-18488?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714182#comment-17714182
 ] 

Andrey Khitrin commented on IGNITE-18488:
-

[~amashenkov] [~korlov]
Guys, do I understand correctly that the only 1 case of 3 was fixed here?
{code:sql}
sql-cli> SELECT SUBSTRING(NULL FROM 1 FOR 2);
╔═╗
║ EXPR$0  ║
╠═╣
║ null║
╚═╝
{code}
I still got an error on the fresh AI3 for this query:
{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( FROM  FOR )'. Supported form(s): 
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
{code}
And for this one:
{code:sql}
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( FROM  FOR )'. Supported form(s): 
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
'SUBSTRING( FROM )'
'SUBSTRING( FROM  FOR )'
{code}
Please correct me if I'm wrong. What should we do with remaining two cases? 
They're also a part of the ANSI99 SQL spec.

> SQL: SUBSTRING function does not support NULL values
> 
>
> Key: IGNITE-18488
> URL: https://issues.apache.org/jira/browse/IGNITE-18488
> 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
>
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> ANSI99 SQL specification ("6.18 ") says the following:
> {code:java}
> 3) If  is specified, then:
>   a) Let C be the value of the , ..., and let S 
> be the value of the .
>   b) If  is specified, then let L be the value of  length> ...
>   c) If either C, S, or L is the null value, then the result of the 
>  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( FROM  
> FOR )'. Supported form(s): 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 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( FROM  FOR 
> )'. Supported form(s): 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 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 + 
> [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(ReduceOp

[jira] [Commented] (IGNITE-18488) SQL: SUBSTRING function does not support NULL values

2023-01-24 Thread Konstantin Orlov (Jira)



[ 
https://issues.apache.org/jira/browse/IGNITE-18488?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17680268#comment-17680268
 ] 

Konstantin Orlov commented on IGNITE-18488:
---

[~amashenkov], LGTM!

> SQL: SUBSTRING function does not support NULL values
> 
>
> Key: IGNITE-18488
> URL: https://issues.apache.org/jira/browse/IGNITE-18488
> 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 ") says the following:
> {code:java}
> 3) If  is specified, then:
>   a) Let C be the value of the , ..., and let S 
> be the value of the .
>   b) If  is specified, then let L be the value of  length> ...
>   c) If either C, S, or L is the null value, then the result of the 
>  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( FROM  
> FOR )'. Supported form(s): 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 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( FROM  FOR 
> )'. Supported form(s): 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 'SUBSTRING( FROM )'
> 'SUBSTRING( FROM  FOR )'
> 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 + 
> [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.net

[jira] [Commented] (IGNITE-18488) SQL: SUBSTRING function does not support NULL values

[jira] [Commented] (IGNITE-18488) SQL: SUBSTRING function does not support NULL values

[jira] [Commented] (IGNITE-18488) SQL: SUBSTRING function does not support NULL values

3 matches

Site Navigation

Mail list logo

Footer information