You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Ihor Krysenko (JIRA)" <ji...@apache.org> on 2018/07/03 11:03:00 UTC
[jira] [Created] (PHOENIX-4803) SELECT query returns rows from
index table instead data table
Ihor Krysenko created PHOENIX-4803:
--------------------------------------
Summary: SELECT query returns rows from index table instead data table
Key: PHOENIX-4803
URL: https://issues.apache.org/jira/browse/PHOENIX-4803
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.14.0
Environment: Cloudera 5.11.2, Phoenix version 4.14.0-cdh5.11.2
Reporter: Ihor Krysenko
Attachments: req_history.log
Select with subquery in condition return data from index table instead data table, but sometimes the same request works correctly.
Workaround NO_INDEX hint.
If this query part of bigger query with UNION ALL it throws exception:
Error: ERROR 525 (42902): SELECT column number differs in a Union All query is not allowed. 1st query has 4 columns whereas 2nd query has 6 (state=42902,code=525)
java.sql.SQLException: ERROR 525 (42902): SELECT column number differs in a Union All query is not allowed. 1st query has 4 columns whereas 2nd query has 6
Look to the attachments for request history;
Env for reproduce:
CREATE SCHEMA IF NOT EXISTS TST_NS;
CREATE TABLE IF NOT EXISTS TST_NS.TEST (
FIELD_1 CHAR(7) NOT NULL,
FIELD_2 VARCHAR(8) NOT NULL,
FIELD_3 TIME NOT NULL,
FIELD_4 UNSIGNED_INT NOT NULL,
CF.FIELD_5 BIGINT,
CF.FIELD_6 INTEGER
CONSTRAINT CFPK PRIMARY KEY (FIELD_1, FIELD_2, FIELD_3, FIELD_4)
) COMPRESSION='SNAPPY', IMMUTABLE_ROWS=true;
CREATE LOCAL INDEX IF NOT EXISTS IDX_TEST ON TST_NS.TEST (FIELD_1 ASC, FIELD_3 ASC, FIELD_2 ASC) COMPRESSION='SNAPPY';
UPSERT INTO TST_NS.TEST VALUES ('record1', 'testrow1', TO_TIME('2018-07-03 10:00:01.123'), 134100, 111123131, 12133);
UPSERT INTO TST_NS.TEST VALUES ('record2', 'testrow2', TO_TIME('2018-07-03 10:00:01.124'), 134101, 111123132, 12134);
UPSERT INTO TST_NS.TEST VALUES ('record3', 'testrow3', TO_TIME('2018-07-03 10:00:01.125'), 134102, 111123133, 12135);
UPSERT INTO TST_NS.TEST VALUES ('record8', 'testrow4', TO_TIME('2018-07-03 10:00:01.126'), 134103, 111123134, 12136);
UPSERT INTO TST_NS.TEST VALUES ('record8', 'testrow5', TO_TIME('2018-07-03 10:00:01.126'), 134104, 111123135, 12137);
UPSERT INTO TST_NS.TEST VALUES ('record6', 'testrow6', TO_TIME('2018-07-03 10:00:02.123'), 134105, 111123136, 12133);
UPSERT INTO TST_NS.TEST VALUES ('record7', 'testrow6', TO_TIME('2018-07-03 10:00:02.124'), 134106, 111123137, 12133);
UPSERT INTO TST_NS.TEST VALUES ('record8', 'testrow6', TO_TIME('2018-07-03 10:00:03.127'), 134107, 111123138, 12133);
UPSERT INTO TST_NS.TEST VALUES ('record8', 'testrow6', TO_TIME('2018-07-03 10:00:03.128'), 134108, 111123139, 12133);
0: jdbc:phoenix:quickstart.cloudera:2181:/hba> select * from TST_NS.TEST;
+----------+-----------+--------------------------+----------+------------+----------+
| FIELD_1 | FIELD_2 | FIELD_3 | FIELD_4 | FIELD_5 | FIELD_6 |
+----------+-----------+--------------------------+----------+------------+----------+
| record1 | testrow1 | 2018-07-03 10:00:01.123 | 134100 | 111123131 | 12133 |
| record2 | testrow2 | 2018-07-03 10:00:01.124 | 134101 | 111123132 | 12134 |
| record3 | testrow3 | 2018-07-03 10:00:01.125 | 134102 | 111123133 | 12135 |
| record6 | testrow6 | 2018-07-03 10:00:02.123 | 134105 | 111123136 | 12133 |
| record7 | testrow6 | 2018-07-03 10:00:02.124 | 134106 | 111123137 | 12133 |
| record8 | testrow4 | 2018-07-03 10:00:01.126 | 134103 | 111123134 | 12136 |
| record8 | testrow5 | 2018-07-03 10:00:01.126 | 134104 | 111123135 | 12137 |
| record8 | testrow6 | 2018-07-03 10:00:03.127 | 134107 | 111123138 | 12133 |
| record8 | testrow6 | 2018-07-03 10:00:03.128 | 134108 | 111123139 | 12133 |
+----------+-----------+--------------------------+----------+------------+----------+
8 rows selected (0.114 seconds)
0: jdbc:phoenix:quickstart.cloudera:2181:/hba> select * from TST_NS.IDX_TEST;
+-----------+--------------------------+-----------+-----------+
| :FIELD_1 | :FIELD_3 | :FIELD_2 | :FIELD_4 |
+-----------+--------------------------+-----------+-----------+
| record1 | 2018-07-03 10:00:01.123 | testrow1 | 134100 |
| record2 | 2018-07-03 10:00:01.124 | testrow2 | 134101 |
| record3 | 2018-07-03 10:00:01.125 | testrow3 | 134102 |
| record6 | 2018-07-03 10:00:02.123 | testrow6 | 134105 |
| record7 | 2018-07-03 10:00:02.124 | testrow6 | 134106 |
| record8 | 2018-07-03 10:00:01.126 | testrow4 | 134103 |
| record8 | 2018-07-03 10:00:01.126 | testrow5 | 134104 |
| record8 | 2018-07-03 10:00:03.127 | testrow6 | 134107 |
| record9 | 2018-07-03 10:00:03.128 | testrow6 | 134108 |
+-----------+--------------------------+-----------+-----------+
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)