[
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 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|
+------------+-------------------------++----------------------+
> 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)
