PREHOOK: query: DESCRIBE FUNCTION find_in_set PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION find_in_set POSTHOOK: type: DESCFUNCTION find_in_set(str,str_array) - Returns the first occurrence of str in str_array where str_array is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument has any commas. PREHOOK: query: DESCRIBE FUNCTION EXTENDED find_in_set PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION EXTENDED find_in_set POSTHOOK: type: DESCFUNCTION find_in_set(str,str_array) - Returns the first occurrence of str in str_array where str_array is a comma-delimited string. Returns null if either argument is null. Returns 0 if the first argument has any commas. Example: > SELECT find_in_set('ab','abc,b,ab,c,def') FROM src LIMIT 1; 3 > SELECT * FROM src1 WHERE NOT find_in_set(key,'311,128,345,956')=0; 311 val_311 128 PREHOOK: query: EXPLAIN FROM src1 SELECT find_in_set(src1.key,concat(src1.key,',',src1.value)) PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN FROM src1 SELECT find_in_set(src1.key,concat(src1.key,',',src1.value)) POSTHOOK: type: QUERY ABSTRACT SYNTAX TREE: (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src1))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION find_in_set (. (TOK_TABLE_OR_COL src1) key) (TOK_FUNCTION concat (. (TOK_TABLE_OR_COL src1) key) ',' (. (TOK_TABLE_OR_COL src1) value))))))) STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 is a root stage STAGE PLANS: Stage: Stage-1 Map Reduce Alias -> Map Operator Tree: src1 TableScan alias: src1 Select Operator expressions: expr: find_in_set(key, concat(key, ',', value)) type: int outputColumnNames: _col0 File Output Operator compressed: false GlobalTableId: 0 table: input format: org.apache.hadoop.mapred.TextInputFormat output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Stage: Stage-0 Fetch Operator limit: -1 PREHOOK: query: FROM src1 SELECT find_in_set(src1.key,concat(src1.key,',',src1.value)) PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-33-55_315_646992624808133634/-mr-10000 POSTHOOK: query: FROM src1 SELECT find_in_set(src1.key,concat(src1.key,',',src1.value)) POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-33-55_315_646992624808133634/-mr-10000 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 PREHOOK: query: SELECT find_in_set('ab','ab,abc,abcde') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-33-58_557_1099694714647120556/-mr-10000 POSTHOOK: query: SELECT find_in_set('ab','ab,abc,abcde') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-33-58_557_1099694714647120556/-mr-10000 1 PREHOOK: query: SELECT find_in_set('ab','abc,ab,bbb') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-01_784_5100662308115228334/-mr-10000 POSTHOOK: query: SELECT find_in_set('ab','abc,ab,bbb') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-01_784_5100662308115228334/-mr-10000 2 PREHOOK: query: SELECT find_in_set('ab','def,abc,ab') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-05_088_2766630277132411005/-mr-10000 POSTHOOK: query: SELECT find_in_set('ab','def,abc,ab') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-05_088_2766630277132411005/-mr-10000 3 PREHOOK: query: SELECT find_in_set('ab','abc,abd,abf') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-08_355_7484586857404284998/-mr-10000 POSTHOOK: query: SELECT find_in_set('ab','abc,abd,abf') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-08_355_7484586857404284998/-mr-10000 0 PREHOOK: query: SELECT find_in_set(null,'a,b,c') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-11_587_8034874388616793686/-mr-10000 POSTHOOK: query: SELECT find_in_set(null,'a,b,c') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-11_587_8034874388616793686/-mr-10000 NULL PREHOOK: query: SELECT find_in_set('a',null) FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-14_812_4060097783920133679/-mr-10000 POSTHOOK: query: SELECT find_in_set('a',null) FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-14_812_4060097783920133679/-mr-10000 NULL PREHOOK: query: SELECT find_in_set('', '') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-18_075_7011831240496627705/-mr-10000 POSTHOOK: query: SELECT find_in_set('', '') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-18_075_7011831240496627705/-mr-10000 1 PREHOOK: query: SELECT find_in_set('',',') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-21_298_8289961390425950955/-mr-10000 POSTHOOK: query: SELECT find_in_set('',',') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-21_298_8289961390425950955/-mr-10000 1 PREHOOK: query: SELECT find_in_set('','a,,b') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-24_530_1884706057617469025/-mr-10000 POSTHOOK: query: SELECT find_in_set('','a,,b') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-24_530_1884706057617469025/-mr-10000 2 PREHOOK: query: SELECT find_in_set('','a,b,') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-27_748_373181277276761998/-mr-10000 POSTHOOK: query: SELECT find_in_set('','a,b,') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-27_748_373181277276761998/-mr-10000 3 PREHOOK: query: SELECT find_in_set(',','a,b,d,') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-31_013_4678805621418440320/-mr-10000 POSTHOOK: query: SELECT find_in_set(',','a,b,d,') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-31_013_4678805621418440320/-mr-10000 0 PREHOOK: query: SELECT find_in_set('a','') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-34_234_38101353986877599/-mr-10000 POSTHOOK: query: SELECT find_in_set('a','') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-34_234_38101353986877599/-mr-10000 0 PREHOOK: query: SELECT find_in_set('a,','a,b,c,d') FROM src1 LIMIT 1 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-37_474_556241634135286352/-mr-10000 POSTHOOK: query: SELECT find_in_set('a,','a,b,c,d') FROM src1 LIMIT 1 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-37_474_556241634135286352/-mr-10000 0 PREHOOK: query: SELECT * FROM src1 WHERE NOT find_in_set(key,'311,128,345,2,956')=0 PREHOOK: type: QUERY PREHOOK: Input: default@src1 PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-40_694_6717091611918281285/-mr-10000 POSTHOOK: query: SELECT * FROM src1 WHERE NOT find_in_set(key,'311,128,345,2,956')=0 POSTHOOK: type: QUERY POSTHOOK: Input: default@src1 POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_17-34-40_694_6717091611918281285/-mr-10000 311 val_311 128