Inner join Bug?

[jxppp.liu]

Hi,
Running Env
1. CloudBase 1.3.1 & Hadoop 0.19.0

I created two tables.
table_A & table_B, same struction

table_A( F_A int, F_B int, F_C int ) COLUMN SEP ' ' IMPORT DATA
FROM '/home/jxppp/CloudData/1.txt'
table_B( F_A int, F_B int, F_C int ) COLUMN SEP ' ' IMPORT DATA
FROM '/home/jxppp/CloudData/2.txt'

1.txt Contents:
70 1 1
70 2 2
70 3 3

2.txt Contents:
70 1 1
70 1 2
70 2 2
70 2 3
70 2 1
70 3 1
70 3 2

Running SQL:
select A.F_A,A.F_B,count(distinct B.F_C) from TABLE_A A inner join
TABLE_B B on A.F_A = B.F_A and A.F_B = B.F_B group by A.F_A,A.F_B;

Result:
F_A,F_B,alias-1097957495
70, 1, 2
only one record.

the more detail running log:
GOT SQL: select A.F_A,A.F_B,count(distinct B.F_C) from TABLE_A A inner
join TABLE_B B on A.F_A = B.F_A and A.F_B = B.F_B group by A.F_A,A.F_B
10/04/19 18:49:17 WARN mapred.JobClient: Use GenericOptionsParser for
parsing the arguments. Applications should implement Tool for the
same.
10/04/19 18:49:17 INFO mapred.FileInputFormat: Total input paths to
process : 1
10/04/19 18:49:18 INFO mapred.JobClient: Running job:
job_201004191815_0012
10/04/19 18:49:19 INFO mapred.JobClient: map 0% reduce 0%
10/04/19 18:49:23 INFO mapred.JobClient: map 50% reduce 0%
10/04/19 18:49:25 INFO mapred.JobClient: map 100% reduce 0%
10/04/19 18:49:29 INFO mapred.JobClient: map 100% reduce 33%
10/04/19 18:49:32 INFO mapred.JobClient: map 100% reduce 66%
10/04/19 18:49:33 INFO mapred.JobClient: map 100% reduce 72%
10/04/19 18:49:39 INFO mapred.JobClient: map 100% reduce 100%
10/04/19 18:49:41 INFO mapred.JobClient: Job complete:
job_201004191815_0012
10/04/19 18:49:41 INFO mapred.JobClient: Counters: 16
10/04/19 18:49:41 INFO mapred.JobClient: File Systems
10/04/19 18:49:41 INFO mapred.JobClient: HDFS bytes read=48
10/04/19 18:49:41 INFO mapred.JobClient: HDFS bytes written=675
10/04/19 18:49:41 INFO mapred.JobClient: Local bytes read=60
10/04/19 18:49:41 INFO mapred.JobClient: Local bytes written=290
10/04/19 18:49:41 INFO mapred.JobClient: Job Counters
10/04/19 18:49:41 INFO mapred.JobClient: Launched reduce tasks=3
10/04/19 18:49:41 INFO mapred.JobClient: Launched map tasks=2
10/04/19 18:49:41 INFO mapred.JobClient: Data-local map tasks=2
10/04/19 18:49:41 INFO mapred.JobClient: Map-Reduce Framework
10/04/19 18:49:41 INFO mapred.JobClient: Reduce input groups=3
10/04/19 18:49:41 INFO mapred.JobClient: Combine output records=0
10/04/19 18:49:41 INFO mapred.JobClient: Map input records=3
10/04/19 18:49:41 INFO mapred.JobClient: Reduce output records=3
10/04/19 18:49:41 INFO mapred.JobClient: Map output bytes=36
10/04/19 18:49:41 INFO mapred.JobClient: Map input bytes=21
10/04/19 18:49:41 INFO mapred.JobClient: Combine input records=0
10/04/19 18:49:41 INFO mapred.JobClient: Map output records=3
10/04/19 18:49:41 INFO mapred.JobClient: Reduce input records=3
10/04/19 18:49:41 WARN mapred.JobClient: Use GenericOptionsParser for
parsing the arguments. Applications should implement Tool for the
same.
10/04/19 18:49:41 INFO mapred.FileInputFormat: Total input paths to
process : 1
10/04/19 18:49:42 INFO mapred.JobClient: Running job:
job_201004191815_0013
10/04/19 18:49:43 INFO mapred.JobClient: map 0% reduce 0%
10/04/19 18:49:51 INFO mapred.JobClient: map 50% reduce 0%
10/04/19 18:49:52 INFO mapred.JobClient: map 100% reduce 0%
10/04/19 18:49:57 INFO mapred.JobClient: map 100% reduce 33%
10/04/19 18:49:59 INFO mapred.JobClient: map 100% reduce 66%
10/04/19 18:50:02 INFO mapred.JobClient: map 100% reduce 100%
10/04/19 18:50:04 INFO mapred.JobClient: Job complete:
job_201004191815_0013
10/04/19 18:50:04 INFO mapred.JobClient: Counters: 16
10/04/19 18:50:04 INFO mapred.JobClient: File Systems
10/04/19 18:50:04 INFO mapred.JobClient: HDFS bytes read=199
10/04/19 18:50:04 INFO mapred.JobClient: HDFS bytes written=28
10/04/19 18:50:04 INFO mapred.JobClient: Local bytes read=116
10/04/19 18:50:04 INFO mapred.JobClient: Local bytes written=402
10/04/19 18:50:04 INFO mapred.JobClient: Job Counters
10/04/19 18:50:04 INFO mapred.JobClient: Launched reduce tasks=3
10/04/19 18:50:04 INFO mapred.JobClient: Launched map tasks=2
10/04/19 18:50:04 INFO mapred.JobClient: Data-local map tasks=2
10/04/19 18:50:04 INFO mapred.JobClient: Map-Reduce Framework
10/04/19 18:50:04 INFO mapred.JobClient: Reduce input groups=3
10/04/19 18:50:04 INFO mapred.JobClient: Combine output records=0
10/04/19 18:50:04 INFO mapred.JobClient: Map input records=7
10/04/19 18:50:04 INFO mapred.JobClient: Reduce output records=2
10/04/19 18:50:04 INFO mapred.JobClient: Map output bytes=84
10/04/19 18:50:04 INFO mapred.JobClient: Map input bytes=49
10/04/19 18:50:04 INFO mapred.JobClient: Combine input records=0
10/04/19 18:50:04 INFO mapred.JobClient: Map output records=7
10/04/19 18:50:04 INFO mapred.JobClient: Reduce input records=2
10/04/19 18:50:04 WARN mapred.JobClient: Use GenericOptionsParser for
parsing the arguments. Applications should implement Tool for the
same.
10/04/19 18:50:04 INFO mapred.FileInputFormat: Total input paths to
process : 3
10/04/19 18:50:04 INFO mapred.JobClient: Running job:
job_201004191815_0014
10/04/19 18:50:05 INFO mapred.JobClient: map 0% reduce 0%
10/04/19 18:50:12 INFO mapred.JobClient: map 25% reduce 0%
10/04/19 18:50:13 INFO mapred.JobClient: map 50% reduce 0%
10/04/19 18:50:14 INFO mapred.JobClient: map 75% reduce 0%
10/04/19 18:50:16 INFO mapred.JobClient: map 100% reduce 0%
10/04/19 18:50:23 INFO mapred.JobClient: map 100% reduce 33%
10/04/19 18:50:24 INFO mapred.JobClient: map 100% reduce 66%
10/04/19 18:50:27 INFO mapred.JobClient: map 100% reduce 100%
10/04/19 18:50:30 INFO mapred.JobClient: Job complete:
job_201004191815_0014
10/04/19 18:50:30 INFO mapred.JobClient: Counters: 16
10/04/19 18:50:30 INFO mapred.JobClient: File Systems
10/04/19 18:50:30 INFO mapred.JobClient: HDFS bytes read=43
10/04/19 18:50:30 INFO mapred.JobClient: HDFS bytes written=18
10/04/19 18:50:30 INFO mapred.JobClient: Local bytes read=44
10/04/19 18:50:30 INFO mapred.JobClient: Local bytes written=446
10/04/19 18:50:30 INFO mapred.JobClient: Job Counters
10/04/19 18:50:30 INFO mapred.JobClient: Launched reduce tasks=3
10/04/19 18:50:30 INFO mapred.JobClient: Launched map tasks=4
10/04/19 18:50:30 INFO mapred.JobClient: Data-local map tasks=2
10/04/19 18:50:30 INFO mapred.JobClient: Map-Reduce Framework
10/04/19 18:50:30 INFO mapred.JobClient: Reduce input groups=2
10/04/19 18:50:30 INFO mapred.JobClient: Combine output records=2
10/04/19 18:50:30 INFO mapred.JobClient: Map input records=2
10/04/19 18:50:30 INFO mapred.JobClient: Reduce output records=2
10/04/19 18:50:30 INFO mapred.JobClient: Map output bytes=22
10/04/19 18:50:30 INFO mapred.JobClient: Map input bytes=28
10/04/19 18:50:30 INFO mapred.JobClient: Combine input records=0
10/04/19 18:50:30 INFO mapred.JobClient: Map output records=2
10/04/19 18:50:30 INFO mapred.JobClient: Reduce input records=0
10/04/19 18:50:30 WARN mapred.JobClient: Use GenericOptionsParser for
parsing the arguments. Applications should implement Tool for the
same.
10/04/19 18:50:30 INFO mapred.FileInputFormat: Total input paths to
process : 3
10/04/19 18:50:30 INFO mapred.JobClient: Running job:
job_201004191815_0015
10/04/19 18:50:31 INFO mapred.JobClient: map 0% reduce 0%
10/04/19 18:50:37 INFO mapred.JobClient: map 33% reduce 0%
10/04/19 18:50:38 INFO mapred.JobClient: map 66% reduce 0%
10/04/19 18:50:39 INFO mapred.JobClient: map 100% reduce 0%
10/04/19 18:50:47 INFO mapred.JobClient: map 100% reduce 33%
10/04/19 18:50:48 INFO mapred.JobClient: map 100% reduce 66%
10/04/19 18:50:52 INFO mapred.JobClient: map 100% reduce 100%
10/04/19 18:50:54 INFO mapred.JobClient: Job complete:
job_201004191815_0015
10/04/19 18:50:54 INFO mapred.JobClient: Counters: 16
10/04/19 18:50:54 INFO mapred.JobClient: File Systems
10/04/19 18:50:54 INFO mapred.JobClient: HDFS bytes read=18
10/04/19 18:50:54 INFO mapred.JobClient: HDFS bytes written=9
10/04/19 18:50:54 INFO mapred.JobClient: Local bytes read=40
10/04/19 18:50:54 INFO mapred.JobClient: Local bytes written=344
10/04/19 18:50:54 INFO mapred.JobClient: Job Counters
10/04/19 18:50:54 INFO mapred.JobClient: Launched reduce tasks=3
10/04/19 18:50:54 INFO mapred.JobClient: Launched map tasks=3
10/04/19 18:50:54 INFO mapred.JobClient: Data-local map tasks=2
10/04/19 18:50:54 INFO mapred.JobClient: Map-Reduce Framework
10/04/19 18:50:54 INFO mapred.JobClient: Reduce input groups=1
10/04/19 18:50:54 INFO mapred.JobClient: Combine output records=2
10/04/19 18:50:54 INFO mapred.JobClient: Map input records=2
10/04/19 18:50:54 INFO mapred.JobClient: Reduce output records=1
10/04/19 18:50:54 INFO mapred.JobClient: Map output bytes=18
10/04/19 18:50:54 INFO mapred.JobClient: Map input bytes=18
10/04/19 18:50:54 INFO mapred.JobClient: Combine input records=2
10/04/19 18:50:54 INFO mapred.JobClient: Map output records=2
10/04/19 18:50:54 INFO mapred.JobClient: Reduce input records=2
10/04/19 18:50:54 WARN mapred.JobClient: Use GenericOptionsParser for
parsing the arguments. Applications should implement Tool for the
same.
10/04/19 18:50:54 INFO mapred.FileInputFormat: Total input paths to
process : 3
10/04/19 18:50:55 INFO mapred.JobClient: Running job:
job_201004191815_0016
10/04/19 18:50:56 INFO mapred.JobClient: map 0% reduce 0%
10/04/19 18:51:01 INFO mapred.JobClient: map 20% reduce 0%
10/04/19 18:51:03 INFO mapred.JobClient: map 40% reduce 0%
10/04/19 18:51:04 INFO mapred.JobClient: map 60% reduce 0%
10/04/19 18:51:05 INFO mapred.JobClient: map 80% reduce 0%
10/04/19 18:51:06 INFO mapred.JobClient: map 100% reduce 0%
10/04/19 18:51:14 INFO mapred.JobClient: map 100% reduce 33%
10/04/19 18:51:16 INFO mapred.JobClient: map 100% reduce 66%
10/04/19 18:51:20 INFO mapred.JobClient: map 100% reduce 100%
10/04/19 18:51:22 INFO mapred.JobClient: Job complete:
job_201004191815_0016
10/04/19 18:51:22 INFO mapred.JobClient: Counters: 16
10/04/19 18:51:22 INFO mapred.JobClient: File Systems
10/04/19 18:51:22 INFO mapred.JobClient: HDFS bytes read=17
10/04/19 18:51:22 INFO mapred.JobClient: HDFS bytes written=7
10/04/19 18:51:22 INFO mapred.JobClient: Local bytes read=29
10/04/19 18:51:22 INFO mapred.JobClient: Local bytes written=510
10/04/19 18:51:22 INFO mapred.JobClient: Job Counters
10/04/19 18:51:22 INFO mapred.JobClient: Launched reduce tasks=3
10/04/19 18:51:22 INFO mapred.JobClient: Launched map tasks=5
10/04/19 18:51:22 INFO mapred.JobClient: Data-local map tasks=3
10/04/19 18:51:22 INFO mapred.JobClient: Map-Reduce Framework
10/04/19 18:51:22 INFO mapred.JobClient: Reduce input groups=1
10/04/19 18:51:22 INFO mapred.JobClient: Combine output records=0
10/04/19 18:51:22 INFO mapred.JobClient: Map input records=1
10/04/19 18:51:22 INFO mapred.JobClient: Reduce output records=1
10/04/19 18:51:22 INFO mapred.JobClient: Map output bytes=9
10/04/19 18:51:22 INFO mapred.JobClient: Map input bytes=9
10/04/19 18:51:22 INFO mapred.JobClient: Combine input records=0
10/04/19 18:51:22 INFO mapred.JobClient: Map output records=1
10/04/19 18:51:22 INFO mapred.JobClient: Reduce input records=1

--
You received this message because you are subscribed to the Google Groups "CloudBase" group.
To post to this group, send email to cloudba...@googlegroups.com.
To unsubscribe from this group, send email to cloudbase-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cloudbase-users?hl=en.

[yanbo]

I tested on my computer. It gave the correct results.

select * from TABLE_A A inner join

TABLE_B B on A.F_A = B.F_A and A.F_B = B.F_B

Result is

F_A F_B F_B F_A F_B F_B
70 2 2 70 2 2

70 2 2 70 2 3

70 2 2 70 2 1
70 3 3 70 3 1
70 3 3 70 3 2
70 1 1 70 1 1

70 1 1 70 1 2

select A.F_A,A.F_B,count(distinct B.F_C) from TABLE_A A inner join
TABLE_B B on A.F_A = B.F_A and A.F_B = B.F_B group by A.F_A,A.F_B;

result is

F_A F_B null
70 3 2
70 1 2
70 2 3

I use CloudBase 1.3.1 and Hadoop 0.20.1

Can you try

select * from TABLE_A A inner join

TABLE_B B on A.F_A = B.F_A and A.F_B = B.F_B

and see what the result is?

Yanbo

[jxppp.liu]

Hi,
below is the result:
----------------------------------------------------------------------------------

select * from TABLE_A A inner join TABLE_B B on A.F_A = B.F_A and
A.F_B = B.F_B

F_A F_B F_B F_A F_B F_B

70 1 1 70 1 1
70 1 1 70 1 2

----------------------------------------------------------------------------------
and i got the correct result when changed the hadoop param
<mapred.reduce.tasks> from 3 to 1.

[yanbo]

Can you try select * from table_a and select * from table_b? I guess
there may be some mistake in your data files, like an extra space
between the numbers.

[yanbo]

Please ignore my previous post. If you can got the correct result by
changing <mapred.reduce.tasks> from 3 to 1, your data files should be
fine. I will look at the source code and see what's the problem.

[yanbo]

Hi, Liu,

Are you running hadoop on a cluster or single machine? I tried both
and they all gave correct results. On single machine, I also tried to
set mapred.reduce.tasks to 1, 2, and 3. All of them worked well.

Can you try the query on some other data?

[jxppp.liu]

Hi,Yanbo:

it running @ hadoop cluster(3 machines).
i will try it more.

thanks

jxppp

[jxppp.liu]

Hi,

test result belows

same data.
hadoop cluster(3 machines)

SQL List:
1) select * from TABLE_A A inner join TABLE_B B on A.F_A = B.F_A
and A.F_B = B.F_B
2) select * from TABLE_A
3) select * from TABLE_B
4) select count(distinct F_C) from TABLE_B
-----------------------------------
mapred.reduce.tasks=1:
all sqls get correct results.
mapred.reduce.tasks=2:
1) select * from TABLE_A A inner join TABLE_B B on A.F_A = B.F_A
and A.F_B = B.F_B ERROR
2) select * from TABLE_A CORRECT
3) select * from TABLE_B CORRECT
4) select count(distinct F_C) from TABLE_B CORRECT
mapred.reduce.tasks=3:
1) select * from TABLE_A A inner join TABLE_B B on A.F_A = B.F_A
and A.F_B = B.F_B ERROR
2) select * from TABLE_A CORRECT
3) select * from TABLE_B CORRECT
4) select count(distinct F_C) from TABLE_B CORRECT

thanks

[Youngwoo Kim]

Hi,

Can you take a look at your hadoop configuration? core-site.xml,
mapred-site.xml. if there are any differences between 3 nodes, you'll
get a error from join queries in some cases. I have seen this before,
but I dont know why.
So Verify your nodes have same configurations. if not, sync the
configurations and try again.

-Youngwoo

[jxppp.liu]

Hi Youngwoo,

after synced thehadoop configuration(3
nodes,<mapred.reduce.tasks>=3) and run these sqls ,got the correct
results.

so. why? anyone knows?

thank you for the reply.

thanks

jxppp