You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@phoenix.apache.org by Josh Mahonin <jm...@interset.com> on 2014/08/18 23:58:08 UTC

Problem with UPSERT SELECT with CHAR field

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

Re: Problem with UPSERT SELECT with CHAR field

Posted by Josh Mahonin <jm...@interset.com>.

Thanks for the very quick fix!


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

> Thank you Josh for reporting the issue!
>
>
> On Tue, Aug 19, 2014 at 5:27 PM, Josh Mahonin <jm...@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 <jm...@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
>

Re: Problem with UPSERT SELECT with CHAR field

Posted by Maryann Xue <ma...@gmail.com>.

Thank you Josh for reporting the issue!


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

Re: Problem with UPSERT SELECT with CHAR field

Posted by Josh Mahonin <jm...@interset.com>.

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 <jm...@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
>