phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Mahonin <jmaho...@interset.com>
Subject Re: Problem with UPSERT SELECT with CHAR field
Date Tue, 19 Aug 2014 21:27:43 GMT
To update the list, this bug appears to have been fixed:

Issue was captured here:
https://issues.apache.org/jira/browse/PHOENIX-1182

And fixed here:
https://github.com/apache/phoenix/commit/7b1ba69ffe1b32a0af1045d481110d26a4818be6

Thanks!

Josh


On Mon, Aug 18, 2014 at 5:58 PM, Josh Mahonin <jmahonin@interset.com> wrote:

> Hi all,
>
> I'm having problems creating a join table when one of the fields involved
> is a CHAR. I have a reproducible test case below:
>
> -- Create source table
> CREATE TABLE IF NOT EXISTS SOURCE_TABLE(
>   TID CHAR(3) NOT NULL,
>   A UNSIGNED_INT NOT NULL,
>   B UNSIGNED_INT NOT NULL
>   CONSTRAINT pk PRIMARY KEY (TID, A, B));
>
> -- Populate with sample data
> UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 1);
> UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 2);
> UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 3);
> UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 2, 1);
> UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 2, 2);
>
> -- Create table for common counts
> CREATE TABLE IF NOT EXISTS JOIN_TABLE(
>   TID CHAR(3) NOT NULL,
>   A UNSIGNED_INT NOT NULL,
>   B UNSIGNED_INT NOT NULL,
>   COUNT UNSIGNED_INT
>   CONSTRAINT pk PRIMARY KEY (TID, A, B));
>
> -- Populate with common occurrences
> UPSERT INTO JOIN_TABLE(TID, A, B, COUNT)
> SELECT t1.TID,
>        t1.A,
>        t2.A,
>        COUNT(*)
> FROM SOURCE_TABLE t1
> INNER JOIN SOURCE_TABLE t2 ON t1.B = t2.B
> WHERE t1.A != t2.A
>   AND t1.TID = '1'
>   AND t2.TID = '1'
> GROUP BY t1.TID,
>          t1.A,
>          t2.A;
>
>
> Unfortunately that last query fails with the following:
> Error: ERROR 203 (22005): Type mismatch. expected: CHAR but was:
> UNSIGNED_INT at column: TID
> SQLState:  22005
> ErrorCode: 203
>
> This query works if I change the data type of TID into something integer
> based, like a TINYINT, but the multi-tenancy guide suggests that the tenant
> column must be a CHAR or VARCHAR. I'm using Phoenix 5.0.0-SNAPSHOT built on
> the latest as of August 12.
>
> Does anyone have any ideas?
>
> Thanks,
>
> Josh
>

Mime
View raw message