You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/06/03 13:58:26 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue #10646: ERROR: column is of type timestamp without time zone but expression is of type character varying
TeslaCN opened a new issue #10646:
URL: https://github.com/apache/shardingsphere/issues/10646
## Bug Report
### Which version of ShardingSphere did you use?
master
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Proxy
### Expected behavior
Insert a row.
### Actual behavior
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "create_time" is of type timestamp without time zone but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 64
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:120)
at icu.wwj.Timestamp.proxy(Timestamp.java:30)
at icu.wwj.Timestamp.main(Timestamp.java:12)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```sql
create table t_order
(
order_id bigint not null
constraint t_order_0_pkey
primary key,
create_time timestamp default CURRENT_TIMESTAMP not null,
user_id bigint not null,
order_name varchar(1000) default ''::character varying not null,
amount integer default 0
);
```
```java
try (PreparedStatement preparedStatement = connection.prepareStatement("insert into t_order (create_time, user_id) values (?, ?)")) {
preparedStatement.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));
preparedStatement.setLong(2, 8888);
preparedStatement.executeUpdate();
}
```
### Example codes for reproduce this issue (such as a github link).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #10646: ERROR: column is of type timestamp without time zone but expression is of type character varying
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #10646:
URL: https://github.com/apache/shardingsphere/issues/10646#issuecomment-855545491
When timestamp column type's oid is `0`, it worked.
```
PostgreSQL
Type: Parse
Length: 94
Statement:
Query: insert into t_order_0 (order_id, create_time, user_id) values ($1, $2, $3)
Parameters: 3
Type OID: 20
Type OID: 0
Type OID: 20
PostgreSQL
Type: Bind
Length: 72
Portal:
Statement:
Parameter formats: 3
Format: Binary (1)
Format: Text (0)
Format: Binary (1)
Parameter values: 3
Column length: 8
Data: 00000179e46e5e8d
Column length: 26
Data: 323032312d30362d30372031313a30353a30392e3030362b3038
Column length: 8
Data: 00000000000022ba
Result formats: 0
PostgreSQL
Type: Describe
Length: 6
Portal:
PostgreSQL
Type: Execute
Length: 9
Portal:
Returns: 1 rows
PostgreSQL
Type: Sync
Length: 4
```
When Proxy executing the actual statement, the timestamp column type's oid was set to `1043` which is `varchar`.
```
PostgreSQL
Type: Parse
Length: 94
Statement:
Query: insert into t_order_1 (create_time, user_id, order_id) values ($1, $2, $3)
Parameters: 3
Type OID: 1043
Type OID: 20
Type OID: 20
PostgreSQL
Type: Bind
Length: 72
Portal:
Statement:
Parameter formats: 3
Format: Text (0)
Format: Binary (1)
Format: Binary (1)
Parameter values: 3
Column length: 26
Data: 323032312d30362d30372031313a30353a30392e3032332b3038
Column length: 8
Data: 00000000000022b8
Column length: 8
Data: 08723d96a807b001
Result formats: 0
PostgreSQL
Type: Describe
Length: 6
Portal:
PostgreSQL
Type: Execute
Length: 9
Portal:
Returns: all rows
PostgreSQL
Type: Sync
Length: 4
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #10646: ERROR: column is of type timestamp without time zone but expression is of type character varying
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #10646:
URL: https://github.com/apache/shardingsphere/issues/10646
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org