phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (PHOENIX-708) DatabaseMetaData column name searches should be case sensitive
Date Fri, 02 May 2014 04:54:16 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-708?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

James Taylor resolved PHOENIX-708.
----------------------------------

       Resolution: Fixed
    Fix Version/s: 5.0.0
                   4.0.0
                   3.0.0

> DatabaseMetaData column name searches should be case sensitive
> --------------------------------------------------------------
>
>                 Key: PHOENIX-708
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-708
>             Project: Phoenix
>          Issue Type: Task
>    Affects Versions: 3.0-Release
>            Reporter: James Taylor
>            Assignee: Julian Hyde
>              Labels: bug
>             Fix For: 3.0.0, 4.0.0, 5.0.0
>
>
> Conversation on the email list with @julianhyde:
> >> 10. PhoenixDatabaseMetaData.getColumns doesn't return anything if the
> >> table name is lower case. I think this is because
> >> PhoenixDatabaseMetaData.getColumns calls
> >> SchemaUtil.normalizeIdentifier(tableNamePattern), and that method converts
> >> the identifier to upper-case. It doesn't seem appropriate to call that
> >> method.
> >>
> >
> > Is this the case where you define a case sensitive lower case column name,
> > like this?
> >
> > create table foo ("my_pk" VARCHAR PRIMARY KEY);
> >
> I think Phoenix, Optiq and Oracle all have the same semantics regarding
> case of identifiers. The SQL parser converts unquoted identifiers go to
> upper-case, but quoted identifiers stay mixed case.
> In my particular case, I was looking for the columns in a lower-case table.
> I had defined the table something like this:
> CREATE TABLE "days" ("id" INTEGER NOT NULL PRIMARY KEY);
> It's true, you won't find this through a getColumns call, as we normalize
> > the columnsNamePattern as we normalize column names. Our typical way of
> > knowing if something is case sensitive, is when it's surrounded by double
> > quotes.
> >
> > For example, the getColumns call would find it if it was declared like
> > this instead:
> >
> > create table foo (my_pk VARCHAR PRIMARY KEY);
> >
> > Do you think it'd be best to do a case insensitive search for the columns
> > names, and if there are multiple ones with that name, return them all?
> >
> The JDBC standard very clear (I went back to the JDBC 1.0 spec, which says
> "same as ODBC") and the ODBC spec [
> http://msdn.microsoft.com/en-us/library/ms716447(v=vs.85).aspx ] says that
> the arguments are case-insensitive if they are patterns, case-sensitive if
> they are identifiers. (But JDBC doesn't have an equivalent of ODBC's
> SQL_ATTR_METADATA_ID, so they're not exactly comparable.)
> All the JDBC drivers I've seen treat the arguments as case-sensitive, even
> if their SQL is case-insensitive. For instance, here is what Postgres does
> [
> https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2DatabaseMetaData.java_L2411
>  ]:
>             if (tableNamePattern != null && !"".equals(tableNamePattern))
>             {
>                 sql += " AND c.relname LIKE " +
> escapeQuotes(tableNamePattern);
>             }



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message