Bug report: Wrong column type retrieved from metainfo for statements with zero rows

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Bug report: Wrong column type retrieved from metainfo for statements with zero rows

Машков Дмитрий Владимирович
Hi there,

I have db with table as

CREATE TABLE GasStats10s(GasStats10s_MaxValue REAL,GasStats10s_IntervalDuration INTEGER,GasStats10s_DataCount INTEGER,GasStats10s_MinValue REAL,GasStats10s_Value REAL,GasStats10s_DateTime INTEGER,GasStats10s_Source UID,GasStats10s_Sum REAL,Class UID,DeleteLog UID,ID UID,Origin UID,TimeStamp INTEGER, primary key (ID))

and

select statement

SELECT
      t.GasStats10s_Value as val,
      cast(hex(t.GasStats10s_Source) as text) as source,
      cast(t.TimeStamp as integer) as timestamp
FROM GasStats10s t
WHERE t.TimeStamp >= ?
ORDER BY t.TimeStamp ASC

Preamble,
This issue came from very long path. Java is my primary language and I used JDBC driver from https://github.com/xerial/sqlite-jdbc with embedded primary SQLite C API code as JNI library mapped to SQLite C API release versions under Apache NiFi.

Bug description.
Different behaviour of sqlite3_column_type and sqlite3_column_decltype functions depends of result of select statement above. If SELECT statement above returns more than 0 records, functions works perfect and return type of columns for this statement, but if SELECT statement return 0 records, functions for source columns return NULL.

I have wrote simple test program to proof that behaviour, one simple Java JDBC program to open connection, execute SQL and get ResultSet, I can ask SQLite C API to get columns types.

I used JDBC version 3.25.2 as SQLite 3.25.2, rebuild for 3.26.0 same results.


import java.sql.*;

public class Main {

    public static void main(String[] args) {
        String url = "jdbc:sqlite:<macOS path to file>";

        try (Connection conn = DriverManager.getConnection(url)) {

                System.out.println("Connection to SQLite has been established.");
                String query1 = "SELECT\n" +
                               "      t.GasStats10s_Value as val,\n" +
                               "      t.GasStats10s_Source as source,\n" +
                               "      t.TimeStamp\n" +
                               "FROM GasStats10s t\n" +
                               "WHERE t.TimeStamp >= 0\n" +
                               "ORDER BY t.TimeStamp ASC\n" +
                               "LIMIT 50;";

                String query2 = "SELECT\n" +
                            "      t.GasStats10s_Value as val,\n" +
                            "      t.GasStats10s_Source as source,\n" +
                            "      t.TimeStamp\n" +
                            "FROM GasStats10s t\n" +
                            "WHERE t.TimeStamp >= 15446222410350000\n" +
                            "ORDER BY t.TimeStamp ASC\n" +
                            "LIMIT 50;";

                try (Statement stmt = conn.createStatement())
                {
                    ResultSet rs1 = stmt.executeQuery(query1);

                    ResultSetMetaData metaData1 = rs1.getMetaData();
                    System.out.println("Query 1");

                    System.out.println("Column 1 Type:" + metaData1.getColumnType(1));
                    System.out.println("Column 2 Type:" + metaData1.getColumnType(2));
                    System.out.println("Column 3 Type:" + metaData1.getColumnType(3));

                    ResultSet rs2 = stmt.executeQuery(query2);

                    ResultSetMetaData metaData2 = rs2.getMetaData();
                    System.out.println("Query 2");

                    System.out.println("Column 1 Type:" + metaData2.getColumnType(1));
                    System.out.println("Column 2 Type:" + metaData2.getColumnType(2));
                    System.out.println("Column 3 Type:" + metaData2.getColumnType(3));
                } catch (SQLException e ) {
                        System.out.println(e.getMessage());
                }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

Result I got follow

Connection to SQLite has been established.
Query 1 (50 records)
Column 1 Type:7
Column 2 Type:2004
Column 3 Type:4
Query 2 (0 records)
Column 1 Type:7
Column 2 Type:0
Column 3 Type:4

I have spend a little bit time to dig this issue,

Primary method
org/sqlite/jdbc3/JDBC3ResultSet.java:741

/**
 * @see java.sql.ResultSetMetaData#getColumnType(int)
 */
public int getColumnType(int col) throws SQLException {
    String typeName = getColumnTypeName(col);
    int valueType = getDatabase().column_type(stmt.pointer, checkCol(col));


next, calls in Java went to JNI native code directly to SQLite C API, on follow methods

org.sqlite.core.NativeDB#column_type
org.sqlite.core.NativeDB#column_decltype

and respectfully to C counterpart


JNIEXPORT jint JNICALL Java_org_sqlite_core_NativeDB_column_1type(
        JNIEnv *env, jobject this, jlong stmt, jint col)
{
    if (!stmt)
    {
        throwex_stmt_finalized(env);
        return SQLITE_MISUSE;
    }

    return sqlite3_column_type(toref(stmt), col);
}

JNIEXPORT jbyteArray JNICALL Java_org_sqlite_core_NativeDB_column_1decltype_1utf8(
        JNIEnv *env, jobject this, jlong stmt, jint col)
{
    const char *str;

    if (!stmt)
    {
        throwex_stmt_finalized(env);
        return NULL;
    }

    str = (const char*) sqlite3_column_decltype(toref(stmt), col);
    if (!str) return NULL;
    return utf8BytesToJavaByteArray(env, str, strlen(str));
}

How I can resolve this issue? If you have any questions let me know.

Sincerely,
Dmitry Mashkov
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users