Redshift Federated Query Error 25101

Question

Hello everyone,

I discovered a problem during a Redshift Spectrum `SELECT` query that works as expected when it's ran inside the RDS (Postgres) DB.

The query is like the following:

```
SELECT MIN(column) FROM table;
```

When ran inside the RDS, the result is correct, when ran via Redshift Spectrum it raises the following exception:

```
SQL Error [XX000]: ERROR: Decimal input parse error
  Detail: 
  -----------------------------------------------
  error:  Decimal input parse error
  code:      25101
  context:   str:0.5507626528024723909068
  query:     10256
  location:  federation_fetchers.cpp:194
  process:   query0_121_10256 [pid=8648]
  -----------------------------------------------
```

The data type is set to `NUMERIC` in the RDS DB. Please, notice there is no `0.5507626528024723909068` value in the table.

I can't find any similar question anywhere and I would be really grateful if anyone had any advice on this. Thank you in advance!

Answer

The easy way to solve this is by moving the table in question to Redshift, as well, via a DMS Replication Task. This works but keep in mind that it changes its data type automatically to `NUMERIC(28,6)` which means that you may lose some precision. However, I haven't found a better solution for this, I am still open to any suggestions. Posting this answer in case it helps someone else.

Redshift Federated Query Error 25101

Relevanter Inhalt