Commitabb9c63

committed

Unbreak index optimization for LIKE on bytea

The same code is used to handle both text and bytea, but bytea is notcollation-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

1 parentc34677f commitabb9c63Copy full SHA for abb9c63

File tree

+32

-1

lines changed

+32

-1

lines changed

Lines changed: 3 additions & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -267,8 +267,10 @@ match_pattern_prefix(Node *leftop,`
`267`	`267`	`* precise error messages.) (It should be possible to support at least`
`268`	`268`	`* Pattern_Prefix_Exact, but no point as along as the actual`
`269`	`269`	`* pattern-matching implementations don't support it.)`
	`270`	`+ *`
	`271`	`+ * expr_coll is not set for a non-collation-aware data type such as bytea.`
`270`	`272`	`*/`
`271`		`-if (!get_collation_isdeterministic(expr_coll))`
	`273`	`+if (expr_coll&&!get_collation_isdeterministic(expr_coll))`
`272`	`274`	`returnNIL;`
`273`	`275`
`274`	`276`	`/*`

Lines changed: 16 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -1101,6 +1101,22 @@ SELECT 'jack' LIKE '%____%' AS t;`
`1101`	`1101`	`t`
`1102`	`1102`	`(1 row)`
`1103`	`1103`
	`1104`	`+--`
	`1105`	`+-- basic tests of LIKE with indexes`
	`1106`	`+--`
	`1107`	`+CREATE TABLE texttest (a text PRIMARY KEY, b int);`
	`1108`	`+SELECT * FROM texttest WHERE a LIKE '%1%';`
	`1109`	`+ a \| b`
	`1110`	`+---+---`
	`1111`	`+(0 rows)`
	`1112`	`+`
	`1113`	`+CREATE TABLE byteatest (a bytea PRIMARY KEY, b int);`
	`1114`	`+SELECT * FROM byteatest WHERE a LIKE '%1%';`
	`1115`	`+ a \| b`
	`1116`	`+---+---`
	`1117`	`+(0 rows)`
	`1118`	`+`
	`1119`	`+DROP TABLE texttest, byteatest;`
`1104`	`1120`	`--`
`1105`	`1121`	`-- test implicit type conversion`
`1106`	`1122`	`--`

Lines changed: 13 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -323,6 +323,19 @@ SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%____' as f;`
`323`	`323`	`SELECT'jack'LIKE'%____%'AS t;`
`324`	`324`
`325`	`325`
	`326`	`+--`
	`327`	`+-- basic tests of LIKE with indexes`
	`328`	`+--`
	`329`	`+`
	`330`	`+CREATETABLEtexttest (atextPRIMARY KEY, bint);`
	`331`	`+SELECT*FROM texttestWHERE aLIKE'%1%';`
	`332`	`+`
	`333`	`+CREATETABLEbyteatest (abyteaPRIMARY KEY, bint);`
	`334`	`+SELECT*FROM byteatestWHERE aLIKE'%1%';`
	`335`	`+`
	`336`	`+DROPTABLE texttest, byteatest;`
	`337`	`+`
	`338`	`+`
`326`	`339`	`--`
`327`	`340`	`-- test implicit type conversion`
`328`	`341`	`--`

Comments

(0)