SQL Compatibility
#
SQL is the standard language used by users to communicate with databases. The SQL parsing engine is responsible for parsing SQL strings into abstract syntax trees that SphereEx-DBPlusEngine can understand and implement with its incremental functionality. Currently, SphereEx-DBPlusEngine supports MySQL, PostgreSQL, SQLServer, Oracle, openGauss, Presto and SQL92 compliant SQL dialects. However, due to the complexity of SQL syntax, there are still a few unsupported SQL statements.
In addition, SphereEx-DBPlusEngine provides the ability for distributed collaboration among databases and abstracts some database features to the upper layer for unified management, which reduces the user’s difficulty in using them. Therefore, for the features provided uniformly, the native SQL will no longer be sent down to the database to indicate that the operation is not supported. Instead, users can use the method provided by SphereEx-DBPlusEngine.
Incompatible Cases
#
MySQL
#
PostgreSQL
#
| SQL |
|---|
| CREATE TABLE agg_data_2k AS SELECT g FROM generate_series(0,1999) g |
| CREATE SERVER alt_ferv1 FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE STATISTICS alt_stat1 ON a,b FROM alt_regress_1 |
| ALTER TABLE alterlock SET(toast.autovacuum_enabled=off) |
| CREATE PUBLICATION pub1 FOR TABLE alter1.t1,ALL TABLES IN SCHEMA alter2 |
openGauss
#
| SQL |
|---|
| CREATE TABLE agg_data_2k AS SELECT g FROM generate_series(0,1999) g |
| CREATE FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE SERVER alt_ferv1 FOREIGN DATA WRAPPER alt_fdw1 |
| CREATE STATISTICS alt_stat1 ON a,b FROM alt_regress_1 |
| ALTER TABLE alterlock SET(toast.autovacuum_enabled=off) |
| CREATE PUBLICATION pub1 FOR TABLE alter1.t1,ALL TABLES IN SCHEMA alter2 |
Presto
#
| SQL |
|---|
| CREATE TABLE orders_by_date COMMENT ‘Summary of orders by date’ WITH (format = ‘ORC’) AS SELECT orderdate, sum(totalprice) AS price FROM orders GROUP BY orderdate |
| CALL test(name => ‘apple’, id => 123) |
| ALTER TABLE IF EXISTS users RENAME column IF EXISTS id to user_id |
| DEALLOCATE PREPARE my_query |
| DESCRIBE table_name |
| REVOKE GRANT OPTION FOR SELECT ON nation FROM ROLE PUBLIC |