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 2022/12/07 06:18:38 UTC
[GitHub] [shardingsphere] zhaojinchao95 commented on issue #22569: In the federation scenario, after creating a view, and select the view, the result is "ERROR: relation "select_view" does not exist on dn_6001_6002_6003".
zhaojinchao95 commented on issue #22569:
URL: https://github.com/apache/shardingsphere/issues/22569#issuecomment-1340425266
```
public class JDBCDriver2 {
static final String DB_URL = "jdbc:mysql://127.0.0.1:3307/sharding_db";
static final String USER = "root";
static final String PASS = "root";
public static void main(String[] args) throws Exception {
Statement statement = getConnection().createStatement();
List<String> sqlList = Arrays.asList("drop table if exists t_new_order;", "drop table if exists t_merchant;", "create table t_new_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date);",
"create table t_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null);");
String t_new_order = "insert into t_new_order values(1000, 10, 'init', 1, 'test', '2017-07-08')," +
"(1001, 10, 'init', 2, 'test', '2017-07-08')," +
"(2000, 20, 'init', 3, 'test', '2017-08-08')," +
"(2001, 20, 'init', 4, 'test', '2017-08-08')," +
"(1100, 11, 'init', 5, 'test', '2017-08-08')," +
"(1101, 11, 'init', 6, 'test', '2017-08-08')," +
"(2100, 21, 'finish', 7, 'test', '2017-08-08')," +
"(2101, 21, 'finish', 8, 'test', '2017-08-08')," +
"(1200, 12, 'finish', 9, 'finish', '2017-08-08')," +
"(1201, 12, 'finish', 10, 'test22', '2017-08-18')," +
"(2200, 22, 'finish', 11, 'test', '2017-08-18')," +
"(2201, 22, 'finish', 12, 'test', '2017-08-18')," +
"(1300, 13, 'finish', 13, '', '2017-08-18')," +
"(1301, 13, 'finish', 14, 'test', '2017-08-18')," +
"(2300, 23, 'finish ', 15, 'test', '2017-08-18')," +
"(2301, 23, 'finish', 16, 'test', '2017-08-18')," +
"(1400, 14, 'init', 17, '', '2017-08-18')," +
"(1401, 14, 'init', 18, 'test', '2017-08-18')," +
"(2400, 24, 'init', 19, 'test', '2017-08-18')," +
"(2401, 24, 'init', 20, 'test', '2017-08-18')," +
"(1500, 15, 'init', 1, '', '2017-08-28')," +
"(1501, 15, 'init', 2, 'test', '2017-08-28')," +
"(2500, 25, 'init', 3, 'test', '2017-08-28')," +
"(2501, 25, 'init', 4, 'test', '2017-08-28')," +
"(1600, 16, 'init', 5, 'test', '2017-08-28')," +
"(1601, 16, 'init', 6, '', '2017-08-28')," +
"(2600, 26, 'init', 7, 'test', '2017-08-28')," +
"(1700, 17, 'init', 9, 'test', '2017-08-28')," +
"(1701, 17, 'finish', 10, 'test', '2017-08-18')," +
"(2700, 27, 'finish', 11, 'test', '2017-08-18')," +
"(2701, 27, 'finish', 12, 'test', '2017-08-18')," +
"(1800, 18, 'finish', 13, 'test', '2017-08-18')," +
"(2800, 28, 'finish', 15, 'test', '2017-08-18')," +
"(2801, 28, 'finish', 16, 'test', '2017-08-18')," +
"(1900, 19, 'init', 17, 'test', '2017-08-18')," +
"(1901, 19, 'init', 18, 'test', '2017-08-18')," +
"(2900, 29, 'init', 19, 'test', '2017-08-18')," +
"(2901, 29, 'init', 20, 'test', '2017-08-18')," +
"(1902, 19, 'init', 17, 'test11', '2017-08-18')," +
"(1903, 19, 'init', 18, 'test12', '2017-08-18')," +
"(2902, 29, 'init', 19, 'test', '2017-08-18')," +
"(2903, 29, 'init', 20, 'test', '2017-08-18');";
String t_merchant = "insert into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08')," +
"(2, 86, 'haier', '86000002', '86100000002', '2017-08-08')," +
"(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08');";
for (String each : sqlList) {
statement.execute(each);
}
statement.execute(t_merchant);
statement.execute(t_new_order);
statement.execute("create view select_view as select all * from t_new_order where order_id>2800;");
statement.executeQuery("select * from select_view;");
statement.execute("drop view select_view;");
statement.execute("create view select_view as select distinct(user_id) from t_new_order order by user_id;");
statement.executeQuery("select * from select_view;");
// statement.execute("drop view select_view;");
// statement.execute("create view select_view as select distinct on (user_id) * from t_new_order order by user_id,order_id;");
// statement.executeQuery("select * from select_view;");
statement.execute("drop view select_view;");
statement.execute("create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_new_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 limit 10;");
statement.executeQuery("select * from select_view;");
statement.execute("drop view select_view;");
}
private static Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
return DriverManager.getConnection(DB_URL, USER, PASS);
}
}
```
I can't reproduce it.
--
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