phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Victor Brakauskas <vbrakaus...@23andme.com>
Subject Re: Strange Exception in queries
Date Wed, 20 Mar 2019 22:33:20 GMT
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 <vbrakauskas@23andme.com>
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.
>

Mime
View raw message