包含 Case 函数的SQL 执行报空指针错误

[zouyanjian]

https://github.com/lealone/Lealone/blob/6c343cec3be0a04d90001886a55124caea5ff973/lealone-sql/src/main/java/org/lealone/sql/LealoneSQLParser.java#L2767

这个位置Case function 被设置为空会导致

SystemFunction.java 中的 getValueN(ServerSession session, Expression[] args, Value[] values)方法的中的下面这个调用会出空指针异常，最终导致包含case 函数的语句都执行不成功， Value v0 = getNullOrValue(session, args, values, 0);

建议FIx LealoneSQLParser.java#L2767 的
function.setParameter(0, null); 修改为 function.setParameter(0, ValueExpression.get(ValueBoolean.get(true)));

[zouyanjian]

补充bug 重现的 Test case

@Test public void testGetMeta() throws SQLException { setEmbedded(true); setInMemory(true); init(); createTable("demo"); String catalog = conn.getCatalog(); String schema = conn.getSchema();

    try (ResultSet tableRs = metaData.getTables(catalog, schema, null, new String[]{"STANDARD_TABLE"})) {
        while (tableRs.next()) {
            String table_type = tableRs.getString("TABLE_TYPE");
            if (!table_type.equalsIgnoreCase("TABLE") && !table_type.equalsIgnoreCase("STANDARD_TABLE")) {
                continue;
            }

            String tableName = tableRs.getString("TABLE_NAME");

            try (ResultSet coloumsRs = metaData.getColumns(catalog, schema, tableName, null)) {
                while (coloumsRs.next()) {
                    String col_name = coloumsRs.getString("COLUMN_NAME");
                }
            }
        }
    }

}

[codefollower]

LealoneSQLParser 那里逻辑是正常的，org.lealone.sql.expression.function.BuiltInFunction.getNullOrValue 少了 null 判断， CASE 函数一直没有测试用例，代码实现有问题，刚刚已经补充完整的测试用例。

感谢报告问题。

[zouyanjian]

有引入了新的bug,应该是22号的代码引入的

org.lealone.common.exceptions.JdbcSQLException: General error: "java.lang.NullPointerException: Cannot invoke ""org.lealone.sql.expression.Expression.accept(org.lealone.sql.expression.visitor.ExpressionVisitor)"" because ""e2"" is null"; SQL statement:
SELECT TABLE_CATALOG TABLE_CAT, TABLE_SCHEMA TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, NUMERIC_SCALE DECIMAL_DIGITS, NUMERIC_PRECISION_RADIX NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEFAULT COLUMN_DEF, DATA_TYPE SQL_DATA_TYPE, ZERO() SQL_DATETIME_SUB, CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, ORDINAL_POSITION, IS_NULLABLE IS_NULLABLE, CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_CATALOG, CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_SCHEMA, CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_TABLE, SOURCE_DATA_TYPE, CASE WHEN SEQUENCE_NAME IS NULL THEN CAST(? AS VARCHAR) ELSE CAST(? AS VARCHAR) END IS_AUTOINCREMENT, CAST(SOURCE_DATA_TYPE AS VARCHAR) SCOPE_CATLOG FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG LIKE ? ESCAPE ? AND TABLE_SCHEMA LIKE ? ESCAPE ? AND TABLE_NAME LIKE ? ESCAPE ? AND COLUMN_NAME LIKE ? ESCAPE ? ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION [50000-0]

    at org.lealone.common.exceptions.DbException.getJdbcSQLException(DbException.java:340)
    at org.lealone.common.exceptions.DbException.get(DbException.java:161)
    at org.lealone.common.exceptions.DbException.convert(DbException.java:292)
    at org.lealone.sql.executor.YieldableBase.handleException(YieldableBase.java:162)
    at org.lealone.sql.executor.YieldableBase.run(YieldableBase.java:115)
    at org.lealone.db.session.ServerSession$YieldableCommand.run(ServerSession.java:1501)
    at org.lealone.server.Scheduler.executeNextStatement(Scheduler.java:235)
    at org.lealone.server.Scheduler.run(Scheduler.java:106)
Caused by: java.lang.NullPointerException: Cannot invoke "org.lealone.sql.expression.Expression.accept(org.lealone.sql.expression.visitor.ExpressionVisitor)" because "e2" is null
    at org.lealone.sql.expression.visitor.BooleanExpressionVisitor.visitFunction(BooleanExpressionVisitor.java:200)
    at org.lealone.sql.expression.visitor.DeterministicVisitor.visitFunction(DeterministicVisitor.java:41)
    at org.lealone.sql.expression.visitor.DeterministicVisitor.visitFunction(DeterministicVisitor.java:15)
    at org.lealone.sql.expression.function.BuiltInFunction.accept(BuiltInFunction.java:260)
    at org.lealone.sql.expression.visitor.BooleanExpressionVisitor.visitAlias(BooleanExpressionVisitor.java:50)
    at org.lealone.sql.expression.visitor.BooleanExpressionVisitor.visitAlias(BooleanExpressionVisitor.java:41)
    at org.lealone.sql.expression.Alias.accept(Alias.java:107)
    at org.lealone.sql.expression.visitor.BooleanExpressionVisitor.visitSelect(BooleanExpressionVisitor.java:223)
    at org.lealone.sql.expression.visitor.DeterministicVisitor.visitSelect(DeterministicVisitor.java:46)
    at org.lealone.sql.expression.visitor.DeterministicVisitor.visitSelect(DeterministicVisitor.java:15)
    at org.lealone.sql.query.Select.accept(Select.java:981)
    at org.lealone.sql.query.QueryResultCache.getResult(QueryResultCache.java:50)
    at org.lealone.sql.query.YieldableSelect.createQueryOperator(YieldableSelect.java:109)
    at org.lealone.sql.query.YieldableSelect.startInternal(YieldableSelect.java:77)
    at org.lealone.sql.executor.YieldableBase.start(YieldableBase.java:132)
    at org.lealone.sql.executor.YieldableBase.run(YieldableBase.java:101)
    ... 3 more

    at org.lealone.net.TransferConnection.parseError(TransferConnection.java:66)
    at org.lealone.net.TcpClientConnection.handleResponse(TcpClientConnection.java:101)
    at org.lealone.net.TransferConnection.handle(TransferConnection.java:123)
    at org.lealone.net.nio.NioEventLoop.read(NioEventLoop.java:208)
    at org.lealone.net.nio.NioEventLoopClient.run(NioEventLoopClient.java:74)
    at org.lealone.net.nio.NioEventLoopClient.lambda$createNioEventLoop$0(NioEventLoopClient.java:46)
    at java.base/java.lang.Thread.run(Thread.java:833)

[codefollower]

那个是 BooleanExpressionVisitor.visitFunction 自身的 bug，把本该用 e2!=null，写错成 e!=null 了，触发条件是同一条 sql 执行到第二次，用 DeterministicVisitor 来确定一下第一次的缓存是否可用。DeterministicVisitor 继承自 BooleanExpressionVisitor。

感谢报告问题。

[zouyanjian]

执行测试了代码，问题修复了