[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13493945#comment-13493945 ] Namit Jain commented on HIVE-3633: -- https://reviews.facebook.net/D6615 > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.1.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13498230#comment-13498230 ] Namit Jain commented on HIVE-3633: -- running tests > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13506732#comment-13506732 ] Kevin Wilfong commented on HIVE-3633: - Added a bunch of comments on the diff. It's mostly minor things and questions to make sure I understand the logic. > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch, > hive.3633.4.patch, hive.3633.5.patch, hive.3633.6.patch, hive.3633.7.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13508574#comment-13508574 ] Namit Jain commented on HIVE-3633: -- comments addressed, and ran tests > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch, > hive.3633.4.patch, hive.3633.5.patch, hive.3633.6.patch, hive.3633.7.patch, > hive.3633.8.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13510084#comment-13510084 ] Kevin Wilfong commented on HIVE-3633: - +1 > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch, > hive.3633.4.patch, hive.3633.5.patch, hive.3633.6.patch, hive.3633.7.patch, > hive.3633.8.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13531326#comment-13531326 ] Kevin Wilfong commented on HIVE-3633: - Namit, can you refresh one more time. > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch, > hive.3633.4.patch, hive.3633.5.patch, hive.3633.6.patch, hive.3633.7.patch, > hive.3633.8.patch, hive.3633.9.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13534142#comment-13534142 ] Kevin Wilfong commented on HIVE-3633: - Thanks Namit, running tests. > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.10.patch, hive.3633.1.patch, > hive.3633.2.patch, hive.3633.3.patch, hive.3633.4.patch, hive.3633.5.patch, > hive.3633.6.patch, hive.3633.7.patch, hive.3633.8.patch, hive.3633.9.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13534334#comment-13534334 ] Kevin Wilfong commented on HIVE-3633: - The output of the new testcase appears to be nondeterministic. Can you update. I pointed out one nondeterministic query in Phabricator. > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Attachments: hive.3633.10.patch, hive.3633.1.patch, > hive.3633.2.patch, hive.3633.3.patch, hive.3633.4.patch, hive.3633.5.patch, > hive.3633.6.patch, hive.3633.7.patch, hive.3633.8.patch, hive.3633.9.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13535954#comment-13535954 ] Hudson commented on HIVE-3633: -- Integrated in Hive-trunk-h0.21 #1865 (See [https://builds.apache.org/job/Hive-trunk-h0.21/1865/]) HIVE-3633. sort-merge join does not work with sub-queries. (njain via kevinwilfong) (Revision 1423731) Result = SUCCESS kevinwilfong : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1423731 Files : * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/DummyStoreOperator.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/OperatorFactory.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/SMBMapJoinOperator.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/AbstractBucketJoinProc.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/BucketMapJoinOptimizer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/SortedMergeBucketMapJoinOptimizer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QB.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBJoinTree.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/TableAccessAnalyzer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/DummyStoreDesc.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/SMBJoinDesc.java * /hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_14.q * /hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_14.q.out > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Fix For: 0.11 > > Attachments: hive.3633.10.patch, hive.3633.11.patch, > hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch, hive.3633.4.patch, > hive.3633.5.patch, hive.3633.6.patch, hive.3633.7.patch, hive.3633.8.patch, > hive.3633.9.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3633) sort-merge join does not work with sub-queries
[ https://issues.apache.org/jira/browse/HIVE-3633?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13548340#comment-13548340 ] Hudson commented on HIVE-3633: -- Integrated in Hive-trunk-hadoop2 #54 (See [https://builds.apache.org/job/Hive-trunk-hadoop2/54/]) HIVE-3633. sort-merge join does not work with sub-queries. (njain via kevinwilfong) (Revision 1423731) Result = ABORTED kevinwilfong : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1423731 Files : * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/DummyStoreOperator.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/OperatorFactory.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/exec/SMBMapJoinOperator.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/AbstractBucketJoinProc.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/BucketMapJoinOptimizer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/optimizer/SortedMergeBucketMapJoinOptimizer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QB.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBJoinTree.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/TableAccessAnalyzer.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/DummyStoreDesc.java * /hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/plan/SMBJoinDesc.java * /hive/trunk/ql/src/test/queries/clientpositive/smb_mapjoin_14.q * /hive/trunk/ql/src/test/results/clientpositive/smb_mapjoin_14.q.out > sort-merge join does not work with sub-queries > -- > > Key: HIVE-3633 > URL: https://issues.apache.org/jira/browse/HIVE-3633 > Project: Hive > Issue Type: Improvement > Components: Query Processor >Reporter: Namit Jain >Assignee: Namit Jain > Fix For: 0.11.0 > > Attachments: hive.3633.10.patch, hive.3633.11.patch, > hive.3633.1.patch, hive.3633.2.patch, hive.3633.3.patch, hive.3633.4.patch, > hive.3633.5.patch, hive.3633.6.patch, hive.3633.7.patch, hive.3633.8.patch, > hive.3633.9.patch > > > Consider the following query: > create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY > (key) INTO 6 BUCKETS STORED AS TEXTFILE; > -- load the above tables > set hive.optimize.bucketmapjoin = true; > set hive.optimize.bucketmapjoin.sortedmerge = true; > set hive.input.format = > org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; > explain > select count(*) from > ( > select /*+mapjoin(a)*/ a.key as key1, b.key as key2, a.value as value1, > b.value as value2 > from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key) > subq; > The above query does not use sort-merge join. This would be very useful as we > automatically convert the queries to use sorting and bucketing properties for > join. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
