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 <[email protected]> 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 >
