[
https://issues.apache.org/jira/browse/PHOENIX-1416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14201241#comment-14201241
]
Samarth Jain commented on PHOENIX-1416:
---------------------------------------
Looks like something is off when we have LIKE expression combined with AND.
CREATE TABLE XYZ.ABC (ID1 VARCHAR(15) NOT NULL, ID2 VARCHAR(15) NOT
NULL,CREATED_DATE DATE,CREATION_TIME BIGINT,LAST_USED DATE CONSTRAINT PK
PRIMARY KEY (ID1, ID2))
CREATE TABLE ABC (ID1 VARCHAR(15) NOT NULL, ID2 VARCHAR(15) NOT
NULL,CREATED_DATE DATE,CREATION_TIME BIGINT,LAST_USED DATE CONSTRAINT PK
PRIMARY KEY (ID1, ID2))
Now, PhoenixDatabaseMetadata.getTables() executes the following query:
select TENANT_ID TABLE_CAT,TABLE_SCHEM,TABLE_NAME ,SQLTableType(TABLE_TYPE) AS
TABLE_TYPE,REMARKS
,TYPE_NAME,SELF_REFERENCING_COL_NAME,REF_GENERATION,IndexStateName(INDEX_STATE)
AS
INDEX_STATE,IMMUTABLE_ROWS,SALT_BUCKETS,MULTI_TENANT,VIEW_STATEMENT,SQLViewType(VIEW_TYPE)
AS VIEW_TYPE,SQLIndexType(INDEX_TYPE) AS INDEX_TYPE from SYSTEM."CATALOG"
"SYSTEM.TABLE"
WHERE
COLUMN_NAME is null
AND COLUMN_FAMILY is null
AND TABLE_SCHEM like 'XYZ'
AND TABLE_NAME like 'ABC'
ORDER BY "SYSTEM.TABLE".TABLE_TYPE,TENANT_ID,TABLE_SCHEM,TABLE_NAME
The above query ends up returning both the tables where it should have just
returned one row corresponding to XYZ.ABC
TENANT_ID TABLE_SCHEM TABLE_NAME
<null> <null> ABC
<null> XYZ ABC
If I change the WHERE clause like this:
WHERE
COLUMN_NAME is null
AND COLUMN_FAMILY is null
AND TABLE_NAME like 'ABC'
AND TABLE_SCHEM like 'XYZ'
ORDER BY "SYSTEM.TABLE".TABLE_TYPE,TENANT_ID,TABLE_SCHEM,TABLE_NAME
then the query returns only one corresponding to XYZ.ABC
TENANT_ID TABLE_SCHEM TABLE_NAME
<null> XYZ ABC
CC [~jamestaylor]
> Given a schema name, DatabaseMetadata.getTables and getColumns calls
> erroneously match tables without schema
> -------------------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-1416
> URL: https://issues.apache.org/jira/browse/PHOENIX-1416
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.1
> Environment: Any unix or windows platform
> Reporter: Sergio Lob
> Assignee: James Taylor
>
> When calling DatabaseMetadata.getTables and getColumns to find a table
> defined with a particular schema, the Phoenix JDBC driver also returns
> description of a table with the same tablename, but empty schema.
> eg, calling getTables(null, "R729999D", TABLE1, "TABLE");
> matches tables R729999D.TABLE1 and TABLE1 (no schema). It should return
> information on only table R729999D.TABLE1.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
