You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Kunal Gupta <ku...@iiitd.ac.in> on 2015/01/12 08:43:01 UTC
Query returning different results in Apache Phoenix and MySQL
I have a table named as* EVENTLOG* which has 3 columns:
*(CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS VARCHAR(200))*
*DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss.* Below is the query that
will work on Apache phoenix as well as for MySQL.
SELECT DISTINCT E1.SUBSTATUS
FROM EVENTLOG AS E1
WHERE E1.DATEPLUSTIME IN
(SELECT MIN(E2.DATEPLUSTIME)
FROM EVENTLOG AS E2
GROUP BY E2.CASEID);
Table contains 466738 records. When i perform the above query in MySQL , i
get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing 3
rows why is there difference in their result.
Table looks like the following(Records Striped).
+----+---+---+---+--+----------+----+-----------+
| CASEID | DATEPLUSTIME | SUBSTATUS |
+----+---+---+---+--------+-----+---+-----------+
| 1 | 2010-04-10 18:20:38 | Assignment|
| 1 | 2010-04-10 20:12:56 | Update |
| 1 | 2010-11-23 23:12:34 | Assigned |
| 1 | 2010-11-25 23:45:34 | Assigned |
| 2 | 2010-12-30 12:32:23 | Reassigned|
| 2 | 2014-12-30 12:34:21 | Operator |
| 2 | 2014-12-25 20:32:45 | Operator |
| 3 | 2014-12-26 08:45:54 | Progress |
| 3 | 2014-12-30 08:49:32 | Call |
+----+---+---+---+---+----+------+--+-----------+
*[Expected Output]*
Assignment
Reassigned
Progress
MySQL does the task correctly butr Phoenix return incomplete result.
Please help me.
Thanks , Kunal
Re: Re: Query returning different results in Apache Phoenix and MySQL
Posted by Kunal Gupta <ku...@iiitd.ac.in>.
Thanks Sunfl
Thanks , Kunal
On Tue, Jan 13, 2015 at 8:26 AM, sunfl@certusnet.com.cn <
sunfl@certusnet.com.cn> wrote:
> Hi, Kunal
>
> Yep. Issuing that command shall lead you to the acuall file size for hbase
> tables on hdfs.
>
> If you definitely want to get that through some hadoop fs api, you can
> reference to
> org.apache.hadoop.fs.DU and org.apache.hadoop.fs.DUHelper, maybe you can
> get the deserved results.
>
> Thanks,
> Sun.
>
> ------------------------------
> ------------------------------
>
> CertusNet
>
>
>
> *From:* Kunal Gupta <ku...@iiitd.ac.in>
> *Date:* 2015-01-13 11:11
> *To:* user <us...@phoenix.apache.org>
> *Subject:* Re: Re: Query returning different results in Apache Phoenix
> and MySQL
> Thanks Sunfl for replying
>
> can you tell me which HDFS api?
> And command that you provided me will it calculate the whole size of table
> if table is splitted in multiple region.
>
> Thanks , Kunal
>
>
> On Tue, Jan 13, 2015 at 6:40 AM, sunfl@certusnet.com.cn <
> sunfl@certusnet.com.cn> wrote:
>
>> Hi, Kunal
>> If you want to know the disk usage of table in Phoenix, you can
>> definitely search for the hbase table size
>> that stored on HDFS. So you can issue command like "hadoop fs -du -h
>> /hbase/data/default/YOUR PHONEIX TABLES".
>>
>> I don't know how to calculate that through java code, but I think you can
>> use sort of hdfs api to do that if you want to.
>>
>> Thanks,
>> Sun.
>>
>> ------------------------------
>> ------------------------------
>>
>> CertusNet
>>
>>
>> *From:* Kunal Gupta <ku...@iiitd.ac.in>
>> *Date:* 2015-01-12 22:35
>> *To:* user <us...@phoenix.apache.org>
>> *Subject:* Re: Query returning different results in Apache Phoenix and
>> MySQL
>> I think i have overcome with my problem but i stuck in another problem
>>
>> I overcome by changing in MySQL Table Eventlog by considering
>> DATEPLUSTIME attribute as TIMESTAMP datatype instead of DATE datatype
>> because in Apache Phoenix there was TIMESTAMP datatype.
>>
>> Thank you for your response
>>
>> *My another Problem is Can you tell me how to calculate DISK USAGE of all
>> table in Apache Phoenix either by query or by some JAVA code*
>>
>> Thanks , Kunal
>>
>>
>> On Mon, Jan 12, 2015 at 7:24 PM, Dhaval Modi <dh...@gmail.com>
>> wrote:
>>
>>> Hi Kunal,
>>>
>>>
>>> Try running below query in both MySQL and Phoenix and find the missing
>>> entries.
>>>
>>> SELECT E1.CASEID, E1.DATEPLUSTIME, E1.SUBSTATUS
>>> FROM EVENTLOG AS E1
>>> WHERE E1.DATEPLUSTIME IN
>>> (SELECT MIN(E2.DATEPLUSTIME)
>>> FROM EVENTLOG AS E2
>>> GROUP BY E2.CASEID);
>>>
>>> Send us the copy of missing entries to understand this issue.
>>>
>>>
>>>
>>>
>>> Regards,
>>> Dhaval Modi
>>> dhavalmodi24@gmail.com
>>>
>>> On 12 January 2015 at 18:06, Kunal Gupta <ku...@iiitd.ac.in> wrote:
>>>
>>>> For 65,000 records i get exact value as MySQL gives, i don't know why
>>>> it giving me wrong answer on 466738 records
>>>>
>>>> Thanks , Kunal
>>>>
>>>>
>>>> On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com>
>>>> wrote:
>>>>
>>>>> Kunal,
>>>>>
>>>>> Just to clarify: I didn't mean that you should post (or send) the full
>>>>> contents of your table. Instead, I mean you should make a minimal test case
>>>>> (with a minimal number of records) that demonstrates the issue. For
>>>>> example, do you get unexpected results if you only have 10 records in your
>>>>> table?
>>>>>
>>>>> - Gabriel
>>>>>
>>>>>
>>>>> On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>>>> wrote:
>>>>>
>>>>>> Thank You Gabriel for your response and will reply you soon with
>>>>>> snapshot of all becasue Table contain 466738 records so i will send you
>>>>>> snapshot of those who are missing in Phoenix.
>>>>>>
>>>>>> Can you tell me if i can perform same operation in different way as i
>>>>>> defined in my earlier mail.I hope you got me question
>>>>>>
>>>>>> Thanks , Kunal
>>>>>>
>>>>>>
>>>>>> On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <gabriel.reid@gmail.com
>>>>>> > wrote:
>>>>>>
>>>>>>> Hi Kunal,
>>>>>>>
>>>>>>> I think you'll need to post some additional information to get an
>>>>>>> answer to your question. You said that MySQL returns 35 rows and
>>>>>>> Phoenix returns 32 rows, but it's not clear from your description
>>>>>>> what
>>>>>>> the rows are that are missing from the Phoenix result, or what it is
>>>>>>> that makes the Phoenix result incorrect and the MySQL result correct.
>>>>>>>
>>>>>>> The best approach to resolving this would be to create a minimal
>>>>>>> case,
>>>>>>> with a smaller number of rows, that demonstrates the issue that
>>>>>>> you're
>>>>>>> encountering.
>>>>>>>
>>>>>>> - Gabriel
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>>>>>> wrote:
>>>>>>> > I have a table named as EVENTLOG which has 3 columns:
>>>>>>> >
>>>>>>> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS
>>>>>>> VARCHAR(200))
>>>>>>> >
>>>>>>> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the
>>>>>>> query that
>>>>>>> > will work on Apache phoenix as well as for MySQL.
>>>>>>> >
>>>>>>> > SELECT DISTINCT E1.SUBSTATUS
>>>>>>> > FROM EVENTLOG AS E1
>>>>>>> > WHERE E1.DATEPLUSTIME IN
>>>>>>> > (SELECT MIN(E2.DATEPLUSTIME)
>>>>>>> > FROM EVENTLOG AS E2
>>>>>>> > GROUP BY E2.CASEID);
>>>>>>> >
>>>>>>> > Table contains 466738 records. When i perform the above query in
>>>>>>> MySQL , i
>>>>>>> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is
>>>>>>> missing 3
>>>>>>> > rows why is there difference in their result.
>>>>>>> > Table looks like the following(Records Striped).
>>>>>>> >
>>>>>>> > +----+---+---+---+--+----------+----+-----------+
>>>>>>> > | CASEID | DATEPLUSTIME | SUBSTATUS |
>>>>>>> > +----+---+---+---+--------+-----+---+-----------+
>>>>>>> > | 1 | 2010-04-10 18:20:38 | Assignment|
>>>>>>> > | 1 | 2010-04-10 20:12:56 | Update |
>>>>>>> > | 1 | 2010-11-23 23:12:34 | Assigned |
>>>>>>> > | 1 | 2010-11-25 23:45:34 | Assigned |
>>>>>>> > | 2 | 2010-12-30 12:32:23 | Reassigned|
>>>>>>> > | 2 | 2014-12-30 12:34:21 | Operator |
>>>>>>> > | 2 | 2014-12-25 20:32:45 | Operator |
>>>>>>> > | 3 | 2014-12-26 08:45:54 | Progress |
>>>>>>> > | 3 | 2014-12-30 08:49:32 | Call |
>>>>>>> > +----+---+---+---+---+----+------+--+-----------+
>>>>>>> > [Expected Output]
>>>>>>> >
>>>>>>> > Assignment
>>>>>>> > Reassigned
>>>>>>> > Progress
>>>>>>> >
>>>>>>> > MySQL does the task correctly butr Phoenix return incomplete
>>>>>>> result.
>>>>>>> > Please help me.
>>>>>>> >
>>>>>>> >
>>>>>>> > Thanks , Kunal
>>>>>>> >
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Re: Query returning different results in Apache Phoenix and MySQL
Posted by "sunfl@certusnet.com.cn" <su...@certusnet.com.cn>.
Hi, Kunal
Yep. Issuing that command shall lead you to the acuall file size for hbase tables on hdfs.
If you definitely want to get that through some hadoop fs api, you can reference to
org.apache.hadoop.fs.DU and org.apache.hadoop.fs.DUHelper, maybe you can get the deserved results.
Thanks,
Sun.
CertusNet
From: Kunal Gupta
Date: 2015-01-13 11:11
To: user
Subject: Re: Re: Query returning different results in Apache Phoenix and MySQL
Thanks Sunfl for replying
can you tell me which HDFS api?
And command that you provided me will it calculate the whole size of table if table is splitted in multiple region.
Thanks , Kunal
On Tue, Jan 13, 2015 at 6:40 AM, sunfl@certusnet.com.cn <su...@certusnet.com.cn> wrote:
Hi, Kunal
If you want to know the disk usage of table in Phoenix, you can definitely search for the hbase table size
that stored on HDFS. So you can issue command like "hadoop fs -du -h /hbase/data/default/YOUR PHONEIX TABLES".
I don't know how to calculate that through java code, but I think you can use sort of hdfs api to do that if you want to.
Thanks,
Sun.
CertusNet
From: Kunal Gupta
Date: 2015-01-12 22:35
To: user
Subject: Re: Query returning different results in Apache Phoenix and MySQL
I think i have overcome with my problem but i stuck in another problem
I overcome by changing in MySQL Table Eventlog by considering DATEPLUSTIME attribute as TIMESTAMP datatype instead of DATE datatype because in Apache Phoenix there was TIMESTAMP datatype.
Thank you for your response
My another Problem is Can you tell me how to calculate DISK USAGE of all table in Apache Phoenix either by query or by some JAVA code
Thanks , Kunal
On Mon, Jan 12, 2015 at 7:24 PM, Dhaval Modi <dh...@gmail.com> wrote:
Hi Kunal,
Try running below query in both MySQL and Phoenix and find the missing entries.
SELECT E1.CASEID, E1.DATEPLUSTIME, E1.SUBSTATUS
FROM EVENTLOG AS E1
WHERE E1.DATEPLUSTIME IN
(SELECT MIN(E2.DATEPLUSTIME)
FROM EVENTLOG AS E2
GROUP BY E2.CASEID);
Send us the copy of missing entries to understand this issue.
Regards,
Dhaval Modi
dhavalmodi24@gmail.com
On 12 January 2015 at 18:06, Kunal Gupta <ku...@iiitd.ac.in> wrote:
For 65,000 records i get exact value as MySQL gives, i don't know why it giving me wrong answer on 466738 records
Thanks , Kunal
On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com> wrote:
Kunal,
Just to clarify: I didn't mean that you should post (or send) the full contents of your table. Instead, I mean you should make a minimal test case (with a minimal number of records) that demonstrates the issue. For example, do you get unexpected results if you only have 10 records in your table?
- Gabriel
On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in> wrote:
Thank You Gabriel for your response and will reply you soon with snapshot of all becasue Table contain 466738 records so i will send you snapshot of those who are missing in Phoenix.
Can you tell me if i can perform same operation in different way as i defined in my earlier mail.I hope you got me question
Thanks , Kunal
On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com> wrote:
Hi Kunal,
I think you'll need to post some additional information to get an
answer to your question. You said that MySQL returns 35 rows and
Phoenix returns 32 rows, but it's not clear from your description what
the rows are that are missing from the Phoenix result, or what it is
that makes the Phoenix result incorrect and the MySQL result correct.
The best approach to resolving this would be to create a minimal case,
with a smaller number of rows, that demonstrates the issue that you're
encountering.
- Gabriel
On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in> wrote:
> I have a table named as EVENTLOG which has 3 columns:
>
> (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS VARCHAR(200))
>
> DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query that
> will work on Apache phoenix as well as for MySQL.
>
> SELECT DISTINCT E1.SUBSTATUS
> FROM EVENTLOG AS E1
> WHERE E1.DATEPLUSTIME IN
> (SELECT MIN(E2.DATEPLUSTIME)
> FROM EVENTLOG AS E2
> GROUP BY E2.CASEID);
>
> Table contains 466738 records. When i perform the above query in MySQL , i
> get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing 3
> rows why is there difference in their result.
> Table looks like the following(Records Striped).
>
> +----+---+---+---+--+----------+----+-----------+
> | CASEID | DATEPLUSTIME | SUBSTATUS |
> +----+---+---+---+--------+-----+---+-----------+
> | 1 | 2010-04-10 18:20:38 | Assignment|
> | 1 | 2010-04-10 20:12:56 | Update |
> | 1 | 2010-11-23 23:12:34 | Assigned |
> | 1 | 2010-11-25 23:45:34 | Assigned |
> | 2 | 2010-12-30 12:32:23 | Reassigned|
> | 2 | 2014-12-30 12:34:21 | Operator |
> | 2 | 2014-12-25 20:32:45 | Operator |
> | 3 | 2014-12-26 08:45:54 | Progress |
> | 3 | 2014-12-30 08:49:32 | Call |
> +----+---+---+---+---+----+------+--+-----------+
> [Expected Output]
>
> Assignment
> Reassigned
> Progress
>
> MySQL does the task correctly butr Phoenix return incomplete result.
> Please help me.
>
>
> Thanks , Kunal
>
Re: Re: Query returning different results in Apache Phoenix and MySQL
Posted by Kunal Gupta <ku...@iiitd.ac.in>.
Thanks Sunfl for replying
can you tell me which HDFS api?
And command that you provided me will it calculate the whole size of table
if table is splitted in multiple region.
Thanks , Kunal
On Tue, Jan 13, 2015 at 6:40 AM, sunfl@certusnet.com.cn <
sunfl@certusnet.com.cn> wrote:
> Hi, Kunal
> If you want to know the disk usage of table in Phoenix, you can definitely
> search for the hbase table size
> that stored on HDFS. So you can issue command like "hadoop fs -du -h
> /hbase/data/default/YOUR PHONEIX TABLES".
>
> I don't know how to calculate that through java code, but I think you can
> use sort of hdfs api to do that if you want to.
>
> Thanks,
> Sun.
>
> ------------------------------
> ------------------------------
>
> CertusNet
>
>
> *From:* Kunal Gupta <ku...@iiitd.ac.in>
> *Date:* 2015-01-12 22:35
> *To:* user <us...@phoenix.apache.org>
> *Subject:* Re: Query returning different results in Apache Phoenix and
> MySQL
> I think i have overcome with my problem but i stuck in another problem
>
> I overcome by changing in MySQL Table Eventlog by considering DATEPLUSTIME
> attribute as TIMESTAMP datatype instead of DATE datatype because in Apache
> Phoenix there was TIMESTAMP datatype.
>
> Thank you for your response
>
> *My another Problem is Can you tell me how to calculate DISK USAGE of all
> table in Apache Phoenix either by query or by some JAVA code*
>
> Thanks , Kunal
>
>
> On Mon, Jan 12, 2015 at 7:24 PM, Dhaval Modi <dh...@gmail.com>
> wrote:
>
>> Hi Kunal,
>>
>>
>> Try running below query in both MySQL and Phoenix and find the missing
>> entries.
>>
>> SELECT E1.CASEID, E1.DATEPLUSTIME, E1.SUBSTATUS
>> FROM EVENTLOG AS E1
>> WHERE E1.DATEPLUSTIME IN
>> (SELECT MIN(E2.DATEPLUSTIME)
>> FROM EVENTLOG AS E2
>> GROUP BY E2.CASEID);
>>
>> Send us the copy of missing entries to understand this issue.
>>
>>
>>
>>
>> Regards,
>> Dhaval Modi
>> dhavalmodi24@gmail.com
>>
>> On 12 January 2015 at 18:06, Kunal Gupta <ku...@iiitd.ac.in> wrote:
>>
>>> For 65,000 records i get exact value as MySQL gives, i don't know why it
>>> giving me wrong answer on 466738 records
>>>
>>> Thanks , Kunal
>>>
>>>
>>> On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com>
>>> wrote:
>>>
>>>> Kunal,
>>>>
>>>> Just to clarify: I didn't mean that you should post (or send) the full
>>>> contents of your table. Instead, I mean you should make a minimal test case
>>>> (with a minimal number of records) that demonstrates the issue. For
>>>> example, do you get unexpected results if you only have 10 records in your
>>>> table?
>>>>
>>>> - Gabriel
>>>>
>>>>
>>>> On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>>> wrote:
>>>>
>>>>> Thank You Gabriel for your response and will reply you soon with
>>>>> snapshot of all becasue Table contain 466738 records so i will send you
>>>>> snapshot of those who are missing in Phoenix.
>>>>>
>>>>> Can you tell me if i can perform same operation in different way as i
>>>>> defined in my earlier mail.I hope you got me question
>>>>>
>>>>> Thanks , Kunal
>>>>>
>>>>>
>>>>> On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi Kunal,
>>>>>>
>>>>>> I think you'll need to post some additional information to get an
>>>>>> answer to your question. You said that MySQL returns 35 rows and
>>>>>> Phoenix returns 32 rows, but it's not clear from your description what
>>>>>> the rows are that are missing from the Phoenix result, or what it is
>>>>>> that makes the Phoenix result incorrect and the MySQL result correct.
>>>>>>
>>>>>> The best approach to resolving this would be to create a minimal case,
>>>>>> with a smaller number of rows, that demonstrates the issue that you're
>>>>>> encountering.
>>>>>>
>>>>>> - Gabriel
>>>>>>
>>>>>>
>>>>>> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>>>>> wrote:
>>>>>> > I have a table named as EVENTLOG which has 3 columns:
>>>>>> >
>>>>>> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS
>>>>>> VARCHAR(200))
>>>>>> >
>>>>>> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the
>>>>>> query that
>>>>>> > will work on Apache phoenix as well as for MySQL.
>>>>>> >
>>>>>> > SELECT DISTINCT E1.SUBSTATUS
>>>>>> > FROM EVENTLOG AS E1
>>>>>> > WHERE E1.DATEPLUSTIME IN
>>>>>> > (SELECT MIN(E2.DATEPLUSTIME)
>>>>>> > FROM EVENTLOG AS E2
>>>>>> > GROUP BY E2.CASEID);
>>>>>> >
>>>>>> > Table contains 466738 records. When i perform the above query in
>>>>>> MySQL , i
>>>>>> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is
>>>>>> missing 3
>>>>>> > rows why is there difference in their result.
>>>>>> > Table looks like the following(Records Striped).
>>>>>> >
>>>>>> > +----+---+---+---+--+----------+----+-----------+
>>>>>> > | CASEID | DATEPLUSTIME | SUBSTATUS |
>>>>>> > +----+---+---+---+--------+-----+---+-----------+
>>>>>> > | 1 | 2010-04-10 18:20:38 | Assignment|
>>>>>> > | 1 | 2010-04-10 20:12:56 | Update |
>>>>>> > | 1 | 2010-11-23 23:12:34 | Assigned |
>>>>>> > | 1 | 2010-11-25 23:45:34 | Assigned |
>>>>>> > | 2 | 2010-12-30 12:32:23 | Reassigned|
>>>>>> > | 2 | 2014-12-30 12:34:21 | Operator |
>>>>>> > | 2 | 2014-12-25 20:32:45 | Operator |
>>>>>> > | 3 | 2014-12-26 08:45:54 | Progress |
>>>>>> > | 3 | 2014-12-30 08:49:32 | Call |
>>>>>> > +----+---+---+---+---+----+------+--+-----------+
>>>>>> > [Expected Output]
>>>>>> >
>>>>>> > Assignment
>>>>>> > Reassigned
>>>>>> > Progress
>>>>>> >
>>>>>> > MySQL does the task correctly butr Phoenix return incomplete result.
>>>>>> > Please help me.
>>>>>> >
>>>>>> >
>>>>>> > Thanks , Kunal
>>>>>> >
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Re: Query returning different results in Apache Phoenix and MySQL
Posted by "sunfl@certusnet.com.cn" <su...@certusnet.com.cn>.
Hi, Kunal
If you want to know the disk usage of table in Phoenix, you can definitely search for the hbase table size
that stored on HDFS. So you can issue command like "hadoop fs -du -h /hbase/data/default/YOUR PHONEIX TABLES".
I don't know how to calculate that through java code, but I think you can use sort of hdfs api to do that if you want to.
Thanks,
Sun.
CertusNet
From: Kunal Gupta
Date: 2015-01-12 22:35
To: user
Subject: Re: Query returning different results in Apache Phoenix and MySQL
I think i have overcome with my problem but i stuck in another problem
I overcome by changing in MySQL Table Eventlog by considering DATEPLUSTIME attribute as TIMESTAMP datatype instead of DATE datatype because in Apache Phoenix there was TIMESTAMP datatype.
Thank you for your response
My another Problem is Can you tell me how to calculate DISK USAGE of all table in Apache Phoenix either by query or by some JAVA code
Thanks , Kunal
On Mon, Jan 12, 2015 at 7:24 PM, Dhaval Modi <dh...@gmail.com> wrote:
Hi Kunal,
Try running below query in both MySQL and Phoenix and find the missing entries.
SELECT E1.CASEID, E1.DATEPLUSTIME, E1.SUBSTATUS
FROM EVENTLOG AS E1
WHERE E1.DATEPLUSTIME IN
(SELECT MIN(E2.DATEPLUSTIME)
FROM EVENTLOG AS E2
GROUP BY E2.CASEID);
Send us the copy of missing entries to understand this issue.
Regards,
Dhaval Modi
dhavalmodi24@gmail.com
On 12 January 2015 at 18:06, Kunal Gupta <ku...@iiitd.ac.in> wrote:
For 65,000 records i get exact value as MySQL gives, i don't know why it giving me wrong answer on 466738 records
Thanks , Kunal
On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com> wrote:
Kunal,
Just to clarify: I didn't mean that you should post (or send) the full contents of your table. Instead, I mean you should make a minimal test case (with a minimal number of records) that demonstrates the issue. For example, do you get unexpected results if you only have 10 records in your table?
- Gabriel
On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in> wrote:
Thank You Gabriel for your response and will reply you soon with snapshot of all becasue Table contain 466738 records so i will send you snapshot of those who are missing in Phoenix.
Can you tell me if i can perform same operation in different way as i defined in my earlier mail.I hope you got me question
Thanks , Kunal
On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com> wrote:
Hi Kunal,
I think you'll need to post some additional information to get an
answer to your question. You said that MySQL returns 35 rows and
Phoenix returns 32 rows, but it's not clear from your description what
the rows are that are missing from the Phoenix result, or what it is
that makes the Phoenix result incorrect and the MySQL result correct.
The best approach to resolving this would be to create a minimal case,
with a smaller number of rows, that demonstrates the issue that you're
encountering.
- Gabriel
On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in> wrote:
> I have a table named as EVENTLOG which has 3 columns:
>
> (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS VARCHAR(200))
>
> DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query that
> will work on Apache phoenix as well as for MySQL.
>
> SELECT DISTINCT E1.SUBSTATUS
> FROM EVENTLOG AS E1
> WHERE E1.DATEPLUSTIME IN
> (SELECT MIN(E2.DATEPLUSTIME)
> FROM EVENTLOG AS E2
> GROUP BY E2.CASEID);
>
> Table contains 466738 records. When i perform the above query in MySQL , i
> get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing 3
> rows why is there difference in their result.
> Table looks like the following(Records Striped).
>
> +----+---+---+---+--+----------+----+-----------+
> | CASEID | DATEPLUSTIME | SUBSTATUS |
> +----+---+---+---+--------+-----+---+-----------+
> | 1 | 2010-04-10 18:20:38 | Assignment|
> | 1 | 2010-04-10 20:12:56 | Update |
> | 1 | 2010-11-23 23:12:34 | Assigned |
> | 1 | 2010-11-25 23:45:34 | Assigned |
> | 2 | 2010-12-30 12:32:23 | Reassigned|
> | 2 | 2014-12-30 12:34:21 | Operator |
> | 2 | 2014-12-25 20:32:45 | Operator |
> | 3 | 2014-12-26 08:45:54 | Progress |
> | 3 | 2014-12-30 08:49:32 | Call |
> +----+---+---+---+---+----+------+--+-----------+
> [Expected Output]
>
> Assignment
> Reassigned
> Progress
>
> MySQL does the task correctly butr Phoenix return incomplete result.
> Please help me.
>
>
> Thanks , Kunal
>
Re: Query returning different results in Apache Phoenix and MySQL
Posted by Kunal Gupta <ku...@iiitd.ac.in>.
I think i have overcome with my problem but i stuck in another problem
I overcome by changing in MySQL Table Eventlog by considering DATEPLUSTIME
attribute as TIMESTAMP datatype instead of DATE datatype because in Apache
Phoenix there was TIMESTAMP datatype.
Thank you for your response
*My another Problem is Can you tell me how to calculate DISK USAGE of all
table in Apache Phoenix either by query or by some JAVA code*
Thanks , Kunal
On Mon, Jan 12, 2015 at 7:24 PM, Dhaval Modi <dh...@gmail.com> wrote:
> Hi Kunal,
>
>
> Try running below query in both MySQL and Phoenix and find the missing
> entries.
>
> SELECT E1.CASEID, E1.DATEPLUSTIME, E1.SUBSTATUS
> FROM EVENTLOG AS E1
> WHERE E1.DATEPLUSTIME IN
> (SELECT MIN(E2.DATEPLUSTIME)
> FROM EVENTLOG AS E2
> GROUP BY E2.CASEID);
>
> Send us the copy of missing entries to understand this issue.
>
>
>
>
> Regards,
> Dhaval Modi
> dhavalmodi24@gmail.com
>
> On 12 January 2015 at 18:06, Kunal Gupta <ku...@iiitd.ac.in> wrote:
>
>> For 65,000 records i get exact value as MySQL gives, i don't know why it
>> giving me wrong answer on 466738 records
>>
>> Thanks , Kunal
>>
>>
>> On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com>
>> wrote:
>>
>>> Kunal,
>>>
>>> Just to clarify: I didn't mean that you should post (or send) the full
>>> contents of your table. Instead, I mean you should make a minimal test case
>>> (with a minimal number of records) that demonstrates the issue. For
>>> example, do you get unexpected results if you only have 10 records in your
>>> table?
>>>
>>> - Gabriel
>>>
>>>
>>> On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>> wrote:
>>>
>>>> Thank You Gabriel for your response and will reply you soon with
>>>> snapshot of all becasue Table contain 466738 records so i will send you
>>>> snapshot of those who are missing in Phoenix.
>>>>
>>>> Can you tell me if i can perform same operation in different way as i
>>>> defined in my earlier mail.I hope you got me question
>>>>
>>>> Thanks , Kunal
>>>>
>>>>
>>>> On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Kunal,
>>>>>
>>>>> I think you'll need to post some additional information to get an
>>>>> answer to your question. You said that MySQL returns 35 rows and
>>>>> Phoenix returns 32 rows, but it's not clear from your description what
>>>>> the rows are that are missing from the Phoenix result, or what it is
>>>>> that makes the Phoenix result incorrect and the MySQL result correct.
>>>>>
>>>>> The best approach to resolving this would be to create a minimal case,
>>>>> with a smaller number of rows, that demonstrates the issue that you're
>>>>> encountering.
>>>>>
>>>>> - Gabriel
>>>>>
>>>>>
>>>>> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>>>> wrote:
>>>>> > I have a table named as EVENTLOG which has 3 columns:
>>>>> >
>>>>> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS
>>>>> VARCHAR(200))
>>>>> >
>>>>> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query
>>>>> that
>>>>> > will work on Apache phoenix as well as for MySQL.
>>>>> >
>>>>> > SELECT DISTINCT E1.SUBSTATUS
>>>>> > FROM EVENTLOG AS E1
>>>>> > WHERE E1.DATEPLUSTIME IN
>>>>> > (SELECT MIN(E2.DATEPLUSTIME)
>>>>> > FROM EVENTLOG AS E2
>>>>> > GROUP BY E2.CASEID);
>>>>> >
>>>>> > Table contains 466738 records. When i perform the above query in
>>>>> MySQL , i
>>>>> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is
>>>>> missing 3
>>>>> > rows why is there difference in their result.
>>>>> > Table looks like the following(Records Striped).
>>>>> >
>>>>> > +----+---+---+---+--+----------+----+-----------+
>>>>> > | CASEID | DATEPLUSTIME | SUBSTATUS |
>>>>> > +----+---+---+---+--------+-----+---+-----------+
>>>>> > | 1 | 2010-04-10 18:20:38 | Assignment|
>>>>> > | 1 | 2010-04-10 20:12:56 | Update |
>>>>> > | 1 | 2010-11-23 23:12:34 | Assigned |
>>>>> > | 1 | 2010-11-25 23:45:34 | Assigned |
>>>>> > | 2 | 2010-12-30 12:32:23 | Reassigned|
>>>>> > | 2 | 2014-12-30 12:34:21 | Operator |
>>>>> > | 2 | 2014-12-25 20:32:45 | Operator |
>>>>> > | 3 | 2014-12-26 08:45:54 | Progress |
>>>>> > | 3 | 2014-12-30 08:49:32 | Call |
>>>>> > +----+---+---+---+---+----+------+--+-----------+
>>>>> > [Expected Output]
>>>>> >
>>>>> > Assignment
>>>>> > Reassigned
>>>>> > Progress
>>>>> >
>>>>> > MySQL does the task correctly butr Phoenix return incomplete result.
>>>>> > Please help me.
>>>>> >
>>>>> >
>>>>> > Thanks , Kunal
>>>>> >
>>>>>
>>>>
>>>>
>>>
>>
>
Re: Query returning different results in Apache Phoenix and MySQL
Posted by Dhaval Modi <dh...@gmail.com>.
Hi Kunal,
Try running below query in both MySQL and Phoenix and find the missing
entries.
SELECT E1.CASEID, E1.DATEPLUSTIME, E1.SUBSTATUS
FROM EVENTLOG AS E1
WHERE E1.DATEPLUSTIME IN
(SELECT MIN(E2.DATEPLUSTIME)
FROM EVENTLOG AS E2
GROUP BY E2.CASEID);
Send us the copy of missing entries to understand this issue.
Regards,
Dhaval Modi
dhavalmodi24@gmail.com
On 12 January 2015 at 18:06, Kunal Gupta <ku...@iiitd.ac.in> wrote:
> For 65,000 records i get exact value as MySQL gives, i don't know why it
> giving me wrong answer on 466738 records
>
> Thanks , Kunal
>
>
> On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com>
> wrote:
>
>> Kunal,
>>
>> Just to clarify: I didn't mean that you should post (or send) the full
>> contents of your table. Instead, I mean you should make a minimal test case
>> (with a minimal number of records) that demonstrates the issue. For
>> example, do you get unexpected results if you only have 10 records in your
>> table?
>>
>> - Gabriel
>>
>>
>> On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in>
>> wrote:
>>
>>> Thank You Gabriel for your response and will reply you soon with
>>> snapshot of all becasue Table contain 466738 records so i will send you
>>> snapshot of those who are missing in Phoenix.
>>>
>>> Can you tell me if i can perform same operation in different way as i
>>> defined in my earlier mail.I hope you got me question
>>>
>>> Thanks , Kunal
>>>
>>>
>>> On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com>
>>> wrote:
>>>
>>>> Hi Kunal,
>>>>
>>>> I think you'll need to post some additional information to get an
>>>> answer to your question. You said that MySQL returns 35 rows and
>>>> Phoenix returns 32 rows, but it's not clear from your description what
>>>> the rows are that are missing from the Phoenix result, or what it is
>>>> that makes the Phoenix result incorrect and the MySQL result correct.
>>>>
>>>> The best approach to resolving this would be to create a minimal case,
>>>> with a smaller number of rows, that demonstrates the issue that you're
>>>> encountering.
>>>>
>>>> - Gabriel
>>>>
>>>>
>>>> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>>> wrote:
>>>> > I have a table named as EVENTLOG which has 3 columns:
>>>> >
>>>> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS
>>>> VARCHAR(200))
>>>> >
>>>> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query
>>>> that
>>>> > will work on Apache phoenix as well as for MySQL.
>>>> >
>>>> > SELECT DISTINCT E1.SUBSTATUS
>>>> > FROM EVENTLOG AS E1
>>>> > WHERE E1.DATEPLUSTIME IN
>>>> > (SELECT MIN(E2.DATEPLUSTIME)
>>>> > FROM EVENTLOG AS E2
>>>> > GROUP BY E2.CASEID);
>>>> >
>>>> > Table contains 466738 records. When i perform the above query in
>>>> MySQL , i
>>>> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is
>>>> missing 3
>>>> > rows why is there difference in their result.
>>>> > Table looks like the following(Records Striped).
>>>> >
>>>> > +----+---+---+---+--+----------+----+-----------+
>>>> > | CASEID | DATEPLUSTIME | SUBSTATUS |
>>>> > +----+---+---+---+--------+-----+---+-----------+
>>>> > | 1 | 2010-04-10 18:20:38 | Assignment|
>>>> > | 1 | 2010-04-10 20:12:56 | Update |
>>>> > | 1 | 2010-11-23 23:12:34 | Assigned |
>>>> > | 1 | 2010-11-25 23:45:34 | Assigned |
>>>> > | 2 | 2010-12-30 12:32:23 | Reassigned|
>>>> > | 2 | 2014-12-30 12:34:21 | Operator |
>>>> > | 2 | 2014-12-25 20:32:45 | Operator |
>>>> > | 3 | 2014-12-26 08:45:54 | Progress |
>>>> > | 3 | 2014-12-30 08:49:32 | Call |
>>>> > +----+---+---+---+---+----+------+--+-----------+
>>>> > [Expected Output]
>>>> >
>>>> > Assignment
>>>> > Reassigned
>>>> > Progress
>>>> >
>>>> > MySQL does the task correctly butr Phoenix return incomplete result.
>>>> > Please help me.
>>>> >
>>>> >
>>>> > Thanks , Kunal
>>>> >
>>>>
>>>
>>>
>>
>
Re: Query returning different results in Apache Phoenix and MySQL
Posted by Kunal Gupta <ku...@iiitd.ac.in>.
For 65,000 records i get exact value as MySQL gives, i don't know why it
giving me wrong answer on 466738 records
Thanks , Kunal
On Mon, Jan 12, 2015 at 4:14 PM, Gabriel Reid <ga...@gmail.com>
wrote:
> Kunal,
>
> Just to clarify: I didn't mean that you should post (or send) the full
> contents of your table. Instead, I mean you should make a minimal test case
> (with a minimal number of records) that demonstrates the issue. For
> example, do you get unexpected results if you only have 10 records in your
> table?
>
> - Gabriel
>
>
> On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in>
> wrote:
>
>> Thank You Gabriel for your response and will reply you soon with snapshot
>> of all becasue Table contain 466738 records so i will send you snapshot of
>> those who are missing in Phoenix.
>>
>> Can you tell me if i can perform same operation in different way as i
>> defined in my earlier mail.I hope you got me question
>>
>> Thanks , Kunal
>>
>>
>> On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com>
>> wrote:
>>
>>> Hi Kunal,
>>>
>>> I think you'll need to post some additional information to get an
>>> answer to your question. You said that MySQL returns 35 rows and
>>> Phoenix returns 32 rows, but it's not clear from your description what
>>> the rows are that are missing from the Phoenix result, or what it is
>>> that makes the Phoenix result incorrect and the MySQL result correct.
>>>
>>> The best approach to resolving this would be to create a minimal case,
>>> with a smaller number of rows, that demonstrates the issue that you're
>>> encountering.
>>>
>>> - Gabriel
>>>
>>>
>>> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
>>> wrote:
>>> > I have a table named as EVENTLOG which has 3 columns:
>>> >
>>> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS
>>> VARCHAR(200))
>>> >
>>> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query
>>> that
>>> > will work on Apache phoenix as well as for MySQL.
>>> >
>>> > SELECT DISTINCT E1.SUBSTATUS
>>> > FROM EVENTLOG AS E1
>>> > WHERE E1.DATEPLUSTIME IN
>>> > (SELECT MIN(E2.DATEPLUSTIME)
>>> > FROM EVENTLOG AS E2
>>> > GROUP BY E2.CASEID);
>>> >
>>> > Table contains 466738 records. When i perform the above query in MySQL
>>> , i
>>> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing
>>> 3
>>> > rows why is there difference in their result.
>>> > Table looks like the following(Records Striped).
>>> >
>>> > +----+---+---+---+--+----------+----+-----------+
>>> > | CASEID | DATEPLUSTIME | SUBSTATUS |
>>> > +----+---+---+---+--------+-----+---+-----------+
>>> > | 1 | 2010-04-10 18:20:38 | Assignment|
>>> > | 1 | 2010-04-10 20:12:56 | Update |
>>> > | 1 | 2010-11-23 23:12:34 | Assigned |
>>> > | 1 | 2010-11-25 23:45:34 | Assigned |
>>> > | 2 | 2010-12-30 12:32:23 | Reassigned|
>>> > | 2 | 2014-12-30 12:34:21 | Operator |
>>> > | 2 | 2014-12-25 20:32:45 | Operator |
>>> > | 3 | 2014-12-26 08:45:54 | Progress |
>>> > | 3 | 2014-12-30 08:49:32 | Call |
>>> > +----+---+---+---+---+----+------+--+-----------+
>>> > [Expected Output]
>>> >
>>> > Assignment
>>> > Reassigned
>>> > Progress
>>> >
>>> > MySQL does the task correctly butr Phoenix return incomplete result.
>>> > Please help me.
>>> >
>>> >
>>> > Thanks , Kunal
>>> >
>>>
>>
>>
>
Re: Query returning different results in Apache Phoenix and MySQL
Posted by Gabriel Reid <ga...@gmail.com>.
Kunal,
Just to clarify: I didn't mean that you should post (or send) the full
contents of your table. Instead, I mean you should make a minimal test case
(with a minimal number of records) that demonstrates the issue. For
example, do you get unexpected results if you only have 10 records in your
table?
- Gabriel
On Mon, Jan 12, 2015 at 10:58 AM, Kunal Gupta <ku...@iiitd.ac.in> wrote:
> Thank You Gabriel for your response and will reply you soon with snapshot
> of all becasue Table contain 466738 records so i will send you snapshot of
> those who are missing in Phoenix.
>
> Can you tell me if i can perform same operation in different way as i
> defined in my earlier mail.I hope you got me question
>
> Thanks , Kunal
>
>
> On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com>
> wrote:
>
>> Hi Kunal,
>>
>> I think you'll need to post some additional information to get an
>> answer to your question. You said that MySQL returns 35 rows and
>> Phoenix returns 32 rows, but it's not clear from your description what
>> the rows are that are missing from the Phoenix result, or what it is
>> that makes the Phoenix result incorrect and the MySQL result correct.
>>
>> The best approach to resolving this would be to create a minimal case,
>> with a smaller number of rows, that demonstrates the issue that you're
>> encountering.
>>
>> - Gabriel
>>
>>
>> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
>> wrote:
>> > I have a table named as EVENTLOG which has 3 columns:
>> >
>> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS
>> VARCHAR(200))
>> >
>> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query
>> that
>> > will work on Apache phoenix as well as for MySQL.
>> >
>> > SELECT DISTINCT E1.SUBSTATUS
>> > FROM EVENTLOG AS E1
>> > WHERE E1.DATEPLUSTIME IN
>> > (SELECT MIN(E2.DATEPLUSTIME)
>> > FROM EVENTLOG AS E2
>> > GROUP BY E2.CASEID);
>> >
>> > Table contains 466738 records. When i perform the above query in MySQL
>> , i
>> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing 3
>> > rows why is there difference in their result.
>> > Table looks like the following(Records Striped).
>> >
>> > +----+---+---+---+--+----------+----+-----------+
>> > | CASEID | DATEPLUSTIME | SUBSTATUS |
>> > +----+---+---+---+--------+-----+---+-----------+
>> > | 1 | 2010-04-10 18:20:38 | Assignment|
>> > | 1 | 2010-04-10 20:12:56 | Update |
>> > | 1 | 2010-11-23 23:12:34 | Assigned |
>> > | 1 | 2010-11-25 23:45:34 | Assigned |
>> > | 2 | 2010-12-30 12:32:23 | Reassigned|
>> > | 2 | 2014-12-30 12:34:21 | Operator |
>> > | 2 | 2014-12-25 20:32:45 | Operator |
>> > | 3 | 2014-12-26 08:45:54 | Progress |
>> > | 3 | 2014-12-30 08:49:32 | Call |
>> > +----+---+---+---+---+----+------+--+-----------+
>> > [Expected Output]
>> >
>> > Assignment
>> > Reassigned
>> > Progress
>> >
>> > MySQL does the task correctly butr Phoenix return incomplete result.
>> > Please help me.
>> >
>> >
>> > Thanks , Kunal
>> >
>>
>
>
Re: Query returning different results in Apache Phoenix and MySQL
Posted by Kunal Gupta <ku...@iiitd.ac.in>.
Thank You Gabriel for your response and will reply you soon with snapshot
of all becasue Table contain 466738 records so i will send you snapshot of
those who are missing in Phoenix.
Can you tell me if i can perform same operation in different way as i
defined in my earlier mail.I hope you got me question
Thanks , Kunal
On Mon, Jan 12, 2015 at 1:20 PM, Gabriel Reid <ga...@gmail.com>
wrote:
> Hi Kunal,
>
> I think you'll need to post some additional information to get an
> answer to your question. You said that MySQL returns 35 rows and
> Phoenix returns 32 rows, but it's not clear from your description what
> the rows are that are missing from the Phoenix result, or what it is
> that makes the Phoenix result incorrect and the MySQL result correct.
>
> The best approach to resolving this would be to create a minimal case,
> with a smaller number of rows, that demonstrates the issue that you're
> encountering.
>
> - Gabriel
>
>
> On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in>
> wrote:
> > I have a table named as EVENTLOG which has 3 columns:
> >
> > (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS VARCHAR(200))
> >
> > DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query that
> > will work on Apache phoenix as well as for MySQL.
> >
> > SELECT DISTINCT E1.SUBSTATUS
> > FROM EVENTLOG AS E1
> > WHERE E1.DATEPLUSTIME IN
> > (SELECT MIN(E2.DATEPLUSTIME)
> > FROM EVENTLOG AS E2
> > GROUP BY E2.CASEID);
> >
> > Table contains 466738 records. When i perform the above query in MySQL ,
> i
> > get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing 3
> > rows why is there difference in their result.
> > Table looks like the following(Records Striped).
> >
> > +----+---+---+---+--+----------+----+-----------+
> > | CASEID | DATEPLUSTIME | SUBSTATUS |
> > +----+---+---+---+--------+-----+---+-----------+
> > | 1 | 2010-04-10 18:20:38 | Assignment|
> > | 1 | 2010-04-10 20:12:56 | Update |
> > | 1 | 2010-11-23 23:12:34 | Assigned |
> > | 1 | 2010-11-25 23:45:34 | Assigned |
> > | 2 | 2010-12-30 12:32:23 | Reassigned|
> > | 2 | 2014-12-30 12:34:21 | Operator |
> > | 2 | 2014-12-25 20:32:45 | Operator |
> > | 3 | 2014-12-26 08:45:54 | Progress |
> > | 3 | 2014-12-30 08:49:32 | Call |
> > +----+---+---+---+---+----+------+--+-----------+
> > [Expected Output]
> >
> > Assignment
> > Reassigned
> > Progress
> >
> > MySQL does the task correctly butr Phoenix return incomplete result.
> > Please help me.
> >
> >
> > Thanks , Kunal
> >
>
Re: Query returning different results in Apache Phoenix and MySQL
Posted by Gabriel Reid <ga...@gmail.com>.
Hi Kunal,
I think you'll need to post some additional information to get an
answer to your question. You said that MySQL returns 35 rows and
Phoenix returns 32 rows, but it's not clear from your description what
the rows are that are missing from the Phoenix result, or what it is
that makes the Phoenix result incorrect and the MySQL result correct.
The best approach to resolving this would be to create a minimal case,
with a smaller number of rows, that demonstrates the issue that you're
encountering.
- Gabriel
On Mon, Jan 12, 2015 at 8:43 AM, Kunal Gupta <ku...@iiitd.ac.in> wrote:
> I have a table named as EVENTLOG which has 3 columns:
>
> (CASEID VARCHAR(200), DATEPLUSTIME TIMESTAMP, SUBSTATUS VARCHAR(200))
>
> DATEPLUSTIME is in format of YYYY-MM-DD hh:mm:ss. Below is the query that
> will work on Apache phoenix as well as for MySQL.
>
> SELECT DISTINCT E1.SUBSTATUS
> FROM EVENTLOG AS E1
> WHERE E1.DATEPLUSTIME IN
> (SELECT MIN(E2.DATEPLUSTIME)
> FROM EVENTLOG AS E2
> GROUP BY E2.CASEID);
>
> Table contains 466738 records. When i perform the above query in MySQL , i
> get 35 rows of it but on Apache Phoenix i get 32 rows so it is missing 3
> rows why is there difference in their result.
> Table looks like the following(Records Striped).
>
> +----+---+---+---+--+----------+----+-----------+
> | CASEID | DATEPLUSTIME | SUBSTATUS |
> +----+---+---+---+--------+-----+---+-----------+
> | 1 | 2010-04-10 18:20:38 | Assignment|
> | 1 | 2010-04-10 20:12:56 | Update |
> | 1 | 2010-11-23 23:12:34 | Assigned |
> | 1 | 2010-11-25 23:45:34 | Assigned |
> | 2 | 2010-12-30 12:32:23 | Reassigned|
> | 2 | 2014-12-30 12:34:21 | Operator |
> | 2 | 2014-12-25 20:32:45 | Operator |
> | 3 | 2014-12-26 08:45:54 | Progress |
> | 3 | 2014-12-30 08:49:32 | Call |
> +----+---+---+---+---+----+------+--+-----------+
> [Expected Output]
>
> Assignment
> Reassigned
> Progress
>
> MySQL does the task correctly butr Phoenix return incomplete result.
> Please help me.
>
>
> Thanks , Kunal
>