Insert语句转换存在ROW关键词问题

tangyibo commented 4 years ago

您好，请教下下面的这个代码转换后的Insert语句存在row关键词，而当前大多数的oracle/mysql/postgresql数据库都不支持，请指教：

    public static void main(String[] args) {
        String sql="insert into dept (name, deptno) values ('a', 123) ,('b',345) ";
        SqlParser.Config config =SqlParser.configBuilder().setCaseSensitive(true).build();
        SqlParser parser = SqlParser.create(sql, config);
        SqlNode sqlNode =null;
        try {
                sqlNode=parser.parseStmt();
        }catch(SqlParseException e) {
            e.printStackTrace();
        }
        System.out.println(sqlNode.toSqlString(OracleSqlDialect.DEFAULT));
        System.out.println("========================");
        System.out.println(sqlNode.toSqlString(PostgresqlSqlDialect.DEFAULT));
        System.out.println("========================");
        System.out.println(sqlNode.toSqlString(MysqlSqlDialect.DEFAULT));
    }

结果输出为：

INSERT INTO "DEPT" ("NAME", "DEPTNO")
VALUES ROW('a', 123),
ROW('b', 345)
========================
INSERT INTO "DEPT" ("NAME", "DEPTNO")
VALUES ROW('a', 123),
ROW('b', 345)
========================
INSERT INTO `DEPT` (`NAME`, `DEPTNO`)
VALUES ROW('a', 123),
ROW('b', 345)

但我期望的是：

INSERT INTO "DEPT" ("NAME", "DEPTNO")
VALUES ('a', 123),('b', 345)
========================
INSERT INTO "DEPT" ("NAME", "DEPTNO")
VALUES ('a', 123),('b', 345)
========================
INSERT INTO `DEPT` (`NAME`, `DEPTNO`)
VALUES ('a', 123),('b', 345)

yuqi1129 commented 4 years ago

这是SqlRowOperator的unparse的默认行为，默认会将方法名ROW带进去，在calcite中 value后面是一个Row方法，如果不想输出，可以覆写该方法

tangyibo commented 4 years ago

@yuqi1129 多谢，按照您的覆写已经成功实现。

yuqi1129 commented 4 years ago

这个只是编码输出问题吧

[image: image.png]

如果是中文，会走到if 分支，采用uncode编码。如果需要改变默认行为，直接override

Yibo notifications@github.com 于2019年11月13日周三上午11:15写道：

还有个问题请教下：关于中文字符串编码问题，代码如下：

public static void main(String[] args) {
  String sql="select * from test_table where name = '王五' ";

    SqlParser.Config config = SqlParser.configBuilder().setCaseSensitive(true).build();

    SqlParser parser = SqlParser.create(sql, config);

    SqlNode sqlNode =null;

    try {

          sqlNode=parser.parseQuery();

    }catch(SqlParseException e) {

      e.printStackTrace();

    }

    System.out.println(sqlNode.toSqlString(OracleSqlDialect.DEFAULT));

    System.out.println("========================");

    System.out.println(sqlNode.toSqlString(PostgresqlSqlDialect.DEFAULT));

    System.out.println("========================");

    System.out.println(sqlNode.toSqlString(MysqlSqlDialect.DEFAULT));

    System.out.println("========================");

    System.out.println(sqlNode.toSqlString(MssqlSqlDialect.DEFAULT));
}

SQL转换后输出的结果为：

SELECT *

FROM "TEST_TABLE"

WHERE "NAME" = u&'\738b\4e94'

========================

SELECT *

FROM "TEST_TABLE"

WHERE "NAME" = u&'\738b\4e94'

========================

SELECT *

FROM TEST_TABLE

WHERE NAME = u&'\738b\4e94'

========================

SELECT *

FROM [TEST_TABLE]

WHERE [NAME] = u&'\738b\4e94'

而期望的结果为：

SELECT *

FROM "TEST_TABLE"

WHERE "NAME" ='王五'

========================

SELECT *

FROM "TEST_TABLE"

WHERE "NAME" = '王五'

========================

SELECT *

FROM TEST_TABLE

WHERE NAME = '王五'

========================

SELECT *

FROM [TEST_TABLE]

WHERE [NAME] = '王五'

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/yuqi1129/calcite-test/issues/2?email_source=notifications&email_token=ADYQDBANPPIRZVT6VNQMWWTQTNWO3A5CNFSM4JKDBYFKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOED4XMMI#issuecomment-553219633, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADYQDBCOUZHBIJ3AOOPIHVLQTNWO3ANCNFSM4JKDBYFA .

yuqi1129 / calcite-test

Insert语句转换存在ROW关键词问题 #2