phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Mahonin <jmaho...@interset.com>
Subject Re: Problem with UPSERT SELECT with CHAR field
Date Tue, 19 Aug 2014 21:44:28 GMT
Thanks for the very quick fix!


On Tue, Aug 19, 2014 at 5:34 PM, Maryann Xue <maryann.xue@gmail.com> wrote:

> 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