ColumnNotFoundException when using queries with column aliasing in JDBCWrapper

[hemil-ruparel-blox]

Consider the following yaml file:

port: 5003

routes:
  get:
    /test: _/test.zm

data-sources:
  test:
    type: jdbc
    secrets: secret_source
    properties:
      user: <username>
      password: <password>
    connection: "jdbc:mysql://localhost/test"

test.zm

resp = _ds.test.select('select a as b from a', [])
panic(resp.inError, resp.error)

resp.value

If you hit localhost:5003/test, you get a SQL Exception:

SEVERE: Error in query : java.sql.SQLException: Column 'a' not found.

Root Cause

Relevant lines from JDBCWrapper:line 176 to 183

Map<String,Object> m = new LinkedHashMap<>(); // because of... order preserving
for (int index = 1; index <= count; index++) {
    String column = rsmd.getColumnName(index);
    Object value = rs.getObject(column);
    Object transformedValue = getObject(value);
    m.put(column, transformedValue);
}
result.add(m);

Root cause is rsmd.getColumnName(index); this gets the name of the column selected. But this name may differ from the actual result returned by the query.

Fix

Replace rsmd.getColumnName(index); with rsmd.getColumnLabel(index);. As per documentation,

Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.

[hemil-ruparel-blox]

@nmondal Fix looks good to me. Can you publish it as well please?

[nmondal]

Already done.