From 08297491c797391c199475a9e47b00550f4804fb Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 18 May 2012 16:42:19 -0400 Subject: [PATCH] QP support for nested WITH construction Nested query plans build their own tsquery chunks which have to be individually JOINed to the final query. To do this, each tsquery SELECT gets its own entry in the top-level WITH, complete with alias for joining. Signed-off-by: Bill Erickson Signed-off-by: Mike Rylander --- .../OpenILS/Application/Storage/Driver/Pg/QueryParser.pm | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index a405c1fbef..f833a6bd86 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -656,6 +656,7 @@ sub toSQL { $flat_where = "AND $flat_where"; } my $with = $$flat_plan{with}; + $with= "\n\t\t\tWITH $with" if $with; # Need an array for query parser db function; this gives a better plan # than the ARRAY_AGG(DISTINCT m.source) option as of PostgreSQL 9.1 @@ -741,12 +742,13 @@ sub flatten { my $node_rank = 'COALESCE(' . $node->rank . " * ${talias}.weight, 0.0)"; my $core_limit = $self->QueryParser->core_limit || 25000; - $from .= "\n\tLEFT JOIN (\n\t\tSELECT fe.*, fe_weight.weight, xq.tsq /* search */\n\t\t FROM $table AS fe"; + $from .= "\n\tLEFT JOIN (\n\t\tSELECT fe.*, fe_weight.weight, ${talias}_xq.tsq /* search */\n\t\t FROM $table AS fe"; $from .= "\n\t\t\tJOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)"; if ($node->dummy_count < @{$node->only_atoms} ) { - $with.= "\n\t\t\tWITH xq AS (SELECT ". $node->tsquery ." AS tsq )"; - $from .= "\n\t\t\tJOIN xq ON (fe.index_vector @@ xq.tsq)"; + #$with.= "\n\t\t\tWITH ${talias}_xq AS (SELECT ". $node->tsquery ." AS tsq )"; + $with.= "${talias}_xq AS (SELECT ". $node->tsquery ." AS tsq )"; + $from .= "\n\t\t\tJOIN ${talias}_xq ON (fe.index_vector @@ ${talias}_xq.tsq)"; } else { $from .= "\n\t\t\t, (SELECT NULL::tsquery AS tsq ) AS x"; } @@ -814,6 +816,11 @@ sub flatten { push(@rank_list, @{$$subnode{rank_list}}); $from .= $$subnode{from}; $where .= "($$subnode{where})"; + + if ($$subnode{with}) { + $with .= ', ' if $with; + $with .= " " . $$subnode{with}; + } } } else { $where .= ' AND ' if ($node eq '&'); -- 2.11.0