phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ihor Krysenko (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-4803) SELECT query returns rows from index table instead data table
Date Tue, 03 Jul 2018 11:04:00 GMT

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

Mime
View raw message