[CALCITE-1222] DatabaseMetaData.getColumnLabel returns null when query has ORDER BY

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.8.0
• Assignee: Julian Hyde
• Reporter: Kevin Risden
• Created: 29/Apr/16 23:23
• Updated: 12/Jun/16 07:44
• Resolved: 03/May/16 17:22

Description

Background from the thread here: http://mail-archives.apache.org/mod_mbox/calcite-dev/201604.mbox/%3CCAJU9nmgxyR%2BCXPXXjYkSKRL4%3DUvbN4nD1JMCtiFzAofRrT1Pug%40mail.gmail.com%3E

risdenk 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.

Issue Links

• is related to: SOLR-8593 Integrate Apache Calcite into the SQLHandler