[
https://issues.apache.org/jira/browse/PHOENIX-4803?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ihor Krysenko updated PHOENIX-4803:
-----------------------------------
Description:
Select with subquery in condition returns 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|
+------------+-------------------------++----------------------+
was:
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 |
+-----------+--------------------------+-----------+-----------+
> 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
> Priority: Major
> Attachments: req_history.log
>
>
> Select with subquery in condition returns 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)
