Also, pointed out to me I forgot to list the versions! We're on phoenix 4.14.0 and hbase 1.3
<https://inboxwhenready.org/?utm_campaign=signature&utm_medium=email&utm_source=signature> I'm using Inbox When Ready <https://inboxwhenready.org/?utm_campaign=signature&utm_medium=email&utm_source=signature> to protect my focus. On Wed, Mar 20, 2019 at 2:18 PM Victor Brakauskas <[email protected]> wrote: > Hello, I'm getting a an error when trying to run a selection of queries. > The schema is > > create table if not exists TABLE1( > col1 tinyint not null, > col2 integer not null, > col3 integer not null, > col4 varchar not null, > ind_id integer not null, > d tinyint > constraint table1_pk primary key (col1, col2, col3, col4, ind_id) > ) > column_encoded_bytes = 0, > data_block_encoding = 'FAST_DIFF', > versions = 1, > compression = 'GZ', > salt_buckets = 60, > filesize = 32212254720; > > create table if not exists IND( > ind_id integer not null, > otherid_id bigint, > delete_ind_attr boolean, > delete_other boolean > constraint ind_pk primary key (ind_id) > ) > column_encoded_bytes = 0, > data_block_encoding = 'FAST_DIFF', > versions = 1, > compression = 'GZ', > salt_buckets = 6, > filesize = 10737418240; > > > create table if not exists table2( > attr_id integer not null, > ind_id integer not null, > int_val bigint, > float_val float, > date_val date, > time_val date, > string_val varchar, > array_val varchar[], > time_of date, > bool_val boolean > constraint ind_attr_pk primary key (attr_id, ind_id) > ) > column_encoded_bytes = 0, > data_block_encoding = 'FAST_DIFF', > versions = 1, > compression = 'GZ', > salt_buckets = 120, > filesize = 10737418240; > > > And the query I'm trying to run is > > > SELECT > > /*+ USE_SORT_MERGE_JOIN HASH_AGGREGATE */ > ind.ind_id , > loj10.date_val , > loj1.res , > loj2.res , > loj3.res , > loj4.res , > loj5.res , > loj6.res , > loj7.res , > loj8.res > FROM > ind > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 10) > AND (col2 = 104548392) > AND (col3 = 1) > AND (col4 = 'A') > ) > ) AS loj1 ON ( > ind.ind_id = loj1.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 3) > AND (col2 = 161482644) > AND (col3 = 1) > AND (col4 = 'A') > ) > ) AS loj2 ON ( > ind.ind_id = loj2.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 13) > AND (col2 = 86467968) > AND (col3 = 1) > AND (col4 = 'A') > ) > ) AS loj3 ON ( > ind.ind_id = loj3.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 5) > AND (col2 = 108803333) > AND (col3 = 1) > AND (col4 = 'B') > ) > ) AS loj4 ON ( > ind.ind_id = loj4.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 12) > AND (col2 = 51837788) > AND (col3 = 1) > AND (col4 = 'B') > ) > ) AS loj5 ON ( > ind.ind_id = loj5.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 1) > AND (col2 = 39948784) > AND (col3 = 1) > AND (col4 = 'C') > ) > ) AS loj6 ON ( > ind.ind_id = loj6.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 7) > AND (col2 = 113679106) > AND (col3 = 1) > AND (col4 = 'C') > ) > ) AS loj7 ON ( > ind.ind_id = loj7.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > CASE WHEN d IS NOT NULL THEN d ELSE 0 END AS res > FROM > table1 > WHERE > ( > (col1 = 20) > AND (col2 = 57418070) > AND (col3 = 1) > AND (col4 = 'C') > ) > ) AS loj8 ON ( > ind.ind_id = loj8.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > string_val > FROM > table2 > WHERE > (tabl2.attr_id = 92008) > ) loj9 ON ( > ind.ind_id = loj9.ind_id > ) > LEFT OUTER JOIN ( > SELECT > ind_id, > date_val > FROM > table2 > WHERE > (table2.attr_id = 20033) > ) loj10 ON ( > ind.ind_id = loj10.ind_id > ) > WHERE > loj9.string_val = 'accepted' > > > The error is below, and looks to point to a data issue. > > *Error: ERROR 201 (22000): Illegal data. Expected length of at least 127 > bytes, but had 7 (state=22000,code=201)* > > java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of > at least 127 bytes, but had 7 > > at > org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:494) > > at > org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150) > > at org.apache.phoenix.schema.KeyValueSchema.next(KeyValueSchema.java:213) > > at > org.apache.phoenix.expression.ProjectedColumnExpression.evaluate(ProjectedColumnExpression.java:116) > > at > org.apache.phoenix.expression.ComparisonExpression.evaluate(ComparisonExpression.java:302) > > at > org.apache.phoenix.expression.AndOrExpression.evaluate(AndOrExpression.java:72) > > at > org.apache.phoenix.iterate.FilterResultIterator.advance(FilterResultIterator.java:62) > > at > org.apache.phoenix.iterate.LookAheadResultIterator.next(LookAheadResultIterator.java:67) > > at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:805) > > at sqlline.BufferedRows.<init>(BufferedRows.java:37) > > at sqlline.SqlLine.print(SqlLine.java:1660) > > at sqlline.Commands.execute(Commands.java:833) > > at sqlline.Commands.sql(Commands.java:732) > > at sqlline.SqlLine.dispatch(SqlLine.java:813) > > at sqlline.SqlLine.begin(SqlLine.java:686) > > at sqlline.SqlLine.start(SqlLine.java:398) > > at sqlline.SqlLine.main(SqlLine.java:291) > > However, when I remove ANY of the loj columns from the select > columns/remove the left outer .join itself, the query returns rows as > expected. Additionally, if I edit one of the left outer joins so that it > returns zero results via the where clauses, the query can successfully > return results. I tried the same query on a development cluster with a > smaller amount of data, and it succeeded, which also points to a data > issue. However, the fact that the query will succeed with any left outer > join removed, and fail when it's added back in, seems to indicate that the > rows are correctly formatted. I'm not totally sure how to go about > troubleshooting this further, any ideas? > > Thanks in advance! > Victor Brakauskas > > <https://inboxwhenready.org/?utm_campaign=signature&utm_medium=email&utm_source=signature> > I'm using Inbox When Ready > <https://inboxwhenready.org/?utm_campaign=signature&utm_medium=email&utm_source=signature> > to protect my focus. >
