You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Kevin Risden <co...@gmail.com> on 2016/04/29 22:55:56 UTC
Calcite/Avatica loses alias when adding order by/limit
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
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Kevin Risden <co...@gmail.com>.
Hmmm I agree I just added the same test to RemoteMetaTest and it passed.
Here is a gist of the JdbcExample that I am running on the latest master:
https://gist.github.com/risdenk/a555998dcbafd4ff14a7c329fd9addbe
I'd turn it into a unit test, but this was quick to show.
Kevin Risden
On Fri, Apr 29, 2016 at 5:38 PM, Josh Elser <jo...@gmail.com> 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"<jo...@gmail.com> 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
>>>>>
>>>>>
>>>>>
>>>
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Kevin Risden <co...@gmail.com>.
Created https://issues.apache.org/jira/browse/CALCITE-1222
Thanks!
Kevin Risden
On Fri, Apr 29, 2016 at 6:08 PM, Julian Hyde <jh...@gmail.com> 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 <jo...@gmail.com> 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"<jo...@gmail.com> 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
> >>>>>
> >>>>>
> >>>
>
>
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Julian Hyde <jh...@gmail.com>.
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 <jo...@gmail.com> 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"<jo...@gmail.com> 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
>>>>>
>>>>>
>>>
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Josh Elser <jo...@gmail.com>.
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"<jo...@gmail.com> 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
>>>>
>>>>
>>
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Josh Elser <jo...@gmail.com>.
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"<jo...@gmail.com> 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
>>>
>>>
>
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Kevin Risden <ri...@avalonconsult.com>.
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" <jo...@gmail.com> 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
>>
>>
Re: Calcite/Avatica loses alias when adding order by/limit
Posted by Josh Elser <jo...@gmail.com>.
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
>