You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Adam Kramer (JIRA)" <ji...@apache.org> on 2009/08/26 22:55:59 UTC
[jira] Created: (HIVE-801) row-wise IN would be useful
row-wise IN would be useful
---------------------------
Key: HIVE-801
URL: https://issues.apache.org/jira/browse/HIVE-801
Project: Hadoop Hive
Issue Type: New Feature
Reporter: Adam Kramer
SELECT * FROM tablename t
WHERE IN(12345,key1,key2,key3);
...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-801) row-wise IN would be useful
Posted by "Paul Yang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Yang updated HIVE-801:
---------------------------
Attachment: HIVE-801.2.patch
* Added comment about type conversion
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch, HIVE-801.2.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-801) row-wise IN would be useful
Posted by "Raghotham Murthy (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12748151#action_12748151 ]
Raghotham Murthy commented on HIVE-801:
---------------------------------------
Couldnt you do exactly the same thing with IF or CASE constructs in the WHERE clause? A general disjunctive join can be written as a UNION ALL of several join queries. And a general theta join can be done (inefficiently) with a cross product.
However, you should be able to achieve what you want in HIVE-783 in the ON clause by doing something like the following. Although its a little verbose, you end up with a single map-reduce job and is probably the most efficient way to doit.
JOIN ON (12345 = (case 12345 when key1 then 12345 when key2 then 12345 when key3 then 12345 else null end))
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-801) row-wise IN would be useful
Posted by "Paul Yang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12866335#action_12866335 ]
Paul Yang commented on HIVE-801:
--------------------------------
The issue with using GenericUDFField is that it requires that all argument types to be the same. e.g. field(1, "1") will return 0 because 1 is an integer while "1" is a string. My implementation of IN allows type conversions so that 1 IN ("1") returns true. If we want to have the same kind of type conversion behavior, then it's possible to merge the two.
Also, that test was to demonstrate the use case in the JIRA where there's an IN in the WHERE clause.
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (HIVE-801) row-wise IN would be useful
Posted by "Paul Yang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Yang reassigned HIVE-801:
------------------------------
Assignee: Paul Yang
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-801) row-wise IN would be useful
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12866317#action_12866317 ]
Namit Jain commented on HIVE-801:
---------------------------------
Do you want to reuse GenericUDFField.java - or create some common utility and use that.
Also, why do you have a test
SELECT key FROM src WHERE key IN ("238", 86);
in udf_in.q
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-801) row-wise IN would be useful
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12866601#action_12866601 ]
Namit Jain commented on HIVE-801:
---------------------------------
+1
will commit if the tests pass
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch, HIVE-801.2.patch, HIVE-801.3.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-801) row-wise IN would be useful
Posted by "Namit Jain (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Namit Jain updated HIVE-801:
----------------------------
Status: Resolved (was: Patch Available)
Hadoop Flags: [Reviewed]
Fix Version/s: 0.6.0
Resolution: Fixed
Committed. Thanks Paul
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Fix For: 0.6.0
>
> Attachments: HIVE-801.1.patch, HIVE-801.2.patch, HIVE-801.3.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-801) row-wise IN would be useful
Posted by "Paul Yang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Yang updated HIVE-801:
---------------------------
Attachment: HIVE-801.1.patch
This patch supports IN using the SQL syntax:
{code}
SELECT * FROM src WHERE key in(238, 11, ...)
{code}
Currently, we only support column names / expressions with in. For a case like WHERE a.foo IN (b.*) , we'll need to support column regexes like SELECT. I'm not sure if that is a big use case though, as most functions take a fixed number of arguments anyway.
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-801) row-wise IN would be useful
Posted by "Paul Yang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Yang updated HIVE-801:
---------------------------
Status: Patch Available (was: Open)
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-801) row-wise IN would be useful
Posted by "Adam Kramer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12839052#action_12839052 ]
Adam Kramer commented on HIVE-801:
----------------------------------
Also note that the true utility of this is syntax like
WHERE a.foo IN (b.*)
...for instances where b has many many columns and it is messy to articulate them. I'm thinking about a current table I have with 800 columns...is there a limit on the character-wise length of a query?
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-801) row-wise IN would be useful
Posted by "Paul Yang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Paul Yang updated HIVE-801:
---------------------------
Attachment: HIVE-801.3.patch
* Fixed build issue with Hadoop 0.17.2.1
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Attachments: HIVE-801.1.patch, HIVE-801.2.patch, HIVE-801.3.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (HIVE-801) row-wise IN would be useful
Posted by "Carl Steinbach (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Carl Steinbach updated HIVE-801:
--------------------------------
Affects Version/s: 0.5.0
0.4.1
0.4.0
0.3.0
Component/s: Query Processor
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Components: Query Processor
> Affects Versions: 0.3.0, 0.4.0, 0.4.1, 0.5.0
> Reporter: Adam Kramer
> Assignee: Paul Yang
> Fix For: 0.6.0
>
> Attachments: HIVE-801.1.patch, HIVE-801.2.patch, HIVE-801.3.patch
>
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-801) row-wise IN would be useful
Posted by "Adam Kramer (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12748188#action_12748188 ]
Adam Kramer commented on HIVE-801:
----------------------------------
Using UNION ALL doesn't do the same as a disjunctive join, though, because in the case of key1 = key2 = key3 = 12345, union all would end up with 3 rows in the output instead of just one row. If we can make assumptions about duplicate rows, we can sort and transform with 'uniq,' but sometimes duplicate rows are meaningful.
The CASE usage is interesting and intuitive, but throws an error: FAILED: Error in semantic analysis: line 3:38 Both Left and Right Aliases Encountered in Join 1. I don't really understand what this means, but it is the same error I get if I use an IF statement like IF(key1=12345 OR key2=12345,True,False), which was my first attempt to "fool" hive into doing a disjunctive join.
(also, ELSE NULL END throws a separate error of "TOK_NULL encountered with 0 children")
> row-wise IN would be useful
> ---------------------------
>
> Key: HIVE-801
> URL: https://issues.apache.org/jira/browse/HIVE-801
> Project: Hadoop Hive
> Issue Type: New Feature
> Reporter: Adam Kramer
>
> SELECT * FROM tablename t
> WHERE IN(12345,key1,key2,key3);
> ...IN would operate on a given row, and return True when the first argument equaled at least one of the other arguments. So here IN would return true if 12345=key1 OR 12345=key2 OR 12345=key3 (but wouldn't test the latter two if the first matched).
> This would also help with https://issues.apache.org/jira/browse/HIVE-783, if IN were implemented in a manner that allows it to be used in an ON clause.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.