[jira] [Commented] (PHOENIX-6023) Wrong result when issuing query for an immutable table with multiple column families

["Toshihiro Suzuki (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-6023?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17159930#comment-17159930 ] 

Toshihiro Suzuki commented on PHOENIX-6023:
-------------------------------------------

It looks like the problem is as an immutable table uses encoded column names, when the table has multiple column families, it has the same qualifier names in different column families even though the actual column names are different. In this case, when we query for this table, we need to compare not only qualifier names but also column family names to disambiguate. In other words, we should use SingleCFCQKeyValueComparisonFilter/MultiCFCQKeyValueComparisonFilter, not SingleCQKeyValueComparisonFilter/MultiCQKeyValueComparisonFilter.

> Wrong result when issuing query for an immutable table with multiple column families
> ------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-6023
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-6023
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: Toshihiro Suzuki
>            Assignee: Toshihiro Suzuki
>            Priority: Major
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> Steps to reproduce are as follows:
> 1. Create an immutable table with multiple column families:
> {code}
> 0: jdbc:phoenix:> CREATE TABLE TEST (
> . . . . . . . . >   ID VARCHAR PRIMARY KEY,
> . . . . . . . . >   A.COL1 VARCHAR,
> . . . . . . . . >   B.COL2 VARCHAR
> . . . . . . . . > ) IMMUTABLE_ROWS = TRUE;
> No rows affected (1.182 seconds)
> {code}
> 2. Upsert some rows:
> {code}
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id0', '0', 'a');
> 1 row affected (0.138 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id1', '1', NULL);
> 1 row affected (0.009 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id2', '2', 'b');
> 1 row affected (0.011 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id3', '3', NULL);
> 1 row affected (0.007 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id4', '4', 'c');
> 1 row affected (0.006 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id5', '5', NULL);
> 1 row affected (0.007 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id6', '6', 'd');
> 1 row affected (0.007 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id7', '7', NULL);
> 1 row affected (0.007 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id8', '8', 'e');
> 1 row affected (0.007 seconds)
> 0: jdbc:phoenix:> UPSERT INTO TEST VALUES ('id9', '9', NULL);
> 1 row affected (0.009 seconds)
> {code}
> 3. Count query is okay:
> {code}
> 0: jdbc:phoenix:> SELECT COUNT(COL1) FROM TEST WHERE COL2 IS NOT NULL;
> +----------------+
> | COUNT(A.COL1)  |
> +----------------+
> | 5              |
> +----------------+
> 1 row selected (0.1 seconds)
> {code}
> 4. However, the following select query returns wrong result (it should return 5 records):
> {code}
> 0: jdbc:phoenix:> SELECT COL1 FROM TEST WHERE COL2 IS NOT NULL;
> +-------+
> | COL1  |
> +-------+
> | 0     |
> | 1     |
> | 2     |
> | 3     |
> | 4     |
> | 5     |
> | 6     |
> | 7     |
> | 8     |
> | 9     |
> +-------+
> 10 rows selected (0.058 seconds)
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)