APIServer return incorrect result for boolean values

[tobegit3hub]

Create table like this.

create table fzzz_test.request (user_code STRING NOT NULL, material_id STRING NOT NULL, distance DOUBLE, delivery_ta DOUBLE,  system_time TIMESTAMP NOT NULL);

Deploy the following SQL.

select system_time, hour(system_time) as system_time_hour, distance, delivery_ta, (delivery_ta >= 80 or (distance >= 500 and distance <= 3000)) as delivery_flag from request

Test with the following data.

{ "input": [ [ "foo", "foo", 1.01, 1.01, 1635247427000 ] ] }

Focus on the output the last value and get true which is expected to false.

[aceforeverd]

I can't reproduce inside sql engine

[tobegit3hub]

I have simplify the case and only reproduce with APIServer. Here are the steps to reproduce.

Create database and table.

create database db1;

use db1;

create table db1.t2 (col1 double, col2 string);

Deploy the SQL.

set @@SESSION.execute_mode='online'

deploy test1 select (col1 >= 80) as bool_col1 from db1.t2;

Test with APIServer.

curl http://127.0.0.1:9080/dbs/db1/deployments/test1 -X POST -d '{ "input": [ [ 1.01, "foo" ] ] }'
# {"code":0,"msg":"ok","data":{"data":[[true]]}}

If we test with OpenMLDB Java SDK for request mode, the result is expected.

import com._4paradigm.openmldb.sdk.SdkOption;
import com._4paradigm.openmldb.sdk.impl.SqlClusterExecutor;
import java.sql.*;

public class OnlineRequest {
    public static void main(String[] args) {
        try {
            String zkHost = "127.0.0.1:2181";
            String zkPath = "/openmldb";

            SdkOption option = new SdkOption();
            option.setZkCluster(zkHost);
            option.setZkPath(zkPath);
            option.setSessionTimeout(10000);
            option.setRequestTimeout(60000);


            SqlClusterExecutor sqlExecutor = new SqlClusterExecutor(option);
            
            String sql = "select (col1 >= 80) as bool_col1 from db1.t2";
            String db = "db1";


            java.sql.PreparedStatement pstmt = sqlExecutor.getRequestPreparedStmt(db, sql);

            pstmt.setDouble(1, 1.01);
            pstmt.setString(2, "foo");

            ResultSet resultSet = pstmt.executeQuery();

            resultSet.next();
            System.out.println("Result: " + resultSet.getBoolean(1)); // false
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

[tobegit3hub]

It may be the issue of boolean type. If we get the column value and APIServer return the correct value. If we try to get the boolean result, the return value is always true no matter it should be true or not.

deploy test2 select (col1) as bool_col1 from db1.t2;