I can totally believe that this bug exists, but it’s likely to be in the underlying database. Avatica will just transmit the values that the database gives it, and it isn’t going to behave differently if there is an ORDER BY. So, this will be a Calcite bug. Can you log it, please?
> On Apr 29, 2016, at 3:38 PM, Josh Elser <[email protected]> wrote: > > I guess I shouldn't write tests on Friday nights > > @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))")); > > for (int i = 0; i < 5; i++) { > assertEquals(1, > stmt.executeUpdate("INSERT INTO " + tableName + " values('asdf" + i > + "')")); > } > ResultSet results = stmt.executeQuery("select name as n from " + tableName > + " LIMIT 1"); > assertNotNull(results); > ResultSetMetaData metadata = results.getMetaData(); > assertEquals("NAME", metadata.getColumnName(1)); > assertEquals("N", metadata.getColumnLabel(1)); > results.close(); > > results = stmt.executeQuery("select name as n from " + tableName + " ORDER > BY NAME LIMIT 1"); > assertNotNull(results); > metadata = results.getMetaData(); > assertEquals("NAME", metadata.getColumnName(1)); > assertEquals("N", metadata.getColumnLabel(1)); > results.close(); > } > } > > Also passed for me. > > Josh Elser wrote: >> 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 >>>>> >>>>> >>>
