Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@phoenix.apache.org
Date: Fri, 30 Dec 2016 08:40:58 +0000 (UTC)
From: "chenglei (JIRA)" <jira@apache.org>
To: dev@phoenix.apache.org
Message-ID: <JIRA.13018128.1478270477000.617682.1483087258394@Atlassian.JIRA>
In-Reply-To: <JIRA.13018128.1478270477000@Atlassian.JIRA>
References: <JIRA.13018128.1478270477000@Atlassian.JIRA> <JIRA.13018128.1478270477870@arcas>
Subject: [jira] [Comment Edited] (PHOENIX-3453) Secondary index and query
 using distinct: Outer query results in ERROR 201 (22000): Illegal data.
 CHAR types may only contain single byte characters
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
archived-at: Fri, 30 Dec 2016 08:41:00 -0000


    [ https://issues.apache.org/jira/browse/PHOENIX-3453?page=3Dcom.atlassi=
an.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D15=
787222#comment-15787222 ]=20

chenglei edited comment on PHOENIX-3453 at 12/30/16 8:40 AM:
-------------------------------------------------------------

I wrote following test case to make this problem can be reproduced under 4.=
9.0, simplifying the original test case by removing the index table and cha=
nge the type from CHAR(15) to Integer,  which is more easier to debug:

{code:borderStyle=3Dsolid}=20
  CREATE TABLE GROUPBY3453_INT (
            ENTITY_ID INTEGER NOT NULL,
            CONTAINER_ID INTEGER NOT NULL,
            SCORE INTEGER NOT NULL,
            CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DES=
C,SCORE DESC)
  )
=20
  UPSERT INTO  GROUPBY3453_INT  VALUES (1,1,1)
  select DISTINCT entity_id, score from ( select entity_id, score from GROU=
PBY3453_INT limit 1)
{code}=20

the expecting result is :=20
{code:borderStyle=3Dsolid}=20
   1  1
{code}=20

but the actual result is:
{code:borderStyle=3Dsolid}
  -104  1       =20
{code}=20

This problem can only be reproduced when the SQL has a SubQuery.


was (Author: comnetwork):
I wrote following test case to make this problem can be reproduced under 4.=
9.0, simplifying the original test case by removing the index table and cha=
nge the type from CHAR(15) to Integer,  which is more easier to debug:

{code:borderStyle=3Dsolid}=20
  CREATE TABLE GROUPBY3453_INT (
            ENTITY_ID INTEGER NOT NULL,
            CONTAINER_ID INTEGER NOT NULL,
            SCORE INTEGER NOT NULL,
            CONSTRAINT TEST_PK PRIMARY KEY (ENTITY_ID DESC,CONTAINER_ID DES=
C,SCORE DESC)
  )
=20
  UPSERT INTO  GROUPBY3453_INT  VALUES (1,1,1)
  select DISTINCT entity_id, score from ( select entity_id, score from GROU=
PBY3453_INT limit 1)
{code}=20

the expecting result is :=20
{code:borderStyle=3Dsolid}=20
   1  1
{code}=20

but the actual result is:
{code:borderStyle=3Dsolid}
      -104  1       =20
{code}=20

This problem can only be reproduced when there SQL has a SubQuery.


> Secondary index and query using distinct: Outer query results in ERROR 20=
1 (22000): Illegal data. CHAR types may only contain single byte characters
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------
>
>                 Key: PHOENIX-3453
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3453
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.8.0
>            Reporter: Joel Palmert
>            Assignee: chenglei
>
> Steps to repro:
> CREATE TABLE IF NOT EXISTS TEST.TEST (
> ENTITY_ID CHAR(15) NOT NULL,
> SCORE DOUBLE,
> CONSTRAINT TEST_PK PRIMARY KEY (
> ENTITY_ID
> )
> ) VERSIONS=3D1, MULTI_TENANT=3DFALSE, REPLICATION_SCOPE=3D1, TTL=3D315360=
00;
> CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (SCORE DESC, ENTITY_ID=
 DESC);
> UPSERT INTO test.test VALUES ('entity1',1.1);
> SELECT DISTINCT entity_id, score
> FROM(
> SELECT entity_id, score
> FROM test.test
> LIMIT 25
> );
> Output (in SQuirreL)
> =EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=
=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=EF=BF=BD=091.1
> If you run it in SQuirreL it results in the entity_id column getting the =
above error value. Notice that if you remove the secondary index or DISTINC=
T you get the correct result.
> I've also run the query through the Phoenix java api. Then I get the foll=
owing exception:
> Caused by: java.sql.SQLException: ERROR 201 (22000): Illegal data. CHAR t=
ypes may only contain single byte characters (????????????)
>     at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newExcepti=
on(SQLExceptionCode.java:454)
>     at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLEx=
ceptionInfo.java:145)
>     at org.apache.phoenix.schema.types.PDataType.newIllegalDataException(=
PDataType.java:291)
>     at org.apache.phoenix.schema.types.PChar.toObject(PChar.java:121)
>     at org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:=
997)
>     at org.apache.phoenix.compile.ExpressionProjector.getValue(Expression=
Projector.java:75)
>     at org.apache.phoenix.jdbc.PhoenixResultSet.getString(PhoenixResultSe=
t.java:608)
>     at org.apache.phoenix.jdbc.PhoenixResultSet.getString(PhoenixResultSe=
t.java:621)


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)