From ae3bb32ede6e7569b935a92ed5bc7fc1cb0564cf Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 26 May 2016 17:51:49 -0400 Subject: [PATCH] LP#1549505: Query literal interpolation casts incorrectly We need to be sure that all query values are of the same type in the same columns because PLPGSQL functions are compiled and cached. In this case, the core query of the in-db search cannot have its shape change. This commit assures that browse and search uses of the SP provide core queries that match on their SELECT lists. Of particular importance is the type of the "rel" output column, which was variously float8 or numeric, depending on whether the search contained any terms (a "search") or not (a browse link). Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier --- .../OpenILS/Application/Storage/Driver/Pg/QueryParser.pm | 8 ++++---- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 8 ++++---- Open-ILS/src/sql/Pg/upgrade/YYYY.function.qp_search.sql | 8 ++++---- 3 files changed, 12 insertions(+), 12 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 9265a8891e..4be8e93330 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 @@ -944,10 +944,10 @@ sub toSQL { # have an overage badge score of zero. my $adjusted_scale = ( $max_mult - 1.0 ) / 5.0; - $rank = "1.0/(( $rel ) * (1.0 + (AVG(COALESCE(pop_with.total_score::NUMERIC,0.0)) * $adjusted_scale)))::NUMERIC"; + $rank = "1.0/(( $rel ) * (1.0 + (AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC)) * ${adjusted_scale}::NUMERIC)))::NUMERIC"; } } elsif ($sort_filter =~ /^pop/) { - $rank = '1.0/(AVG(COALESCE(pop_with.total_score::NUMERIC,0.0)) + 5.0)::NUMERIC'; + $rank = '1.0/(AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC)) + 5.0::NUMERIC)::NUMERIC'; my $pop_desc = $desc eq 'ASC' ? 'DESC' : 'ASC'; $pop_extra_sort = "3 $pop_desc $nullpos,"; } else { @@ -980,11 +980,11 @@ sub toSQL { $with SELECT $key AS id, $agg_records, - $rel AS rel, + ${rel}::NUMERIC AS rel, $rank AS rank, FIRST(pubdate_t.value) AS tie_break, STRING_AGG(ARRAY_TO_STRING(pop_with.badges,','),',') AS badges, - AVG(COALESCE(pop_with.total_score::NUMERIC,0.0))::NUMERIC(2,1) AS popularity + AVG(COALESCE(pop_with.total_score::NUMERIC,0.0::NUMERIC))::NUMERIC(2,1) AS popularity FROM metabib.metarecord_source_map m $$flat_plan{from} $mra_join diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 40f0dfb308..0d3fb006fb 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -2192,8 +2192,8 @@ BEGIN superpage_of_records := all_brecords[slice_start:slice_end]; qpfts_query := 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || - 'NULL AS badges, NULL AS popularity, ' || - '1::INT AS rel FROM (SELECT UNNEST(' || + 'NULL AS badges, NULL::NUMERIC AS popularity, ' || + '1::NUMERIC AS rel FROM (SELECT UNNEST(' || quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; -- We use search.query_parser_fts() for visibility testing. @@ -2231,8 +2231,8 @@ BEGIN superpage_of_records := all_arecords[slice_start:slice_end]; qpfts_query := 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || - 'NULL AS badges, NULL AS popularity, ' || - '1::INT AS rel FROM (SELECT UNNEST(' || + 'NULL AS badges, NULL::NUMERIC AS popularity, ' || + '1::NUMERIC AS rel FROM (SELECT UNNEST(' || quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; -- We use search.query_parser_fts() for visibility testing. diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.function.qp_search.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.function.qp_search.sql index a4ea53d111..3cb1da6a52 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.function.qp_search.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.function.qp_search.sql @@ -484,8 +484,8 @@ BEGIN superpage_of_records := all_brecords[slice_start:slice_end]; qpfts_query := 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || - 'NULL AS badges, NULL AS popularity, ' || - '1::INT AS rel FROM (SELECT UNNEST(' || + 'NULL AS badges, NULL::NUMERIC AS popularity, ' || + '1::NUMERIC AS rel FROM (SELECT UNNEST(' || quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; -- We use search.query_parser_fts() for visibility testing. @@ -523,8 +523,8 @@ BEGIN superpage_of_records := all_arecords[slice_start:slice_end]; qpfts_query := 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || - 'NULL AS badges, NULL AS popularity, ' || - '1::INT AS rel FROM (SELECT UNNEST(' || + 'NULL AS badges, NULL::NUMERIC AS popularity, ' || + '1::NUMERIC AS rel FROM (SELECT UNNEST(' || quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; -- We use search.query_parser_fts() for visibility testing. -- 2.43.2