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

Cannot compare timestamp with varchar value #7334

Closed
andreas-eberle opened this issue Mar 18, 2021 · 3 comments
Closed

Cannot compare timestamp with varchar value #7334

andreas-eberle opened this issue Mar 18, 2021 · 3 comments

Comments

@andreas-eberle
Copy link

We want to query a subset of our data based on a timestamp from a hive table. The query could look like this:

select * from my_table where uploadtimestamp < '2021-03-17 11:42:18';

While a query like this works with any other database we tested, it doesn't work with trino. Instead, we get the following error:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL-Fehler [58]: Query failed (#20210318_135115_00417_7pw2p): line 1:59: Cannot apply operator: timestamp(3) < varchar(19)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:509)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:440)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:167)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:427)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:812)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3233)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:167)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4517)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Query failed (#20210318_135115_00417_7pw2p): line 1:59: Cannot apply operator: timestamp(3) < varchar(19)
	at io.trino.jdbc.AbstractTrinoResultSet.resultsException(AbstractTrinoResultSet.java:1912)
	at io.trino.jdbc.TrinoResultSet.getColumns(TrinoResultSet.java:242)
	at io.trino.jdbc.TrinoResultSet.create(TrinoResultSet.java:53)
	at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:249)
	at io.trino.jdbc.TrinoStatement.execute(TrinoStatement.java:227)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:327)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
	... 12 more
Caused by: io.trino.spi.TrinoException: line 1:59: Cannot apply operator: timestamp(3) < varchar(19)
	at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:48)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.getOperator(ExpressionAnalyzer.java:1758)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:600)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:404)
	at io.trino.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:71)
	at io.trino.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:27)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:427)
	at io.trino.sql.analyzer.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:359)
	at io.trino.sql.analyzer.ExpressionAnalyzer.analyzeExpression(ExpressionAnalyzer.java:1973)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeExpression(StatementAnalyzer.java:2919)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeWhere(StatementAnalyzer.java:2752)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.lambda$visitQuerySpecification$26(StatementAnalyzer.java:1594)
	at java.base/java.util.Optional.ifPresent(Optional.java:183)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1594)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:343)
	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:360)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:370)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1075)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:343)
	at io.trino.sql.tree.Query.accept(Query.java:107)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:360)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:323)
	at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:91)
	at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:83)
	at io.trino.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:263)
	at io.trino.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:186)
	at io.trino.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:768)
	at io.trino.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:129)
	at io.trino.$gen.Trino_353____20210318_120502_2.call(Unknown Source)
	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: io.trino.metadata.OperatorNotFoundException: Cannot apply operator: timestamp(3) < varchar(19)
	at io.trino.metadata.MetadataManager.resolveOperator(MetadataManager.java:1880)
	at io.trino.sql.analyzer.ExpressionAnalyzer$Visitor.getOperator(ExpressionAnalyzer.java:1755)
	... 36 more
Caused by: io.trino.spi.TrinoException: Unexpected parameters (timestamp(3), varchar(19)) for function $operator$less_than. Expected: $operator$less_than(T, T) T:orderable
	at io.trino.metadata.FunctionResolver.resolveFunction(FunctionResolver.java:151)
	at io.trino.metadata.MetadataManager.lambda$resolveFunction$29(MetadataManager.java:1868)
	at java.base/java.util.Optional.orElseGet(Optional.java:369)
	at io.trino.metadata.MetadataManager.resolveFunction(MetadataManager.java:1868)
	at io.trino.metadata.MetadataManager.resolveOperator(MetadataManager.java:1876)
	... 37 more
@martint
Copy link
Member

martint commented Mar 18, 2021

VARCHAR and TIMESTAMP are not directly comparable. This is intentional and follows standard SQL semantics. You need to convert one to the other type. For a literal, you can use this form:

SELECT * FROM my_table WHERE uploadtimestamp < TIMESTAMP '2021-03-17 11:42:18';

Otherwise, you can use a CAST:

SELECT * FROM my_table WHERE uploadtimestamp < CAST('2021-03-17 11:42:18' AS timestamp(3))

@andreas-eberle
Copy link
Author

Is there a reason not to auto cast it like e.g. Postgres, MySQL and many others do?

E.g. for Superset, this causes problems (see apache/superset#13009).

@andreas-eberle
Copy link
Author

@martint: Is there a reason not to adapt to the widely used practice? It seems some tools rely on it and it is also very convenient as a user.

@ebyhr ebyhr closed this as not planned Won't fix, can't repro, duplicate, stale Aug 18, 2022
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
3 participants
@martint @ebyhr @andreas-eberle