aws / aws/amazon-redshift-jdbc-driver

`RedshiftPreparedStatement#toString` generates incorrect Redshift query for query containing filter condition on decimal columns

Open
#129 0 comments 1 reaction 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.