You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "jyotisharma7 (via GitHub)" <gi...@apache.org> on 2023/02/03 08:16:56 UTC
[GitHub] [shardingsphere] jyotisharma7 opened a new issue, #23964: Boolean type giving error in ShardingSphere while insert
jyotisharma7 opened a new issue, #23964:
URL: https://github.com/apache/shardingsphere/issues/23964
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
ShardingSphere Proxy - 5.3.2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere Proxy
### Expected behavior
`INSERT INTO "mobile" ("created_at","tracking_id","custom_released") VALUES (2023-01-25 16:36:04.11781, 775275296,false);
`
The above insert statement should pass successfully.
### Actual behavior
`ERROR: column "custom_released" is of type boolean but expression is of type character varying
`
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
sh start.sh
psql -U root -h 127.0.0.1 -p 3307 dbname=hint_db
INSERT INTO "mobile" ("created_at","tracking_id","custom_released") VALUES (2023-01-25 16:36:04.11781, 775275296,false);
```
### 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415416900
@TeslaCN happy to work on a fix for this. do you have any related PR or bug reported related to this issue on the mysql side?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415418809
Here is the log statement from ShardingSphere logs:
```
org.postgresql.util.PSQLException: ERROR: column "custom_released" is of type boolean but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 310
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:43)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:70)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:47)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:90)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:69)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:121)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:115)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:75)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:229)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:186)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:151)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:176)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.Portal.bind(Portal.java:103)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:53)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415832912
> > Hi @surukonda About the `goorm`, did you mean this [gorm](https://gorm.io/) or something else? Could you provide some info to help us reproduce this issue?
>
> `gorm` I meant, sorry for the confusion, you can assign this to me, I'll give it a try.
Welcome. I will help you about this as possible.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418599016
I found another problem from the example https://github.com/karanpratapsingh/tutorials/tree/master/go/gorm.
The example replies on `pg_catalog`, which is not well supported by ShardingSphere-Proxy for now.
When the client connected to PostgreSQL directly.
The Oid is incorrect when connected to ShardingSphere-Proxy.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1416786364
@TeslaCN running into compilation issues after rebasing with latest master. Any thoughts?
```
/work/shardingsphere/distsql/parser/src/main/java/org/apache/shardingsphere/distsql/parser/core/kernel/KernelDistSQLParser.java:[22,56] package org.apache.shardingsphere.distsql.parser.autogen does not exist
```
Tried `mvn clean` and `mvn install`, no luck.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418776517
The PostgreSQL Proxy didn't set `standard_conforming_strings=on`. Beginning in PostgreSQL 9.1, the default of `standard_conforming_strings` is on (prior releases defaulted to off).
https://www.postgresql.org/docs/current/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION
If we add the following
```java
context.write(new PostgreSQLParameterStatusPacket("standard_conforming_strings", "on"));
```
to
https://github.com/apache/shardingsphere/blob/a74a8f5be32a43cc5617aab1001fd97cb665e29f/proxy/frontend/postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/authentication/PostgreSQLAuthenticationEngine.java#L104-L108
The following code could circumvent the problem like `cannot convert true/false to Varbit/Varchar`.
```golang
dbURL := "postgres://postgres:postgres@localhost:55432/postgres"
db, err := gorm.Open(postgres.New(postgres.Config{DSN: dbURL, PreferSimpleProtocol: true}), &gorm.Config{})
```
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415682989
> @TeslaCN thanks for the inputs. This sql statement is being generated using `goorm`, I'm not sure if we can enforce `goorm` to add a type cast. I will check at the `goorm` side if we an do this. Do you think we should address it in the proxy code eventually?
Of course we should fix it. I think this is a bug.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1420152211
@TeslaCN raised a PR, kindly check when you get a chance.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1423707377
> > I think the type cast issue is related to the following codes. If we invoked `setObject` with a String, I guess the type cast error will occur.
> > https://github.com/apache/shardingsphere/blob/1fc916e078b922abf336d247dc526f4ed7794b9a/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/statement/JDBCBackendStatement.java#L58-L65
>
> things seems to fail even before this code block is hit. Following statement is being executed before actual insert by the database driver and it fails with `cannot convert false to Varbit` error
>
> ```
> begin
> INSERT INTO "books" ("title","author","desc","published") VALUES ($1,$2,$3,$4) RETURNING "id"
> INSERT INTO "books" ("title","author","desc","published") VALUES (?,?,?,?) RETURNING "id"
> rollback
> ```
About the error `cannot convert false to Varbit`.
You may refer to:
https://github.com/jackc/pgtype/blob/e26c6b4e3d1c1d25a086e5da106165f1819d62e2/varbit.go#L17-L19
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1423709713
> @TeslaCN Can you please help point out the file where the response to a query is returned back to the proxy client? I want to see what is returned to app from proxy when above statement is executed.
Proxy executes statement here.
https://github.com/apache/shardingsphere/blob/c69b3d939b882700ef6956c373e670f20f207800/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/executor/callback/ProxyJDBCExecutorCallback.java
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN closed issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN closed issue #23964: Boolean type giving error in ShardingSphere while insert
URL: https://github.com/apache/shardingsphere/issues/23964
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1422897890
@TeslaCN thanks for reopening this, the fix didn't address the problem. We are seeing cast related error for boolean and datetime data types. Any thoughts what might be causing the problem?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1423457440
I think the type cast issue is related to the following codes. If we invoked `setObject` with a String, I guess the type cast error will occur.
https://github.com/apache/shardingsphere/blob/1fc916e078b922abf336d247dc526f4ed7794b9a/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/statement/JDBCBackendStatement.java#L58-L65
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] susongyan commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "susongyan (via GitHub)" <gi...@apache.org>.
susongyan commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1565158500
>
OK， i'd open a new issue
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418035439
> > > Hi @surukonda About the `goorm`, did you mean this [gorm](https://gorm.io/) or something else? Could you provide some info to help us reproduce this issue?
> >
> >
> > `gorm` I meant, sorry for the confusion, you can assign this to me, I'll give it a try.
>
> in order to reproduce this issue you can try using [this](https://dev.to/karanpratapsingh/connecting-to-postgresql-using-gorm-24fj) go example by adding a boolean column in `book.go` file under `models` folder. This example works fine when directly connected to `postgres` but fails when connected via`proxy`
Thanks for your input.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1416791803
> > Hi @surukonda About the `goorm`, did you mean this [gorm](https://gorm.io/) or something else? Could you provide some info to help us reproduce this issue?
>
> `gorm` I meant, sorry for the confusion, you can assign this to me, I'll give it a try.
in order to reproduce this issue you can try using [this](https://dev.to/karanpratapsingh/connecting-to-postgresql-using-gorm-24fj) go example by adding a boolean column in `book.go` file under `models` folder. This example works fine when directly connected to `postgres` but fails when connected via`proxy`
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418726349
> I found another problem when using the example https://github.com/karanpratapsingh/tutorials/tree/master/go/gorm.
>
> The example replies on `pg_catalog`, which is not well supported by ShardingSphere-Proxy for now.
>
> When the client connected to PostgreSQL directly. 
>
> The Oid is incorrect when connected to ShardingSphere-Proxy. 
Yes, is there a way to circumvent this problem? I'm also debugging this issue at my side, the INSERT statement doesn't even seems to log in the shardingshpere logs, it simply gives a `cannot convert true/false to Varbit/Varchar`
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418722654
> > @TeslaCN running into compilation issues after rebasing with latest master. Any thoughts?
> > ```
> > /work/shardingsphere/distsql/parser/src/main/java/org/apache/shardingsphere/distsql/parser/core/kernel/KernelDistSQLParser.java:[22,56] package org.apache.shardingsphere.distsql.parser.autogen does not exist
> > ```
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Tried `mvn clean` and `mvn install`, no luck.
>
> Please make sure you are using Java 11+. We have upgraded antlr to 4.10.1 recently, which required Java 11 for compiling.
Thanks @TeslaCN , I'm able to compile now.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1422900806
```
[ERROR] 2023-02-08 12:30:46.717 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.postgresql.util.PSQLException: ERROR: column "custom_released" is of type boolean but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 310
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:43)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:70)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:47)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:86)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:65)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:133)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:119)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:113)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:74)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:222)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:184)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:149)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:178)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.Portal.bind(Portal.java:103)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:53)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1423570806
> I think the type cast issue is related to the following codes. If we invoked `setObject` with a String, I guess the type cast error will occur.
>
> https://github.com/apache/shardingsphere/blob/1fc916e078b922abf336d247dc526f4ed7794b9a/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/statement/JDBCBackendStatement.java#L58-L65
ok, let me have a look at it. I will debug and let you know.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418033260
> @TeslaCN running into compilation issues after rebasing with latest master. Any thoughts?
>
> ```
> /work/shardingsphere/distsql/parser/src/main/java/org/apache/shardingsphere/distsql/parser/core/kernel/KernelDistSQLParser.java:[22,56] package org.apache.shardingsphere.distsql.parser.autogen does not exist
> ```
>
> Tried `mvn clean` and `mvn install`, no luck.
Please make sure you are using Java 11+. We have upgraded antlr to 4.10.1 recently, which required Java 11 for compiling.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415769896
> Hi @surukonda About the `goorm`, did you mean this [gorm](https://gorm.io/) or something else? Could you provide some info to help us reproduce this issue?
`gorm` I meant, sorry for the confusion
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1423666428
> I think the type cast issue is related to the following codes. If we invoked `setObject` with a String, I guess the type cast error will occur.
>
> https://github.com/apache/shardingsphere/blob/1fc916e078b922abf336d247dc526f4ed7794b9a/proxy/backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/statement/JDBCBackendStatement.java#L58-L65
things seems to fail even before this code block is hit. Following statement is being executed before actual insert by the database driver and it fails with `` error
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1423700822
@TeslaCN Can you please help point out the file where the response to a query is returned back to the proxy client? I want to see what is returned to app from proxy when above statement is executed.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1422899459
```
org.postgresql.util.PSQLException: ERROR: column "created_at" 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: 123
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:181)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:170)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:43)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:70)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:47)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:86)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:65)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:133)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:119)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:113)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:74)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:222)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:184)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:149)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:178)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.Portal.bind(Portal.java:103)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:53)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.extended.PostgreSQLAggregatedCommandExecutor.execute(PostgreSQLAggregatedCommandExecutor.java:41)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] susongyan commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "susongyan (via GitHub)" <gi...@apache.org>.
susongyan commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1564248668
i meet this case using node typeorm update ,too
it seems like pg's parameterMarkerIndex may not be in order as jdbc's treat
generated sql is like :
`UPDATE "t_alert_rule" SET "alert_persons" = $2, "update_time" = CURRENT_TIMESTAMP WHERE "id" IN ($1) RETURNING "update_time” `
and parseExecutor convert it to jdbc style
`UPDATE "t_alert_rule" SET "alert_persons" = ?, "update_time" = CURRENT_TIMESTAMP WHERE "id" IN (?) RETURNING "update_time” `
eventually the actual parameters is miss match with index； re order the actual parameters' order in 'bind' phase seems works
@TeslaCN
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1564554135
> i meet this case using node typeorm update ,too
>
> it seems like pg's parameterMarkerIndex may not be in order as jdbc's treat
>
> （1）generated sql is like : `UPDATE "t_alert_rule" SET "alert_persons" = $2, "update_time" = CURRENT_TIMESTAMP WHERE "id" IN ($1) RETURNING "update_time” `
>
> （2） and parseExecutor convert it to jdbc style `UPDATE "t_alert_rule" SET "alert_persons" = ?, "update_time" = CURRENT_TIMESTAMP WHERE "id" IN (?) RETURNING "update_time” `
>
> （3） parameters readed in 'bind' is json: ['test'] bigint : 1
>
> eventually the actual parameters is miss match with index； re order the actual parameters' order in 'bind' phase may works
>
> @TeslaCN
I think this is another issue.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418778282
> The PostgreSQL Proxy didn't set `standard_conforming_strings=on`. Beginning in PostgreSQL 9.1, the default of `standard_conforming_strings` is on (prior releases defaulted to off).
>
> https://www.postgresql.org/docs/current/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION
>
> If we add the following
>
> ```java
> context.write(new PostgreSQLParameterStatusPacket("standard_conforming_strings", "on"));
> ```
>
> to
>
> https://github.com/apache/shardingsphere/blob/a74a8f5be32a43cc5617aab1001fd97cb665e29f/proxy/frontend/postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/authentication/PostgreSQLAuthenticationEngine.java#L104-L108
>
> The following code could circumvent the problem like `cannot convert true/false to Varbit/Varchar`.
>
> ```go
> dbURL := "postgres://postgres:postgres@localhost:55432/postgres"
>
> db, err := gorm.Open(postgres.New(postgres.Config{DSN: dbURL, PreferSimpleProtocol: true}), &gorm.Config{})
> ```
Hi @surukonda
Would you like to submit a PR to add this parameter status to Proxy?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1418789951
> > The PostgreSQL Proxy didn't set `standard_conforming_strings=on`. Beginning in PostgreSQL 9.1, the default of `standard_conforming_strings` is on (prior releases defaulted to off).
> > https://www.postgresql.org/docs/current/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION
> > If we add the following
> > ```java
> > context.write(new PostgreSQLParameterStatusPacket("standard_conforming_strings", "on"));
> > ```
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > to
> > https://github.com/apache/shardingsphere/blob/a74a8f5be32a43cc5617aab1001fd97cb665e29f/proxy/frontend/postgresql/src/main/java/org/apache/shardingsphere/proxy/frontend/postgresql/authentication/PostgreSQLAuthenticationEngine.java#L104-L108
> >
> > The following code could circumvent the problem like `cannot convert true/false to Varbit/Varchar`.
> > ```go
> > dbURL := "postgres://postgres:postgres@localhost:55432/postgres"
> >
> > db, err := gorm.Open(postgres.New(postgres.Config{DSN: dbURL, PreferSimpleProtocol: true}), &gorm.Config{})
> > ```
>
> Hi @surukonda Would you like to submit a PR to add this parameter status to Proxy?
Hi @TeslaCN, I'll make this change and submit PR. Thanks for the suggestion!
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415557055
Welcome @surukonda
It seems that this is not caused by `psql`.
Could you try adding type cast on the value such as
```sql
INSERT INTO "mobile" ("created_at","tracking_id","custom_released") VALUES ($1, $2, $3::bool)
```
If you could debug the ShardingSphere-Proxy, you may try putting a breakpoint here:
https://github.com/apache/shardingsphere/blob/f8c4f616667d5678c44e98e0e6e4ce5d7d6dbfd4/db-protocol/postgresql/src/main/java/org/apache/shardingsphere/db/protocol/postgresql/packet/command/query/extended/bind/PostgreSQLComBindPacket.java#L79
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415608522
@TeslaCN thanks for the inputs. This sql statement is being generated using `goorm`, I'm not sure if we can enforce `goorm` to add a type cast. I will check at the `goorm` side if we an do this. Do you think we should address it in the proxy code eventually?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #23964: Boolean type giving error in ShardingSphere while insert
Posted by "TeslaCN (via GitHub)" <gi...@apache.org>.
TeslaCN commented on issue #23964:
URL: https://github.com/apache/shardingsphere/issues/23964#issuecomment-1415701861
Hi @surukonda
About the `goorm`, did you mean this [gorm](https://gorm.io/) or something else? Could you provide some info to help us reproduce this issue?
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org