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 2020/08/23 14:38:31 UTC
[GitHub] [shardingsphere] sandynz opened a new issue #7010: PostgreSQL dedicated column missed in t_order cause GeneralDQLIT.assertExecuteQuery failed
sandynz opened a new issue #7010:
URL: https://github.com/apache/shardingsphere/issues/7010
## Bug Report
### Which version of ShardingSphere did you use?
master branch, 5.0.0-RC1-SNAPSHOT
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Related dql-test-case pass.
### Actual behavior
Test failed, exception thrown:
```
org.postgresql.util.PSQLException: ERROR: column "rule" does not exist
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.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.apache.shardingsphere.driver.executor.StatementExecutor$1.createQueryResult(StatementExecutor.java:81)
at org.apache.shardingsphere.driver.executor.StatementExecutor$1.executeSQL(StatementExecutor.java:77)
at org.apache.shardingsphere.driver.executor.StatementExecutor$1.executeSQL(StatementExecutor.java:73)
at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.impl.DefaultSQLExecutorCallback.execute0(DefaultSQLExecutorCallback.java:75)
at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.impl.DefaultSQLExecutorCallback.execute(DefaultSQLExecutorCallback.java:57)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorKernel.syncExecute(ExecutorKernel.java:99)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorKernel.parallelExecute(ExecutorKernel.java:95)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorKernel.execute(ExecutorKernel.java:78)
at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.SQLExecutor.execute(SQLExecutor.java:66)
at org.apache.shardingsphere.infra.executor.sql.resourced.jdbc.executor.SQLExecutor.execute(SQLExecutor.java:50)
at org.apache.shardingsphere.driver.executor.StatementExecutor.executeQuery(StatementExecutor.java:85)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:127)
at org.apache.shardingsphere.dbtest.engine.dql.GeneralDQLIT.assertExecuteQueryForStatement(GeneralDQLIT.java:70)
at org.apache.shardingsphere.dbtest.engine.dql.GeneralDQLIT.assertExecuteQuery(GeneralDQLIT.java:60)
```
### Reason analyze (If you can)
`rule` and `start_point` columns doesn't exist in `t_order` table.
Since they're special column type, we can't add these columns for all types of database. Possible solution:
- Make `schema.xml > table-create > sql` support `db-types` definition, e.g. `db-types=PostgreSQL`, then the same name tables that have different columns will be created for different database.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### 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] sandynz commented on issue #7010: PostgreSQL dedicated column missed in t_order cause GeneralDQLIT.assertExecuteQuery failed
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #7010:
URL: https://github.com/apache/shardingsphere/issues/7010#issuecomment-683266478
After some testing, I found solution 1 is not suitable, since added table columns will affect other test cases (columnCount assertion), there're too much related test cases need to update.
Considering possible solution 2:
If it could add columns before executing sql for every test case, and also drop columns after executing sql, the table structure change will be isolated to current test case.
Example test case:
```
<dql-test-case
sql="SELECT * FROM t_order WHERE rule = ?::jsonb AND start_point=ST_GeographyFromText('SRID=4326;POINT('||?||' '||?||')') AND user_id = ? AND order_id = ?"
column-create="CREATE EXTENSION postgis; ALTER TABLE t_order ADD COLUMN rule ...;"
column-drop="ALTER TABLE t_order DROP COLUMN rule"
db-types="PostgreSQL">
```
`column-create` and `column-drop` are added attributes.
It might need some underlying base classes change.
----------------------------------------------------------------
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] sandynz edited a comment on issue #7010: PostgreSQL dedicated column missed in t_order cause GeneralDQLIT.assertExecuteQuery failed
Posted by GitBox <gi...@apache.org>.
sandynz edited a comment on issue #7010:
URL: https://github.com/apache/shardingsphere/issues/7010#issuecomment-683409336
Another thing to memtion:
It need to install [PostGIS](https://postgis.net), and enable it in PostgreSQL.
> PostGIS is an optional extension that must be enabled in EVERY database you want to use it in.
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
`CREATE EXTENSION postgis` could not be executed repeatedly in the same database.
----------------------------------------------------------------
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] tuohai666 commented on issue #7010: PostgreSQL dedicated column missed in t_order cause GeneralDQLIT.assertExecuteQuery failed
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #7010:
URL: https://github.com/apache/shardingsphere/issues/7010#issuecomment-683687342
This test case is for parser test only, and should be removed from integration test.
----------------------------------------------------------------
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] jingshanglu closed issue #7010: PostgreSQL dedicated column missed in t_order cause GeneralDQLIT.assertExecuteQuery failed
Posted by GitBox <gi...@apache.org>.
jingshanglu closed issue #7010:
URL: https://github.com/apache/shardingsphere/issues/7010
----------------------------------------------------------------
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] sandynz commented on issue #7010: PostgreSQL dedicated column missed in t_order cause GeneralDQLIT.assertExecuteQuery failed
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #7010:
URL: https://github.com/apache/shardingsphere/issues/7010#issuecomment-683409336
Another thing to memtion:
It need to install [PostGIS](https://postgis.net), and enable it in PostgreSQL.
> PostGIS is an optional extension that must be enabled in EVERY database you want to use it in.
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;
----------------------------------------------------------------
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