phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Mahonin <jmaho...@interset.com>
Subject Problem with UPSERT SELECT with CHAR field
Date Mon, 18 Aug 2014 21:58:08 GMT
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