postgrespro.git / summary / log / commit / refs

commit    abb9c63b2c00109183cdb9f588d2f23fc383ed38
Author:   Peter Eisentraut <peter@eisentraut.org>
Date:     Mon Apr 15 07:26:23 2019 +0000

    Unbreak index optimization for LIKE on bytea
    
    The same code is used to handle both text and bytea, but bytea is not
    collation-aware, so we shouldn't call get_collation_isdeterministic()
    in that case, since that will error out with an invalid collation.
    
    Reported-by: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
    Discussion: https://www.postgresql.org/message-id/flat/CAM2%2B6%3DWaf3qJ1%3DyVTUH8_yG-SC0xcBMY%2BSFLhvKKNnWNXSUDBw%40mail.gmail.com


 src/backend/utils/adt/like_support.c  |  4 +++-
 src/test/regress/expected/strings.out | 16 ++++++++++++++++
 src/test/regress/sql/strings.sql      | 13 +++++++++++++
 3 files changed, 32 insertions(+), 1 deletion(-)

diff --git a/src/backend/utils/adt/like_support.c b/src/backend/utils/adt/like_support.c
index a65e63736c..7528c80f7c 100644
--- a/src/backend/utils/adt/like_support.c
+++ b/src/backend/utils/adt/like_support.c
@@ -267,8 +267,10 @@ match_pattern_prefix(Node *leftop,
 	 * precise error messages.)  (It should be possible to support at least
 	 * Pattern_Prefix_Exact, but no point as along as the actual
 	 * pattern-matching implementations don't support it.)
+	 *
+	 * expr_coll is not set for a non-collation-aware data type such as bytea.
 	 */
-	if (!get_collation_isdeterministic(expr_coll))
+	if (expr_coll && !get_collation_isdeterministic(expr_coll))
 		return NIL;
 
 	/*
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 2f5f58273a..7c7f8726fc 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1101,6 +1101,22 @@ SELECT 'jack' LIKE '%____%' AS t;
  t
 (1 row)
 
+--
+-- basic tests of LIKE with indexes
+--
+CREATE TABLE texttest (a text PRIMARY KEY, b int);
+SELECT * FROM texttest WHERE a LIKE '%1%';
+ a | b 
+---+---
+(0 rows)
+
+CREATE TABLE byteatest (a bytea PRIMARY KEY, b int);
+SELECT * FROM byteatest WHERE a LIKE '%1%';
+ a | b 
+---+---
+(0 rows)
+
+DROP TABLE texttest, byteatest;
 --
 -- test implicit type conversion
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 1f4cd88a18..9cd2bc5d7e 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -323,6 +323,19 @@ SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f;
 SELECT 'jack' LIKE '%____%' AS t;
 
 
+--
+-- basic tests of LIKE with indexes
+--
+
+CREATE TABLE texttest (a text PRIMARY KEY, b int);
+SELECT * FROM texttest WHERE a LIKE '%1%';
+
+CREATE TABLE byteatest (a bytea PRIMARY KEY, b int);
+SELECT * FROM byteatest WHERE a LIKE '%1%';
+
+DROP TABLE texttest, byteatest;
+
+
 --
 -- test implicit type conversion
 --


[parent: c34677fdaa73]