Inappropriate built-in function is used in the Freemarker template

[kyle306]

Bug Report

Which version of ShardingSphere did you use?

5.4.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

In PostgreSql 14, there is a table whose primary key is a generated identity and the sequence has a max value of 2147483647. When to migrate the table to sharding tables, ShardingSphere-Proxy can migrate data in the table into sharding tables according to the configuration.

Actual behavior

When to migrate the table to sharding tables, ShardingSphere-Proxy failed to convert 2,147,483,647 to number for Freemarker expression c.seqmax?number in shardingsphere-5.4.0/kernel/data-pipeline/dialect/postgresql/src/main/resources/template/component/table/12_plus/create.ftl . Thus it failed to migrate data.

Reason analyze (If you can)

According to Freemarker documents , the result of c.seqmax depends on the locale and the output might be with grouping separators for human reading. That is why the max value of sequence is formatted to 2,147,483,647 .

As the create.ftl is used to generate SQL which is for computer consuming, c.seqmax?c might be the better one. So 'c.seqmax?number' might go to 'c.seqmax?c?number'.

c.seqmin and other templates might have the same issue.

[sandynz]

Hi @kyle306 , thanks for you feedback.
It depends on kernel function. We'll improve it in new version.

And you could create table in proxy manually as a workaround for now. When target table is existing, auto-create table step in migration will be skipped.