phoenix-user mailing list archives

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