bug: Empty nullable fields return as true when queried from RDS Postgres #11871
Description
Is there an existing issue for this?
- I have searched the existing issues
Current Behavior
When i return an object from a table with a nullable column, if that records value is null... the RDS Data API appears to return TRUE.
Expected Behavior
The column should be set to NULL.
How are you starting LocalStack?
With the localstack script
Steps To Reproduce
How are you starting localstack (e.g., bin/localstack command, arguments, or docker-compose.yml)
DISABLE_CORS_CHECKS=1 DEBUG=1 localstack start
Client commands (e.g., AWS SDK code snippet, or sequence of "awslocal" commands)
Add a table such as this to Postgres v11 or v13 on LocalStack.
CREATE TABLE IF NOT EXISTS my_table (
my_table_id VARCHAR(36) PRIMARY KEY,
will_return_ok TEXT NOT NULL,
will_return_true_if_empty: TEXT[],
);
Add data where will_return_true_if_empty=[], will_return_true_if_empty=['text'], will_return_true_if_empty=NULL
Call this query 'SELECT * FROM my_table;'
export const executeRdsStatement = async (
queryString: string,
secretArn: string,
dbClusterArn: string,
): Promise<DatabaseResponse[]> => {
const command = new ExecuteStatementCommand({
database: "root" /* required */,
formatRecordsAs: "JSON",
includeResultMetadata: true,
resourceArn: dbClusterArn,
secretArn: secretArn,
sql: queryString /* required */,
});
const result = await rdsdataservice.send(command);
if (result.formattedRecords) {
return JSON.parse(result.formattedRecords) as DatabaseResponse[];
}
return [];
};
Observe that the formatted records have TRUE for the null value record.
Environment
- OS: MacOS
- LocalStack:
LocalStack version: 3.8.1
LocalStack Docker image sha: sha256:ede1832a84149228365c9a431c526da8cc004f3a199d6fc20fc4e3f739b9dd06
LocalStack build date: 2024-07-16
LocalStack build git hash: c4457f6Anything else?
Current workaround is the inspect the table schema and map the column value back. This does not occur in my prod/dev deploys in AWS. The database, when queried directly has the correct values.
const getTableSchema = async (
secretArn: string,
dbClusterArn: string,
tableName: string,
) => {
const schemaQuery = `
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = $1
ORDER BY ordinal_position;
`;
const command = new ExecuteStatementCommand({
database: "root" /* required */,
formatRecordsAs: "JSON",
includeResultMetadata: true,
parameters: [{ name: "1", value: { stringValue: tableName } }],
resourceArn: dbClusterArn,
secretArn: secretArn,
sql: schemaQuery,
});
const response = await rdsdataservice.send(command);
if (response.formattedRecords) {
return JSON.parse(response.formattedRecords);
}
return [];
};