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.
