Thanks for the very quick fix!
On Tue, Aug 19, 2014 at 5:34 PM, Maryann Xue <[email protected]> wrote: > 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 >
