Hi Kevin,
Great detective work so far :)
I wrote a quick test in RemoteMetaTest.java for Avatica on its own
@Test public void testIncorrectColumnName() throws Exception {
final String tableName = "incorrectColumnName";
try (final Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableName));
assertFalse(stmt.execute("CREATE TABLE " + tableName + "(name
varchar(20))"));
assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + "
values('asdf')"));
ResultSet results = stmt.executeQuery("select name as n from " +
tableName);
assertNotNull(results);
ResultSetMetaData metadata = results.getMetaData();
assertEquals("NAME", metadata.getColumnName(1));
assertEquals("N", metadata.getColumnLabel(1));
assertTrue(results.next());
assertEquals("asdf", results.getString(1));
assertFalse(results.next());
results.close();
}
}
This also works as expected. What version of Calcite/Avatica are you
trying to integrate with Solr? Did you perhaps run your test against a
newer version (that might have a bug fix)?
- Josh
Kevin Risden wrote:
I've been integrating Calcite/Avatica into Solr and ran into a case where
adding an order by or a limit changes the output of
DatabaseMetaData.getColumnName and getColumnLabel.
An example is below:
Behavior 1 (which I think is correct from reading the JDBC DatabaseMetaData
spec):
- select e."name" as full_name from "hr"."emps" as e;
- getColumnName(1) -> name
- getColumnLabel(1) -> FULL_NAME
Behavior 2:
- select e."name" as full_name from "hr"."emps" as e limit 10;
- getColumnName(1) -> FULL_NAME
- getColumnLabel(1) -> FULL_NAME
I was able to reproduce the behavior with the JdbcExample.java file in the
master branch of Calcite since I wasn't sure if it was my adapter
originally.
I started looking at the SqlParserTest and added this test case which
doesn't error out.
/**
* "LIMIT ... OFFSET ..." is the postgres equivalent of SQL:2008
* "OFFSET ... FETCH". It all maps down to a parse tree that looks like
* SQL:2008.
*/
@Test public void testColumnAliasWithAsWithLimit() {
check(
"select a as abc from foo order by b, c limit 2 offset 1",
"SELECT `A` AS `ABC`\n"
+ "FROM `FOO`\n"
+ "ORDER BY `B`, `C`\n"
+ "OFFSET 1 ROWS\n"
+ "FETCH NEXT 2 ROWS ONLY");
check(
"select a as abc from foo order by b, c limit 2",
"SELECT `A` AS `ABC`\n"
+ "FROM `FOO`\n"
+ "ORDER BY `B`, `C`\n"
+ "FETCH NEXT 2 ROWS ONLY");
check(
"select a as abc from foo order by b, c offset 1",
"SELECT `A` AS `ABC`\n"
+ "FROM `FOO`\n"
+ "ORDER BY `B`, `C`\n"
+ "OFFSET 1 ROWS");
I'm not sure where to look next to try to find this to submit a patch. Let
me know if you need more information.
I think this potentially causes some problems down the line when the
original name or alias is used for ordering and having, but I haven't been
able to verify this for sure.
Kevin Risden
