Created https://issues.apache.org/jira/browse/CALCITE-1222
Thanks! Kevin Risden On Fri, Apr 29, 2016 at 6:08 PM, Julian Hyde <[email protected]> wrote: > 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 > >>>>> > >>>>> > >>> > >
