Thank you Josh for reporting the issue!
On Tue, Aug 19, 2014 at 5:27 PM, Josh Mahonin <[email protected]> wrote: > 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 >> > > -- Thanks, Maryann
