View Issue Details

IDProjectCategoryView StatusLast Update
0004345SymmetricDSBugpublic2021-05-28 20:45
Reportercparis Assigned Tojosh-a-hicks  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.11.7 
Target Version3.12.10Fixed in Version3.12.10 
Summary0004345: Postgres array types improperly cast into non-array types
DescriptionWhen synchronizing a table that has the select Postgres value types that are arrays in Postgres, the PostgresSqlDmlStatement.appendColumnParameter incorrectly casts them as non-arrays.

The exception generated summary is:
org.jumpmind.db.sql.SqlException: ERROR: column "foo" is of type uuid[] but expression is of type uuid

This impacts the following Postgres array types:
* timestamp with time zone[]
* uuid[]
* bit varying[]
* interval[]
* tsvector[]

The two functions that would need to be corrected for this are:
org.jumpmind.db.platform.postgresql.PostgreSqlDmlStatement:
* appendColumnParameter
* appendColumnEquals

Steps To Reproduce1. Create a table to sync:
    CREATE TABLE example(id integer, friends uuid[]);

2. Insert data:
    INSERT INTO example VALUES (1, null);
    INSERT INTO example VALUES (2, ['db3c1ff0-6a73-4aa3-86bd-53aa379b0dd7', 'e014d4fc-a14a-4d19-86dc-7ac78aa0c939']::uuid[]);

3. Allow data to synchronize.

Repeat with an update, or setting the values to NULL.
Additional InformationWhile in the same space, json/jsonb values are not being cast. Will look for additional bugs on this.

I have had to patch my Java code, and am using the "patches" folder to drop in a replacement. Happy to provide you the diff, branch+PR, etc.
Tagsdata type, dialect: postgresql

Related Changesets

SymmetricDS: 3.12 b17543d0

2021-05-11 13:46:05

avarianik


Committer: GitHub Details Diff
0004345: Postgres array types improperly cast into non-array types (#122)

* 0004345: add json and jsonb column type support for PostgreSqlDmlStatement

* refactor methods for parameters appending from PostgreSqlDmlStatement

* 0004345: add array column type support for PostgreSqlDmlStatement
Affected Issues
0004345
mod - symmetric-db/src/main/java/org/jumpmind/db/model/TypeMap.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDmlStatement.java Diff File

SymmetricDS: 3.12 f22fa390

2021-05-11 17:49:52

joshhicks

Details Diff
0004345: Postgres array types improperly cast into non-array types Affected Issues
0004345
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDmlStatement.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDatabasePlatform.java Diff File

Issue History

Date Modified Username Field Change
2020-04-15 00:12 cparis New Issue
2020-04-15 00:13 cparis Tag Attached: dialect: postgresql
2020-04-15 14:23 elong Tag Attached: data type
2021-02-04 05:48 avarianik Note Added: 0001887
2021-05-11 13:47 josh-a-hicks Assigned To => josh-a-hicks
2021-05-11 13:47 josh-a-hicks Status new => resolved
2021-05-11 13:47 josh-a-hicks Resolution open => fixed
2021-05-11 13:47 josh-a-hicks Fixed in Version => 3.12.10
2021-05-11 14:00 avarianik Changeset attached => SymmetricDS 3.12 b17543d0
2021-05-11 18:00 Changeset attached => SymmetricDS 3.12 f22fa390
2021-05-28 20:45 elong Status resolved => closed
2021-05-28 20:45 elong Target Version => 3.12.10