Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

DECIMAL '0E-10' is not a valid DECIMAL literal. #23523

Open
sajjoseph opened this issue Sep 22, 2024 · 0 comments
Open

DECIMAL '0E-10' is not a valid DECIMAL literal. #23523

sajjoseph opened this issue Sep 22, 2024 · 0 comments

Comments

@sajjoseph
Copy link
Contributor

Following is a challenge I came across.

Original SQL:

select item, item_cost
from   remotedatasource.schema.item_table
and    item_cost <> 0

This produced the following query against a remote Trino cluster:

EXECUTE IMMEDIATE 'SELECT "item", "item_cost" FROM "datasource"."schema"."item_table" 
WHERE NOT ("item_cost" = ?)' USING DECIMAL '0E-10'

Remote query failed with the following exception:

io.trino.spi.TrinoException: line 1:615: '0E-10' is not a valid DECIMAL literal
	at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:52)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitDecimalLiteral(ExpressionAnalyzer.java:1145)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitDecimalLiteral(ExpressionAnalyzer.java:645)
	at io.trino.sql.tree.DecimalLiteral.accept(DecimalLiteral.java:50)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:675)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitParameter(ExpressionAnalyzer.java:2229)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitParameter(ExpressionAnalyzer.java:645)
	at io.trino.sql.tree.Parameter.accept(Parameter.java:50)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:675)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.getOperator(ExpressionAnalyzer.java:3284)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:889)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:645)
	at io.trino.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:129)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:675)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.coerceType(ExpressionAnalyzer.java:3317)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitNotExpression(ExpressionAnalyzer.java:864)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitNotExpression(ExpressionAnalyzer.java:645)
	at io.trino.sql.tree.NotExpression.accept(NotExpression.java:54)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:675)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.coerceType(ExpressionAnalyzer.java:3317)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitLogicalExpression(ExpressionAnalyzer.java:873)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitLogicalExpression(ExpressionAnalyzer.java:645)
	at io.trino.sql.tree.LogicalExpression.accept(LogicalExpression.java:77)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:675)
	at io.trino.sql.analyzer.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:496)
	at io.trino.sql.analyzer.ExpressionAnalyzer.analyzeExpression(ExpressionAnalyzer.java:3630)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeExpression(StatementAnalyzer.java:5051)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeWhere(StatementAnalyzer.java:4873)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.lambda$visitQuerySpecification$72(StatementAnalyzer.java:3096)
	at java.base/java.util.Optional.ifPresent(Optional.java:178)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:3096)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:520)
	at io.trino.sql.tree.QuerySpecification.accept(QuerySpecification.java:155)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:539)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:547)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1562)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:520)
	at io.trino.sql.tree.Query.accept(Query.java:118)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:539)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:499)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:493)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitTableSubquery(StatementAnalyzer.java:3081)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitTableSubquery(StatementAnalyzer.java:520)
	at io.trino.sql.tree.TableSubquery.accept(TableSubquery.java:53)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:539)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitAliasedRelation(StatementAnalyzer.java:2889)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitAliasedRelation(StatementAnalyzer.java:520)
	at io.trino.sql.tree.AliasedRelation.accept(AliasedRelation.java:71)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:539)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:4891)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:3091)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:520)
	at io.trino.sql.tree.QuerySpecification.accept(QuerySpecification.java:155)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:539)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:547)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1562)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:520)
	at io.trino.sql.tree.Query.accept(Query.java:118)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:539)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:499)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:488)
	at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:100)
	at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:89)
	at io.trino.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:285)
	at io.trino.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:218)
	at io.trino.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:884)
	at io.trino.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:153)
	at io.trino.$gen.Trino_455_c0_3____20240920_004729_2.call(Unknown Source)
	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76)
	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
	at java.base/java.lang.Thread.run(Thread.java:1570)
Caused by: java.lang.IllegalArgumentException: Invalid DECIMAL value '0E-10'
	at io.trino.spi.type.Decimals.parse(Decimals.java:81)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitDecimalLiteral(ExpressionAnalyzer.java:1142)
	... 83 more

item_cost is of type DECIMAL(38,10)
Value 0 gets converted into BigDecimal 0 at

trino/plugin/trino-base-jdbc/src/main/java/io/trino/plugin/jdbc/StandardColumnMappings.java

Line 247 in 057f2d4

BigDecimal bigDecimal = new BigDecimal(unscaledValue, decimalType.getScale(), new MathContext(decimalType.getPrecision()));

Later

trino/client/trino-jdbc/src/main/java/io/trino/jdbc/TrinoPreparedStatement.java

Line 245 in 057f2d4

setParameter(parameterIndex, formatLiteral("DECIMAL", x.toString()));
converts the BigDecimal into string. BigDecimal's string value for 0 became "0E-10".

This value is not recognized by Trino itself.

trino> SELECT DECIMAL '0E-10';
Query 20240922_015106_00003_2uigf failed: line 1:8: '0E-10' is not a valid DECIMAL literal.

I can fix the problem by adjusting the trino-jdbc driver code (TrinoPreparedStatement.java - setBigDecimal) as below.

    @Override
    public void setBigDecimal(int parameterIndex, BigDecimal x)
            throws SQLException
    {
        checkOpen();
        if (x == null) {
            setNull(parameterIndex, Types.DECIMAL);
        }
        else {
            if (x.signum() == 0) {
                // Trino does not support negative zero
                x = BigDecimal.ZERO;
            }
            setParameter(parameterIndex, formatLiteral("DECIMAL", x.toString()));
        }
    }

But, do we need to add support for "0E-10" format in Decimal?
The current code doesn't accommodate this style.

trino/core/trino-spi/src/main/java/io/trino/spi/type/Decimals.java

Line 49 in 057f2d4

private static final Pattern DECIMAL_PATTERN = Pattern.compile("([+-]?)(\\d(?:_?\\d)*)?(?:\\.(\\d(?:_?\\d)*)?)?");

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@sajjoseph