phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: Problem with UPSERT SELECT with CHAR field
Date Tue, 19 Aug 2014 21:34:18 GMT
Thank you Josh for reporting the issue!


On Tue, Aug 19, 2014 at 5:27 PM, Josh Mahonin <jmahonin@interset.com> 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 <jmahonin@interset.com>
> 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

Mime
View raw message