ql/src/test/results/clientpositive/llap/subquery_in_invalid_intermediate_plan.q.out - hive - Git at Google

 PREHOOK: query: explain cbo
 select *
 from src b
 where b.key in (select a.key from src a where b.value = a.value and a.key > '9')
 PREHOOK: type: QUERY
 PREHOOK: Input: default@src
 #### A masked pattern was here ####
 POSTHOOK: query: explain cbo
 select *
 from src b
 where b.key in (select a.key from src a where b.value = a.value and a.key > '9')
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@src
 #### A masked pattern was here ####
 CBO PLAN:
 HiveSemiJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
   HiveProject(key=[$0], value=[$1])
     HiveFilter(condition=[AND(>($0, _UTF-16LE'9'), IS NOT NULL($1))])
       HiveTableScan(table=[[default, src]], table:alias=[b])
   HiveProject(key=[$0], value=[$1])
     HiveFilter(condition=[AND(>($0, _UTF-16LE'9'), IS NOT NULL($1))])
       HiveTableScan(table=[[default, src]], table:alias=[a])

 PREHOOK: query: explain cbo
 select count(*) as c
 from part as e
 where p_size + 100 IN (select p_partkey from part where p_name = e.p_name)
 PREHOOK: type: QUERY
 PREHOOK: Input: default@part
 #### A masked pattern was here ####
 POSTHOOK: query: explain cbo
 select count(*) as c
 from part as e
 where p_size + 100 IN (select p_partkey from part where p_name = e.p_name)
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@part
 #### A masked pattern was here ####
 CBO PLAN:
 HiveAggregate(group=[{}], agg#0=[count()])
   HiveSemiJoin(condition=[AND(=($3, $0), =($1, $2))], joinType=[semi])
     HiveProject(p_name=[$1], $f13=[+($5, 100)])
       HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($5))])
         HiveTableScan(table=[[default, part]], table:alias=[e])
     HiveProject(p_partkey=[$0], p_name=[$1])
       HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
         HiveTableScan(table=[[default, part]], table:alias=[part])

 PREHOOK: query: explain cbo
 select *
 from part
 where p_name IN (select p_name from part p where p.p_size = part.p_size AND part.p_size + 121150 = p.p_partkey)
 PREHOOK: type: QUERY
 PREHOOK: Input: default@part
 #### A masked pattern was here ####
 POSTHOOK: query: explain cbo
 select *
 from part
 where p_name IN (select p_name from part p where p.p_size = part.p_size AND part.p_size + 121150 = p.p_partkey)
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@part
 #### A masked pattern was here ####
 CBO PLAN:
 HiveProject(p_partkey=[$0], p_name=[$1], p_mfgr=[$2], p_brand=[$3], p_type=[$4], p_size=[$5], p_container=[$6], p_retailprice=[$7], p_comment=[$8])
   HiveSemiJoin(condition=[AND(=($1, $10), =($11, $5), =($9, $12))], joinType=[semi])
     HiveProject(p_partkey=[$0], p_name=[$1], p_mfgr=[$2], p_brand=[$3], p_type=[$4], p_size=[$5], p_container=[$6], p_retailprice=[$7], p_comment=[$8], $f15=[+($5, 121150)])
       HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($5))])
         HiveTableScan(table=[[default, part]], table:alias=[part])
     HiveProject(p_name=[$1], p_size=[$5], p_partkey=[$0])
       HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($0), IS NOT NULL($1))])
         HiveTableScan(table=[[default, part]], table:alias=[p])

 PREHOOK: query: explain cbo
 select *
 from src b
 where b.key in (select key from src a where b.value > a.value and b.key < a.key)
 PREHOOK: type: QUERY
 PREHOOK: Input: default@src
 #### A masked pattern was here ####
 POSTHOOK: query: explain cbo
 select *
 from src b
 where b.key in (select key from src a where b.value > a.value and b.key < a.key)
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@src
 #### A masked pattern was here ####
 CBO PLAN:
 HiveSemiJoin(condition=[AND(=($2, $0), >($1, $3))], joinType=[semi])
   HiveProject(key=[$0], value=[$1])
     HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
       HiveTableScan(table=[[default, src]], table:alias=[b])
   HiveProject(key=[$0], value=[$1])
     HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
       HiveTableScan(table=[[default, src]], table:alias=[a])

 PREHOOK: query: explain cbo select p_mfgr, p_name, p_size
 from part b where b.p_size in
                   (select min(p_size)
                    from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
                    where r <= 2 and b.p_mfgr = a.p_mfgr
                   )
 PREHOOK: type: QUERY
 PREHOOK: Input: default@part
 #### A masked pattern was here ####
 POSTHOOK: query: explain cbo select p_mfgr, p_name, p_size
 from part b where b.p_size in
                   (select min(p_size)
                    from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
                    where r <= 2 and b.p_mfgr = a.p_mfgr
                   )
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@part
 #### A masked pattern was here ####
 CBO PLAN:
 HiveProject(p_mfgr=[$1], p_name=[$0], p_size=[$2])
   HiveSemiJoin(condition=[AND(=($1, $4), =($2, $3))], joinType=[semi])
     HiveProject(p_name=[$1], p_mfgr=[$2], p_size=[$5])
       HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($5))])
         HiveTableScan(table=[[default, part]], table:alias=[b])
     HiveProject(_o__c0=[$1], p_mfgr=[$0])
       HiveFilter(condition=[IS NOT NULL($1)])
         HiveAggregate(group=[{0}], agg#0=[min($1)])
           HiveFilter(condition=[<=($2, 2)])
             HiveProject(p_mfgr=[$2], p_size=[$5], rank_window_0=[rank() OVER (PARTITION BY $2 ORDER BY $5 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
               HiveFilter(condition=[IS NOT NULL($2)])
                 HiveTableScan(table=[[default, part]], table:alias=[part])

 PREHOOK: query: select p_mfgr, p_name, p_size
 from part b where b.p_size in
                   (select min(p_size)
                    from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
                    where r <= 2 and b.p_mfgr = a.p_mfgr
                   )
 PREHOOK: type: QUERY
 PREHOOK: Input: default@part
 #### A masked pattern was here ####
 POSTHOOK: query: select p_mfgr, p_name, p_size
 from part b where b.p_size in
                   (select min(p_size)
                    from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
                    where r <= 2 and b.p_mfgr = a.p_mfgr
                   )
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@part
 #### A masked pattern was here ####
 Manufacturer#1	almond antique burnished rose metallic	2
 Manufacturer#1	almond antique burnished rose metallic	2
 Manufacturer#2	almond aquamarine midnight light salmon	2
 Manufacturer#3	almond antique misty red olive	1
 Manufacturer#4	almond aquamarine yellow dodger mint	7
 Manufacturer#5	almond antique sky peru orange	2
 PREHOOK: query: explain cbo select *
             from src b
             where b.key in
                   (select distinct a.key
                    from src a
                    where b.value <> a.key and a.key > '9'
                   )
 PREHOOK: type: QUERY
 PREHOOK: Input: default@src
 #### A masked pattern was here ####
 POSTHOOK: query: explain cbo select *
             from src b
             where b.key in
                   (select distinct a.key
                    from src a
                    where b.value <> a.key and a.key > '9'
                   )
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@src
 #### A masked pattern was here ####
 CBO PLAN:
 HiveSemiJoin(condition=[AND(=($0, $2), <>($1, $3))], joinType=[semi])
   HiveProject(key=[$0], value=[$1])
     HiveFilter(condition=[IS NOT NULL($0)])
       HiveTableScan(table=[[default, src]], table:alias=[b])
   HiveProject(key=[$0], key0=[$0])
     HiveAggregate(group=[{0}])
       HiveFilter(condition=[>($0, _UTF-16LE'9')])
         HiveTableScan(table=[[default, src]], table:alias=[a])

 PREHOOK: query: select *
 from src b
 where b.key in
       (select distinct a.key
        from src a
        where b.value <> a.key and a.key > '9'
       )
 PREHOOK: type: QUERY
 PREHOOK: Input: default@src
 #### A masked pattern was here ####
 POSTHOOK: query: select *
 from src b
 where b.key in
       (select distinct a.key
        from src a
        where b.value <> a.key and a.key > '9'
       )
 POSTHOOK: type: QUERY
 POSTHOOK: Input: default@src
 #### A masked pattern was here ####
 90	val_90
 90	val_90
 90	val_90
 92	val_92
 95	val_95
 95	val_95
 96	val_96
 97	val_97
 97	val_97
 98	val_98
 98	val_98
	PREHOOK: query: explain cbo
	select *
	from src b
	where b.key in (select a.key from src a where b.value = a.value and a.key > '9')
	PREHOOK: type: QUERY
	PREHOOK: Input: default@src
	#### A masked pattern was here ####
	POSTHOOK: query: explain cbo
	select *
	from src b
	where b.key in (select a.key from src a where b.value = a.value and a.key > '9')
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@src
	#### A masked pattern was here ####
	CBO PLAN:
	HiveSemiJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
	HiveProject(key=[$0], value=[$1])
	HiveFilter(condition=[AND(>($0, _UTF-16LE'9'), IS NOT NULL($1))])
	HiveTableScan(table=[[default, src]], table:alias=[b])
	HiveProject(key=[$0], value=[$1])
	HiveFilter(condition=[AND(>($0, _UTF-16LE'9'), IS NOT NULL($1))])
	HiveTableScan(table=[[default, src]], table:alias=[a])

	PREHOOK: query: explain cbo
	select count(*) as c
	from part as e
	where p_size + 100 IN (select p_partkey from part where p_name = e.p_name)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@part
	#### A masked pattern was here ####
	POSTHOOK: query: explain cbo
	select count(*) as c
	from part as e
	where p_size + 100 IN (select p_partkey from part where p_name = e.p_name)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@part
	#### A masked pattern was here ####
	CBO PLAN:
	HiveAggregate(group=[{}], agg#0=[count()])
	HiveSemiJoin(condition=[AND(=($3, $0), =($1, $2))], joinType=[semi])
	HiveProject(p_name=[$1], $f13=[+($5, 100)])
	HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($5))])
	HiveTableScan(table=[[default, part]], table:alias=[e])
	HiveProject(p_partkey=[$0], p_name=[$1])
	HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
	HiveTableScan(table=[[default, part]], table:alias=[part])

	PREHOOK: query: explain cbo
	select *
	from part
	where p_name IN (select p_name from part p where p.p_size = part.p_size AND part.p_size + 121150 = p.p_partkey)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@part
	#### A masked pattern was here ####
	POSTHOOK: query: explain cbo
	select *
	from part
	where p_name IN (select p_name from part p where p.p_size = part.p_size AND part.p_size + 121150 = p.p_partkey)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@part
	#### A masked pattern was here ####
	CBO PLAN:
	HiveProject(p_partkey=[$0], p_name=[$1], p_mfgr=[$2], p_brand=[$3], p_type=[$4], p_size=[$5], p_container=[$6], p_retailprice=[$7], p_comment=[$8])
	HiveSemiJoin(condition=[AND(=($1, $10), =($11, $5), =($9, $12))], joinType=[semi])
	HiveProject(p_partkey=[$0], p_name=[$1], p_mfgr=[$2], p_brand=[$3], p_type=[$4], p_size=[$5], p_container=[$6], p_retailprice=[$7], p_comment=[$8], $f15=[+($5, 121150)])
	HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($5))])
	HiveTableScan(table=[[default, part]], table:alias=[part])
	HiveProject(p_name=[$1], p_size=[$5], p_partkey=[$0])
	HiveFilter(condition=[AND(IS NOT NULL($5), IS NOT NULL($0), IS NOT NULL($1))])
	HiveTableScan(table=[[default, part]], table:alias=[p])

	PREHOOK: query: explain cbo
	select *
	from src b
	where b.key in (select key from src a where b.value > a.value and b.key < a.key)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@src
	#### A masked pattern was here ####
	POSTHOOK: query: explain cbo
	select *
	from src b
	where b.key in (select key from src a where b.value > a.value and b.key < a.key)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@src
	#### A masked pattern was here ####
	CBO PLAN:
	HiveSemiJoin(condition=[AND(=($2, $0), >($1, $3))], joinType=[semi])
	HiveProject(key=[$0], value=[$1])
	HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
	HiveTableScan(table=[[default, src]], table:alias=[b])
	HiveProject(key=[$0], value=[$1])
	HiveFilter(condition=[AND(IS NOT NULL($1), IS NOT NULL($0))])
	HiveTableScan(table=[[default, src]], table:alias=[a])

	PREHOOK: query: explain cbo select p_mfgr, p_name, p_size
	from part b where b.p_size in
	(select min(p_size)
	from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
	where r <= 2 and b.p_mfgr = a.p_mfgr
	)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@part
	#### A masked pattern was here ####
	POSTHOOK: query: explain cbo select p_mfgr, p_name, p_size
	from part b where b.p_size in
	(select min(p_size)
	from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
	where r <= 2 and b.p_mfgr = a.p_mfgr
	)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@part
	#### A masked pattern was here ####
	CBO PLAN:
	HiveProject(p_mfgr=[$1], p_name=[$0], p_size=[$2])
	HiveSemiJoin(condition=[AND(=($1, $4), =($2, $3))], joinType=[semi])
	HiveProject(p_name=[$1], p_mfgr=[$2], p_size=[$5])
	HiveFilter(condition=[AND(IS NOT NULL($2), IS NOT NULL($5))])
	HiveTableScan(table=[[default, part]], table:alias=[b])
	HiveProject(_o__c0=[$1], p_mfgr=[$0])
	HiveFilter(condition=[IS NOT NULL($1)])
	HiveAggregate(group=[{0}], agg#0=[min($1)])
	HiveFilter(condition=[<=($2, 2)])
	HiveProject(p_mfgr=[$2], p_size=[$5], rank_window_0=[rank() OVER (PARTITION BY $2 ORDER BY $5 NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
	HiveFilter(condition=[IS NOT NULL($2)])
	HiveTableScan(table=[[default, part]], table:alias=[part])

	PREHOOK: query: select p_mfgr, p_name, p_size
	from part b where b.p_size in
	(select min(p_size)
	from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
	where r <= 2 and b.p_mfgr = a.p_mfgr
	)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@part
	#### A masked pattern was here ####
	POSTHOOK: query: select p_mfgr, p_name, p_size
	from part b where b.p_size in
	(select min(p_size)
	from (select p_mfgr, p_size, rank() over(partition by p_mfgr order by p_size) as r from part) a
	where r <= 2 and b.p_mfgr = a.p_mfgr
	)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@part
	#### A masked pattern was here ####
	Manufacturer#1 almond antique burnished rose metallic 2
	Manufacturer#1 almond antique burnished rose metallic 2
	Manufacturer#2 almond aquamarine midnight light salmon 2
	Manufacturer#3 almond antique misty red olive 1
	Manufacturer#4 almond aquamarine yellow dodger mint 7
	Manufacturer#5 almond antique sky peru orange 2
	PREHOOK: query: explain cbo select *
	from src b
	where b.key in
	(select distinct a.key
	from src a
	where b.value <> a.key and a.key > '9'
	)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@src
	#### A masked pattern was here ####
	POSTHOOK: query: explain cbo select *
	from src b
	where b.key in
	(select distinct a.key
	from src a
	where b.value <> a.key and a.key > '9'
	)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@src
	#### A masked pattern was here ####
	CBO PLAN:
	HiveSemiJoin(condition=[AND(=($0, $2), <>($1, $3))], joinType=[semi])
	HiveProject(key=[$0], value=[$1])
	HiveFilter(condition=[IS NOT NULL($0)])
	HiveTableScan(table=[[default, src]], table:alias=[b])
	HiveProject(key=[$0], key0=[$0])
	HiveAggregate(group=[{0}])
	HiveFilter(condition=[>($0, _UTF-16LE'9')])
	HiveTableScan(table=[[default, src]], table:alias=[a])

	PREHOOK: query: select *
	from src b
	where b.key in
	(select distinct a.key
	from src a
	where b.value <> a.key and a.key > '9'
	)
	PREHOOK: type: QUERY
	PREHOOK: Input: default@src
	#### A masked pattern was here ####
	POSTHOOK: query: select *
	from src b
	where b.key in
	(select distinct a.key
	from src a
	where b.value <> a.key and a.key > '9'
	)
	POSTHOOK: type: QUERY
	POSTHOOK: Input: default@src
	#### A masked pattern was here ####
	90 val_90
	90 val_90
	90 val_90
	92 val_92
	95 val_95
	95 val_95
	96 val_96
	97 val_97
	97 val_97
	98 val_98
	98 val_98