Oh, you're right. I totally forgot that bit :P
Kevin Risden wrote:
Reading from my phone, but I think the limit or order by is missing in the
test you tried. Only when order by or limit is present will the column name
and column label be the same instead of different.
Kevin Risden
Hadoop Tech Lead | Avalon Consulting, LLC
M: 732 213 8417
<sent from my phone>
On Apr 29, 2016 5:08 PM, "Josh Elser"<[email protected]> wrote:
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
