Summary
This article discusses how to resolve any query against PostgreSQL sources failing with Source 'my_postgres_source' returned error 'ERROR: operator does not exist: character varying = integer when using the ORDER BY clause in the underlying VDS definitions.
Reported Issue
When attempting to run VDS queries (containing the ORDER BY clause in their definitions) which go against underlying PostgreSQL sources, these would fail with the below error:
Source 'my_postgres_source' returned error 'ERROR: operator does not exist: character varying = integer
However running the same query without the ORDER BY clause in the VDS definition allows the query to succeed.
Relevant Versions
This issue applies to Dremio versions 25.1.x and 25.2.x.
Troubleshooting Steps
1. Navigate to the failing job profile as per the following documentation link steps: https://docs.dremio.com/current/sonar/monitoring/jobs/raw-profile/
2. After navigating to the "Raw Profile", click on the "Error" tab of the profile.
3. Confirm that the error is the same as mentioned in this article. As an example, the following error can be seen in the "Verbose" section of the "Error" tab of the job profile:
DATA_READ ERROR: Source 'my_postgres_source' returned error 'ERROR: operator does not exist: character varying = integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 801'
plugin my_postgres_source
sql SELECT "id", "fieldgg", UPPER("fieldgg") AS "field_name"
FROM (SELECT fieldA, field B
FROM "a"."table1") AS "table2"
WHERE ("fieldcc" = 0) AND UPPER("fieldgg") LIKE '[aa, bb, cc, dd, %' COLLATE "ee"
SqlOperatorImpl JDBC_SUB_SCAN
Location 3:0:16
SqlOperatorImpl JDBC_SUB_SCAN
Location 3:0:16
ErrorOrigin: EXECUTOR
[Error Id: 6a03d45c-c490-48f7-b72d-6290ab7e3e77 on dremio-executor-0.dremio-cluster-pod.dremio-stage.svc.cluster.local:0]
(org.postgresql.util.PSQLException) ERROR: operator does not exist: character varying = integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 801
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2725
org.postgresql.core.v3.QueryExecutorImpl.processResults():2412
org.postgresql.core.v3.QueryExecutorImpl.execute():371
org.postgresql.jdbc.PgStatement.executeInternal():502
org.postgresql.jdbc.PgStatement.execute():419
org.postgresql.jdbc.PgStatement.executeWithFlags():341
org.postgresql.jdbc.PgStatement.executeCachedSql():326
org.postgresql.jdbc.PgStatement.executeWithFlags():302
org.postgresql.jdbc.PgStatement.executeQuery():251
org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():242
com.dremio.exec.store.CoercionReader.setup():127
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():410
com.dremio.sabot.op.scan.ScanOperator.setupReader():400
com.dremio.sabot.op.scan.ScanOperator.setup():364
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():638
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():90
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():70
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():609
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.WyePipe.setup():130
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.WyePipe.setup():128
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.WyePipe.setup():128
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.StraightPipe.setup():100
com.dremio.sabot.driver.Pipeline.setup():79
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():770
com.dremio.sabot.exec.fragment.FragmentExecutor.run():549
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():1274
com.dremio.sabot.task.AsyncTaskWrapper.run():130
com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():281
com.dremio.sabot.task.slicing.SlicingThread.run():186
4. Carry out the "Steps to Resolve" described below.
Cause
This issue is caused by unsupported pushdown operations with VARCHAR data types from the Dremio PostgreSQL connector/plugin. Known issue reported internally against DX-98093.
Steps to Resolve
Upgrade to Dremio v25.1.5 and higher 25.1.x versions or Dremio v25.2.2 and higher 25.2.x versions.
Additional Resources
Viewing a Raw Profile: https://docs.dremio.com/current/sonar/monitoring/jobs/raw-profile/
v25.1.6 Release Notes: https://docs.dremio.com/current/release-notes/version-250-release#2516-january-2025-enterprise
v25.2.2 Release Notes: https://docs.dremio.com/current/release-notes/version-250-release#2522-december-2024