aws / aws/amazon-redshift-jdbc-driver
`RedshiftPreparedStatement#toString` generates incorrect Redshift query for query containing filter condition on decimal columns
- Dominant language
- Java
- Stars
- 71
- Forks
- 42
- PR merge metrics
- No merged PRs in 30d
Description
## Driver version
## Redshift version
`PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.79229`
but issue doesn't seem to be related to Redshift server.
## Client Operating System
mac. but NA for this issue
## JAVA/JVM version
openjdk 23.0.1
## Table schema
```sql
CREATE TABLE test_schema.t_decimal (
c_decimal numeric(3,1) ENCODE az64
)
DISTSTYLE AUTO;
insert into test_schema.t_decimal values (CAST('10.1' AS decimal(3, 1)));
```
## Problem description
- Create an object of `RedshiftPreparedStatement` for `SELECT * FROM "test_schema"."t_decimal" WHERE "c_decimal" = 10.1;` sql query.
- Output of `RedshiftPreparedStatement.toString()` for the above query produces `SELECT "c_decimal" FROM "test_schema"."t_decimal" WHERE "c_decimal" = ('10.1'::numeric)` output. Ideally it should generate `SELECT "c_decimal" FROM "test_schema"."t_decimal" WHERE "c_decimal" = ('10.1'::numeric(3,1))`
As we can see that the cast is missing precision ie`'10.1'::numeric` vs `'10.1'::numeric(3,1)` leading to no output from the Redshift database(ideally 10.1 row should be returned as an output when numeric(3,1) case is provided)
1. Expected behaviour: `SELECT "c_decimal" FROM "test_schema"."t_decimal" WHERE "c_decimal" = ('10.1'::numeric)`
2. Actual behaviour: `SELECT "c_decimal" FROM "test_schema"."t_decimal" WHERE "c_decimal" = ('10.1'::numeric(3,1))`
3. Error message/stack trace:
4. Any other details that can be helpful:
## JDBC trace logs
## Reproduction code
Contributor guide
Research direction
Start at RedshiftPreparedStatement.toString and reproduce the decimal-filter query against the supplied test_schema.t_decimal schema. Resolve the issue text's reversed expected and actual outputs, then verify that the agreed cast preserves numeric(3,1) precision and returns the inserted 10.1 row.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100