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

[sajjoseph]

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)*)?)?");

[martint]

DECIMAL '0E-10' is an invalid expression. This is a bug in the JDBC driver, which should not be encoding the value in that format:

 setParameter(parameterIndex, formatLiteral("DECIMAL", x.toString())); 

[Akanksha-kedia]

can i take up this issue @martint ?

[ebyhr]

@Akanksha-kedia Sure, please feel free to send a PR!