Location>code7788 >text

5. Want to verify the correctness of SQL in the code?

Popularity：237 ℃/2025-02-22 00:10:37

1. Introduction

In our daily development, we may encounter the need to verify whether SQL is correct, that is, we need to check SQL.

To determine whether SQL is correct, generally include the following points:

1. Whether the column used in sql exists
 2. Is the sql syntax correct?
 3. Whether the operators/functions used in sql exist and are there any correct use

We can use the following SQL example to explore how to verify SQL using Calcite

select 
	,
    max()
from user u
         inner join role r on u.role_id = 
where  = 1
group by

2. Maven

<dependency>
    <groupId></groupId>
    <artifactId>calcite-core</artifactId>
    <version>1.37.0</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

3. Verification

First, verify the correctness of SQL in calcite by using theSqlValidatorclass is validated, butSqlValidatoris an interface, usually through(...)The method is instantiated as follows:

public static SqlValidatorWithHints newValidator(SqlOperatorTable opTab,
                                                 SqlValidatorCatalogReader catalogReader, 
                                                 RelDataTypeFactory typeFactory, 
                                                  config) 
{
    return new SqlValidatorImpl(opTab, catalogReader, typeFactory, config);    
}
  • SqlOperatorTable: Used to provide the operators (SqlOperator) and functions (SqlFunction) required for SQL verification, for example: >, <, = or max(), in()
  • SqlValidatorCatalogReader: Used to provide the metadata information required for verification, for example: schema, table, column
  • RelDataTypeFactory: A factory class that processes data types, used to provide the creation and conversion of types, java types and collection types. For different interface forms, calcite supports two implementations of SQL and Java (SqlTypeFactoryImpl and JavaTypeFactoryImpl). Of course, users can expand on their own according to different situations.
  • : You can customize some configurations, such as whether to enable implicit type conversion, whether to enable SQL rewrite, etc.

3.1 Creating SqlValidator

createSqlValidatorBefore, we need to instantiate the above four parameter objects. Fortunately, calcite provides the default implementation of corresponding properties, which makes it easy for us to create.SqlValidatorObject

SqlValidator validator = (
     (),
     catalogReader, // catalog information needs to be created manually by yourself
     new SqlTypeFactoryImpl(),
     );

Except hereSqlValidatorCatalogReaderObjects need to be created extra by themselves. If there are no other special requirements, we can use the default implementation provided by calcite.

Let's talk about it here.SqlValidatorCatalogReaderHow to create an object

firstSqlValidatorCatalogReaderIf we use it to provide the catalog information required for verification, we need to provide the catalog information (because calcite needs to verify metadata, such as whether the table or field exists, and if metadata is not provided, how can we talk about verification?)

createSqlValidatorCatalogReaderThere are two ways:

  1. Through the data source, we know the server information of executing SQL, and give the connection information to Calcite, so that Calcite can obtain meta information and verify it by itself. That is, at this time, we need to connect to DB to verify it.

    @SneakyThrows
private static CalciteCatalogReader createCatalogReaderWithDataSource() {
    Connection connection = ("jdbc:calcite:");
    CalciteConnection calciteConnection = ();
    SchemaPlus rootSchema = ();
    DataSource dataSource = (
        "jdbc:mysql://localhost:3306/test",
        "",
        "root",
        "123456"
    );
    JdbcSchema jdbcSchema = (rootSchema, "my_mysql", dataSource, null, null);
    ("my_mysql", jdbcSchema);
    ("my_mysql");
    CalciteServerStatement statement = ().unwrap();
     prepareContext = ();
    SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl();

    return new CalciteCatalogReader(
        (),
        (),
        factory,
        ());
}

  2. Manually add catalog information, so you can verify it without connecting to the library

    private static CalciteCatalogReader createCatalogReaderWithMeta() {
    SchemaPlus rootSchema = (true);
    RelDataTypeSystem relDataTypeSystem = ;
    RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl(relDataTypeSystem);
    ("user", new AbstractTable() {
        @Override
        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
             builder = ();
            ("id", new BasicSqlType(relDataTypeSystem, ));
            ("name", new BasicSqlType(relDataTypeSystem, ));
            ("age", new BasicSqlType(relDataTypeSystem, ));
            ("sex", new BasicSqlType(relDataTypeSystem, ));
            ("role_id", new BasicSqlType(relDataTypeSystem, ));
            return ();
        }
    });
    ("role", new AbstractTable() {
        @Override
        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
             builder = ();
            ("id", new BasicSqlType(relDataTypeSystem, ));
            ("name", new BasicSqlType(relDataTypeSystem, ));
            return ();
        }
    });
    CalciteConnectionConfig connectionConfig = ;

    return new CalciteCatalogReader(
        (rootSchema),
        (rootSchema).path(null),
        typeFactory,
        connectionConfig);
}

    OK, create it hereSqlValidatorAll required parameters are ready, but the required parameters are not SQL strings but(SqlNode topNode), SoSqlNodeHow to create it?

3.2 Analyzing Sql

SqlNodeAs the name suggests, it is a SQL node object, directly passedSqlParserObject creation, as follows

config = ()
                                      // Analysis factory
                                      .withParserFactory()
                                      // It can also be directly set as the lexical analyzer for the corresponding database
                                      // .withLex()
                                      // Insensitive to upper and lower case
                                      .withCaseSensitive(false)
                                      // Quotation symbol is backquoted
                                      .withQuoting(Quoting.BACK_TICK)
                                      // Unquoted identifiers are not processed during parsing
                                      .withUnquotedCasing()
                                      // Quoted identifiers are not processed during parsing
                                      .withQuotedCasing()
                                      // Use default syntax rules
                                      .withConformance();
 // sql parser
 final SqlParser parser = (SQL, config);
 // Convert sql to calcite SqlNode
 SqlNode sqlNode = ();

3.3 Perform verification

Through the above steps we have been able to createSqlValidatorObjects and can create their validation requiredSqlNodeObjects are actually very simple. As long as there is no error reported during verification, sql is correct.

try{
  // Verify sql
  (sqlNode);
  ("sql is valid");
 }
 catch (Exception e) {
  ("sql is invalid", e);
 }

4. Complete verification code

4.1 Verification via SqlValidator

package ;

 import ;
 import .slf4j.Slf4j;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;

 import ;
 import ;
 import ;

 @Slf4j
 public class SqlValidatorTest {
     private static final String SQL = "select , max() from `user` u inner join role r on u.role_id = where = 1 group by ";

     @Test
     @SneakyThrows
     public void given_sql_and_meta_then_validate_sql() {
          config = ()
                                            // Analysis factory
                                            .withParserFactory()
                                            // It can also be directly set as the lexical analyzer for the corresponding database
                                            // .withLex()
                                            // Insensitive to upper and lower case
                                            .withCaseSensitive(false)
                                            // Quotation symbol is backquoted
                                            .withQuoting(Quoting.BACK_TICK)
                                            // Unquoted identifiers are not processed during parsing
                                            .withUnquotedCasing()
                                            // Quoted identifiers are not processed during parsing
                                            .withQuotedCasing()
                                            // Use default syntax rules
                                            .withConformance();
         // sql parser
         final SqlParser parser = (SQL, config);
         // Convert SQL to Calcite's SqlNode
         SqlNode sqlNode = ();
         // Create SqlValidator for verification
         SqlValidator validator = (
                 (),
                 // Use the method of providing meta information directly
                 createCatalogReaderWithMeta(),
                 // Use the method of providing data sources
                 //createCatalogReaderWithDataSource(),
                 new SqlTypeFactoryImpl(),
                 );
         try{
             // Verify sql
             (sqlNode);
             ("sql is valid");
         }
         catch (Exception e) {
             ("sql is invalid", e);
         }
     }

     private static CalciteCatalogReader createCatalogReaderWithMeta() {
         SchemaPlus rootSchema = (true);
         RelDataTypeSystem relDataTypeSystem = ;
         RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl(relDataTypeSystem);
         ("user", new AbstractTable() {
                     @Override
                     public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                          builder = ();
                         ("id", new BasicSqlType(relDataTypeSystem, ));
                         ("name", new BasicSqlType(relDataTypeSystem, ));
                         ("age", new BasicSqlType(relDataTypeSystem, ));
                         ("sex", new BasicSqlType(relDataTypeSystem, ));
                         ("role_id", new BasicSqlType(relDataTypeSystem, ));
                         return ();
                     }
                 });
         ("role", new AbstractTable() {
             @Override
             public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                  builder = ();
                 ("id", new BasicSqlType(relDataTypeSystem, ));
                 ("name", new BasicSqlType(relDataTypeSystem, ));
                 return ();
             }
         });
         CalciteConnectionConfig connectionConfig = ;

         return new CalciteCatalogReader(
                 (rootSchema),
                 (rootSchema).path(null),
                 typeFactory,
                 connectionConfig);
     }

     @SneakyThrows
     private static CalciteCatalogReader createCatalogReaderWithDataSource() {
         Connection connection = ("jdbc:calcite:");
         CalciteConnection calciteConnection = ();
         SchemaPlus rootSchema = ();
         DataSource dataSource = (
                 "jdbc:mysql://localhost:3306/test",
                 "",
                 "root",
                 "123456"
         );
         JdbcSchema jdbcSchema = (rootSchema, "my_mysql", dataSource, null, null);
         ("my_mysql", jdbcSchema);
         ("my_mysql");
         CalciteServerStatement statement = ().unwrap();
          prepareContext = ();
         SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl();

         return new CalciteCatalogReader(
                 (),
                 (),
                 factory,
                 ());
     }
 }

4.2 Verification using Planner object

In fact, the method is using the SqlValidator object for verification

package ;

 import ;
 import .slf4j.Slf4j;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;
 import ;

 @Slf4j
 public class SqlValidatorWithPlannerTest {
     private static final String SQL = "select , max() from `user` u inner join role r on u.role_id = where = 1 group by ";

     @Test
     @SneakyThrows
     public void given_sql_and_meta_then_validate_sql() {
         // Create Calcite configuration
         FrameworkConfig config = createFrameworkConfig();
         // Create Planner
         Planner planner = (config);
         // parse SQL
         final SqlNode parse = (SQL);

         try {
             // Get SqlValidator for verification
             (parse);
             ("sql is valid");
         } catch (ValidationException e) {
             ("sql is invalid", e);
         }
     }

     private static FrameworkConfig createFrameworkConfig() {
         SchemaPlus rootSchema = (true);
         RelDataTypeSystem relDataTypeSystem = ;
         ("user", new AbstractTable() {
             @Override
             public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                  builder = ();
                 ("id", new BasicSqlType(relDataTypeSystem, ));
                 ("name", new BasicSqlType(relDataTypeSystem, ));
                 ("age", new BasicSqlType(relDataTypeSystem, ));
                 ("sex", new BasicSqlType(relDataTypeSystem, ));
                 ("role_id", new BasicSqlType(relDataTypeSystem, ));
                 return ();
             }
         });
         ("role", new AbstractTable() {
             @Override
             public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                  builder = ();
                 ("id", new BasicSqlType(relDataTypeSystem, ));
                 ("name", new BasicSqlType(relDataTypeSystem, ));
                 return ();
             }
         });
          config = ()
                                            .withParserFactory()
                                            .withQuoting(Quoting.BACK_TICK)
                                            .withCaseSensitive(false)
                                            .withUnquotedCasing()
                                            .withQuotedCasing()
                                            .withConformance();
         return Frameworks
                 .newConfigBuilder()
                 .defaultSchema(rootSchema)
                 .parserConfig(config)
                 .build();
     }
 }
<Previous Next>