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
