Where condition doesn't work on the result of the subquery

[ghost]

Hi

I'm trying to filter a result of a subquery, but the where condition doesn't work on the result of the subquery. The last result should list sub_gourp=1 and sub_group=2 only, but it includes also sub_group=3.

create database temp

use temp

insert test,my_group=1,sub_group=1 val=1
insert test,my_group=1,sub_group=2 val=1
insert test,my_group=1,sub_group=3 val=0

select * from test
	name: test
	time                my_group sub_group val
	----                -------- --------- ---
	1484407176627242006 1        1         1
	1484407179866974593 1        2         1
	1484407185003410148 1        3         0

select * from (select * from test where my_group='1' group by sub_group order by desc limit 1) group by sub_group
	name: test
	tags: sub_group=3
	time                my_group val
	----                -------- ---
	1484407185003410148 1        0

	name: test
	tags: sub_group=2
	time                my_group val
	----                -------- ---
	1484407179866974593 1        1

	name: test
	tags: sub_group=1
	time                my_group val
	----                -------- ---
	1484407176627242006 1        1

select * from (select * from test where my_group='1' group by sub_group order by desc limit 1) where val > 0 group by sub_group
	name: test
	tags: sub_group=3
	time                my_group val
	----                -------- ---
	1484407185003410148 1        0

	name: test
	tags: sub_group=2
	time                my_group val
	----                -------- ---
	1484407179866974593 1        1

	name: test
	tags: sub_group=1
	time                my_group val
	----                -------- ---
	1484407176627242006 1        1

My first mistake was comparing val with '0' (#7646), but even comparing with 0 the result is the same. Does anyone have a suggestion

[jsternberg]

I need to investigate, but @jwilder should I add this to the 1.2 milestone?

[jsternberg]

I'm looking at this now. We don't propagate the condition of the outer query at all and it doesn't get applied anywhere. I'm looking at it now, but this is pretty difficult. @jwilder if I'm unable to resolve this, we may have to put a caveat down that conditions don't work correctly yet.

/cc @rkuchan