search

embulk / embulk-input-jdbc

MySQL, PostgreSQL, Redshift and generic JDBC input plugins for Embulk
Other
102 stars 74 forks source link

`use_raw_query_with_incremental` does not work in 2 over `incremental_columns` and same column name length #190

Closed fukasawah closed 4 years ago

fukasawah commented 4 years ago

I'm seeing this problem in postgresql and oracle.

Full config, log and test data here. >> reports.zip

I think, this code has problem if 2 incremental_columns and same column name length.

https://github.com/embulk/embulk-input-jdbc/blob/v0.10.1/embulk-input-jdbc/src/main/java/org/embulk/input/jdbc/JdbcInputConnection.java#L374-L379

For the same column name, I think it will just overwrite the value. (https://wandbox.org/permlink/wDmiPkNCwQJUkVWc)

Then I believe that one of the placeholders is not replaced and the next query is generated, resulting in an issue

    SELECT
      created_at, updated_at, col1, col2
    FROM
      test_incremental_columns
    WHERE
      (created_at > ?)
      OR (created_at = ? AND updated_at > :updated_at)
    ORDER BY
      created_at,
      updated_at

Exception log.

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: syntax error at or near ":"
  ポジション: 142
        at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.Exec.doWith(Exec.java:22)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:242)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:291)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:155)
        at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
        at org.embulk.cli.Main.main(Main.java:64)

On the basis of a different length column name, it passed. (see: reports.zip/config-ok.yml)

hito4t commented 4 years ago

@fukasawah Thank you for reporting the issue!

As you wrote, the way to sort column names is incorrect. I'll correct it before long.

hito4t commented 4 years ago

@fukasawah Thank you for reporting the issue!

As you wrote, the way to sort column names is incorrect. I'll correct it before long.

I've created the PR #191 .

fukasawah commented 4 years ago

Thanks! I tried that PR and it worked in my environment!!