"No such column with index" error if CASE in SELECT clause

[monetdb-team]

Date: 2012-11-21 02:57:08 +0100
From: Julian Hyde <>
To: SQL devs <>
Version: 11.13.3 (Oct2012)

Last updated: 2013-01-22 09:29:15 +0100

Comment 17969

Date: 2012-11-21 02:57:08 +0100
From: Julian Hyde <>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:16.0) Gecko/20100101 Firefox/16.0
Build Identifier:

I prepare the following SQL statement:

select case when "sales_fact_1997"."promotion_id" = 0 then 0
else "sales_fact_1997"."store_sales" end as "c0" from "sales_fact_1997" as "sales_fact_1997"

using Connection.prepareStatement(String) in JDBC driver. ResultSetMetaData.getColumnCount() returns 1, but ResultSetMetaData.getColumnName(1) throws:

java.sql.SQLException: No such column with index: 1
at nl.cwi.monetdb.jdbc.MonetPreparedStatement.getColumnIdx(MonetPreparedStatement.java:311)
at nl.cwi.monetdb.jdbc.MonetPreparedStatement.access$400(MonetPreparedStatement.java:53)
at nl.cwi.monetdb.jdbc.MonetPreparedStatement$1.getColumnName(MonetPreparedStatement.java:615)
at mondrian.rolap.RolapSchema$PhysSchema.describe(RolapSchema.java:1025)

This is from mondrian's lagunitas branch, using JDBC driver 2.6 and MonetDB-11.13.4.

Reproducible: Always

Comment 17970

Date: 2012-11-21 08:41:26 +0100
From: @grobian

This looks very similar to bug #3158. That fix is in 2.7. Can you give it a try (http://dev.monetdb.org/downloads/testing/Java/Oct2012-SP1/)?

Comment 17976

Date: 2012-11-21 14:54:02 +0100
From: @grobian

Oct2012-SP1 was released
(http://dev.monetdb.org/downloads/Java/Oct2012-SP1/monetdb-jdbc-2.7.jar)

I believe this is fixed in 2.7 (Oct2012-SP1), please test.

Comment 17978

Date: 2012-11-21 19:16:08 +0100
From: Julian Hyde <>

Still occurs with 2.7. Error stack is identical.

I looked at http://dev.monetdb.org/hg/MonetDB/file/7fb99d31ceec/java/src/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java. It seems a bit strange that getColumnIdx, if it returns, will return an index for which column[index] is null, yet getColumnName uses that index into the columns array.

public String getColumnName(int col) throws SQLException {
return column[getColumnIdx(col)];
}

private int getColumnIdx(int colnr) throws SQLException {
int curcol = 0;
for (int i = 0; i < size; i++) {
if (column[i] != null)
continue;
curcol++;
if (curcol == colnr)
return i;
}
throw new SQLException("No such column with index: " + colnr, "M1M05");
}

There is very similar code in MonetResultSet which I suspect is better tested.

I also spotted a bug in getTableName. Code looks familiar?

public String getTableName(int col) throws SQLException {
return column[getColumnIdx(col)];
}

Comment 17979

Date: 2012-11-21 21:25:42 +0100
From: @grobian

(In reply to comment 3)

I also spotted a bug in getTableName. Code looks familiar?

please enlighten me, I don't see the bug

Comment 17980

Date: 2012-11-22 02:40:09 +0100
From: Julian Hyde <>

It should be something like

public String getTableName(int col) throws SQLException {
return table[getColumnIdx(col)];
}

As it stands getTableName(x) will always return the same as getColumnName(x).

Comment 17981

Date: 2012-11-22 09:01:41 +0100
From: @grobian

oh, doh! thanks

Comment 17982

Date: 2012-11-22 09:05:55 +0100
From: @grobian

Changeset c58ed8603d69 made by Fabian Groffen fabian@monetdb.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c58ed8603d69

Changeset description:

getTableName: use correct backing array

Thanks Julian Hyde for noticing in bug #3192

Comment 17983

Date: 2012-11-22 13:37:16 +0100
From: @grobian

Problem found, I mixed up parameters and columns that one has in a prepared statement. Building proper test now.

Comment 17984

Date: 2012-11-22 13:54:16 +0100
From: @grobian

Changeset 59deba53fa55 made by Fabian Groffen fabian@monetdb.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=59deba53fa55

Changeset description:

Test_PSmetadata: test for column and parameter metadata

This test reveals the problem reported in bug #3192

Comment 17985

Date: 2012-11-22 13:54:23 +0100
From: @grobian

Changeset 1e15c1849512 made by Fabian Groffen fabian@monetdb.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=1e15c1849512

Changeset description:

PreparedStatement: don't mix up parameters and columns

PreparedStatements can return metadata for parameters as well as result
columns since Dec2011.  In previous fixes to cope with this, I didn't
quite realise that there is a difference between the two.  We now
properly separate both, such that we don't mistakenly claim a column or
parameter doesn't exist, such as in bug #3192.  This changeset makes
Test_PSmetadata working correctly.

Comment 17986

Date: 2012-11-22 13:54:25 +0100
From: @grobian

Changeset e4a6b9edf48f made by Fabian Groffen fabian@monetdb.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=e4a6b9edf48f

Changeset description:

Add Mtest for bug #3192

Comment 18375

Date: 2013-01-22 09:29:15 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.