[jira] [Created] (TAJO-1305) With metadata storage of MySQL, columns with the same character but difference case are not allowed

[Keuntae Park (JIRA)]

Keuntae Park created TAJO-1305:
----------------------------------

             Summary: With metadata storage of MySQL, columns with the same 
character but difference case are not allowed
                 Key: TAJO-1305
                 URL: https://issues.apache.org/jira/browse/TAJO-1305
             Project: Tajo
          Issue Type: Bug
            Reporter: Keuntae Park
            Priority: Minor


With MySQL as catalog storage, I experienced following create table statement 
did not work.

{code}
create table test ("name" text, "Name" text);
{code}

Error log is as followings:
{code}
2015-01-14 15:25:27,386 ERROR org.apache.tajo.catalog.CatalogServer: 
java.sql.BatchUpdateException: Duplicate entry '1221-Name' for key 'PRIMARY'
org.apache.tajo.catalog.exception.CatalogException: 
java.sql.BatchUpdateException: Duplicate entry '1221-Name' for key 'PRIMARY'
        at 
org.apache.tajo.catalog.store.AbstractDBStore.createTable(AbstractDBStore.java:819)
        at 
org.apache.tajo.catalog.CatalogServer$CatalogProtocolHandler.createTable(CatalogServer.java:523)
        at 
org.apache.tajo.catalog.AbstractCatalogClient$16.call(AbstractCatalogClient.java:354)
        at 
org.apache.tajo.catalog.AbstractCatalogClient$16.call(AbstractCatalogClient.java:351)
{code}

With Google's assistance, I found that 
MySQL primary key column does not distinguish the case of characters.
I made above statement work with following COLUMS schema (according to Google's 
advice):
{code}
CREATE TABLE COLUMNS (
  TID INT NOT NULL,
  COLUMN_NAME VARCHAR(255) BINARY NOT NULL,
  ORDINAL_POSITION INT NOT NULL,
  DATA_TYPE CHAR(16),
  TYPE_LENGTH INTEGER,
  PRIMARY KEY (TID, COLUMN_NAME),
  FOREIGN KEY (TID) REFERENCES TABLES (TID) ON DELETE CASCADE
)
{code}

I'm not sure this is proper approach to solve the problem
because I'm not familiar with Catalog :)
Please, check the situation.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)