On 12/12/2013 04:36 PM, Tom Lane wrote:
> BTW, on further thought, I'm afraid this is a bigger can of worms than
> it appears. The remarks above presume that the subquery is simple enough
> to be pulled up, which is the case in this example. It might not be too
> hard to make that case work. But
I wrote:
> That's about what I thought: it's unique-ifying according to the original
> semijoin qual, without realizing that the pulled-up clause from the lower
> WHERE would need to be treated as part of the semijoin qual. This isn't
> a bug in the existing code, because the case can never arise,
Kevin Grittner writes:
> Further confirmation using the EXPLAIN patch with Antonin's v2
> patch against the table before any EXPLAIN or ANALYZE:
> Hash Join (cost=37.12..80.40 rows=442 width=12)
> Hash Cond: (((upper.f2)::double precision = lower.f3) AND (upper.f1 =
> lower.f2))
> -> Se
Tom Lane wrote:
> Hm, that means there's only one grouping column (and it's the second
> tlist entry of the child plan node). So that seems conclusive that
> the unique-ification is being done wrong.
Further confirmation using the EXPLAIN patch with Antonin's v2
patch against the table before a
Tom Lane writes:
>> Yeah, I found myself wishing for an EXPLAIN option that would show
>> that.
>
> It's not hard to do ... how about the attached?
+1
> I chose to print grouping keys for both Agg and Group nodes, and to
> show them unconditionally. There's some case maybe for only including
>
Antonin Houska writes:
> debug_print_plan output contains
> :grpColIdx 2
> in the AGG node.
Hm, that means there's only one grouping column (and it's the second
tlist entry of the child plan node). So that seems conclusive that
the unique-ification is being done wrong. It's not very clear why
t
On 12/11/2013 10:15 PM, Tom Lane wrote:
>
> FWIW, that plan isn't obviously wrong; if it is broken, most likely the
> reason is that the HashAggregate is incorrectly unique-ifying the lower
> table. (Unfortunately, EXPLAIN doesn't show enough about the HashAgg
> to know what it's doing exactly.)
Kevin Grittner writes:
> Tom Lane wrote:
>> FWIW, that plan isn't obviously wrong; if it is broken, most
>> likely the reason is that the HashAggregate is incorrectly
>> unique-ifying the lower table. (Unfortunately, EXPLAIN doesn't
>> show enough about the HashAgg to know what it's doing exactl
Tom Lane wrote:
> FWIW, that plan isn't obviously wrong; if it is broken, most
> likely the reason is that the HashAggregate is incorrectly
> unique-ifying the lower table. (Unfortunately, EXPLAIN doesn't
> show enough about the HashAgg to know what it's doing exactly.)
Yeah, I found myself wis
Kevin Grittner writes:
> Kevin Grittner wrote:
>> I applied it to master and ran the regression tests, and one of
>> the subselect tests failed.
>>
>> This query:
>>
>> SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second
>> Field"
>> FROM SUBSELECT_TBL upper
>> WHERE f1 IN
>> (
Kevin Grittner wrote:
> I applied it to master and ran the regression tests, and one of
> the subselect tests failed.
>
> This query:
>
> SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second
> Field"
> FROM SUBSELECT_TBL upper
> WHERE f1 IN
> (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(
Antonin Houska wrote:
> I used the DDLs attached (tables.ddl) for this query too, not
> only for the queries in quaries.sql. Yes, if I had mentioned it
> and/or qualified the 'k' column reference, it wouldn't have
> broken anything.
Apologies; I missed the attachments. It makes a lot more sense
On 12/06/2013 03:33 PM, Kevin Grittner wrote:
> Antonin Houska wrote:
>
>> SELECT *
>> FROMtab1 a
>> LEFT JOIN
>> tab2 b
>> ON a.i = ANY (
>> SELECT k
>> FROMtab3 c
>> WHEREk = a.i);
>
> This query works with k in any or all tables, but the
Kevin Grittner wrote:
> test=# SELECT *
> FROM tab1 a
> LEFT JOIN
> tab2 b
> ON a.i = ANY (
> SELECT k
> FROM tab3 c
> WHERE k = a.i);
> i | j
> ---+---
> 1 | 4
> 1 | 5
> 1 | 6
> 2 |
> 3 | 4
> 3 | 5
> 3 | 6
> (7 rows)
>
>> SELECT *
>>
Antonin Houska wrote:
> SELECT *
> FROM tab1 a
> LEFT JOIN
> tab2 b
> ON a.i = ANY (
> SELECT k
> FROM tab3 c
> WHERE k = a.i);
This query works with k in any or all tables, but the semantics
certainly vary depending on where k happens to be. It wou
On 10/31/2013 09:37 PM, Antonin Houska wrote:
> On 10/31/2013 03:46 PM, Antonin Houska wrote:
> I'm not sure if it's legal for the WHERE clause to reference LHS of the
> original outer join (a.j). Some more restriction may be needed. I need
> to think about it a bit more.
For a subquery or sublink
On 10/31/2013 03:46 PM, Antonin Houska wrote:
> Can the change be as simple as this or do I neglect anything?
Well, the example of outer join is wrong. Instead I think query
SELECT *
FROMtab1 a
LEFT JOIN
tab1 b
ON b.i = ANY (
SELECT tab2.k
So far, a suquery of ANY sublink located in WHERE/ON clause can't
reference vars exactly one level up, as long as pull-up into the join
tree is expected. Now that we have LATERAL subqueries (there seem to be
no specifics of SEMI JOIN when it comes to parameterization etc), I
think this restriction
18 matches
Mail list logo
