Thread: Re: What do I do with this error?
Folks, Never mind. It turns out that if you call try to feed a NULL to a regular expression (e.g. Client_name ~* NULL) you get: ERROR: regcomp failed with error empty (sub)expression Good to know. I had expected that regular expressions, like other operators, would return NULL if NULL was the pattern. Is this a bug? -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
> Never mind. It turns out that if you call try to feed a NULL to a
> regular expression (e.g. Client_name ~* NULL) you get:
> ERROR: regcomp failed with error empty (sub)expression
> Good to know. I had expected that regular expressions, like other
> operators, would return NULL if NULL was the pattern. Is this a bug?
Yes, I'd say so. 7.1 returns a NULL as expected ...
regards, tom lane
Tom, > Yes, I'd say so. 7.1 returns a NULL as expected ... Er, Tom, I'm using 7.1. Pgsql does return a null as expected when going from the command line. However, if I call the regular expression from within a PL/pgSQL function, it bombs. Therefore, please forward to pgsql-bugs: Bug Name: Regular Expressions with NULL error out in PL/pgSQL. Can Reproduce: Reliably Severity: Annoyance. Location: PL/pgSQL / Function handler / SPI Version: 7.1 beta 3 Description: If you attempt to run a query within a PL/pgSQL function which compares a field. via regular expression, to a NULL variable, you get a fatal error. Example: CREATE FUNCTION test1 () RETURNS INT4 AS 'DELCARE expression1 VARCHAR;return_value INT4; BEGINexpression1 := NULL;SELECT id INTO return_valueFROM table1WHERE field2 ~* expression1;RETURN return_value; END; -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Using the below (with a few fixes, delcare->declare and adding the close quote and language type as plpgsql, and a table1 defined as id int, field1 varchar, field2 varchar, I get a NULL returned if I select test1(); I'm somewhere between beta4 and 5 though. On Sun, 18 Mar 2001, Josh Berkus wrote: > Tom, > > > Yes, I'd say so. 7.1 returns a NULL as expected ... > > Er, Tom, I'm using 7.1. > > Pgsql does return a null as expected when going from the command line. > However, if I call the regular expression from within a PL/pgSQL > function, it bombs. > > Therefore, please forward to pgsql-bugs: > > Bug Name: Regular Expressions with NULL error out in PL/pgSQL. > Can Reproduce: Reliably > Severity: Annoyance. > Location: PL/pgSQL / Function handler / SPI > Version: 7.1 beta 3 > > Description: If you attempt to run a query within a PL/pgSQL function > which compares a field. via regular expression, to a NULL variable, you > get a fatal error. > > Example: > > CREATE FUNCTION test1 () RETURNS INT4 AS > 'DELCARE > expression1 VARCHAR; > return_value INT4; > BEGIN > expression1 := NULL; > SELECT id INTO return_value > FROM table1 > WHERE field2 ~* expression1; > RETURN return_value; > END;
"Josh Berkus" <josh@agliodbs.com> writes:
> Er, Tom, I'm using 7.1.
> Pgsql does return a null as expected when going from the command line.
> However, if I call the regular expression from within a PL/pgSQL
> function, it bombs.
Well, that's more interesting, but I still can't reproduce it ...
regression=# \df+ test1
List of functionsResult | Function | Arguments | Owner | Language |
Source |
Description
---------+----------+-----------+----------+----------+-------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------+------------
-integer | test1 | | postgres | plpgsql | declare
expression1 VARCHAR;
return_value INT4;
BEGIN
expression1 := NULL;
SELECT id INTO return_value
FROM table1
WHERE field2 ~* expression1;
RETURN return_value;
END;|
(1 row)
regression=# create table table1 (id int, field2 text);
CREATE
regression=# select test1();test1
-------
(1 row)
regression=# insert into table1 values(1,'zz');
INSERT 1273361 1
regression=# select test1();test1
-------
(1 row)
regression=# insert into table1 values(1,null);
INSERT 1273362 1
regression=# select test1();test1
-------
(1 row)
regards, tom lane
Tom, Stephan: I was hoping that the error would be reproduceable with a simplified example; however, it looks like I'll have to supply you folks with the full function an tabledefs. I hesitated to do so, because the function involves doing a pattern-matching search based on a complex view linked to an IN search on a subtable. It's no doubt this combination of factors that's making things blow up. BTW, I found an easy workaround for the problem; I'm pursuing this just as a beta issue for PgSQL 7.1. Can either of you supply me with syntax to pg_dump the full function & view decarations to a text file? I'm using pgaccess mostly for development, but it doesn' let me cut-and-paste. -Josh Berkus
"Josh Berkus" <josh@agliodbs.com> writes:
> Can either of you supply me with syntax to pg_dump the full function &
> view decarations to a text file?
If you're sure it doesn't require any data, you can do "pg_dump -s dbname"
to get just the database schema. I'd start with that and then trim away
unrelated stuff, if you feel the urge to trim.
regards, tom lane
"Josh Berkus" <josh@agliodbs.com> writes:
> I was hoping that the error would be reproduceable with a simplified
> example; however, it looks like I'll have to supply you folks with the
> full function an tabledefs. I hesitated to do so, because the function
> involves doing a pattern-matching search based on a complex view linked
> to an IN search on a subtable. It's no doubt this combination of
> factors that's making things blow up.
Possibly, or it could be dependent on platform or configuration factors.
What platform/configure options/locale/etc are you using?
regards, tom lane
BTW, are you certain that your failure case actually does involve a
NULL, and not an empty string?
regression=# select 'foo' ~* null;?column?
----------
(1 row)
regression=# select 'foo' ~* '';
ERROR: regcomp failed with error empty (sub)expression
This is correct according to the POSIX definition of regular expressions
("A (modern) RE is one or more non-empty branches ...").
regards, tom lane
Tom,
> regression=# select 'foo' ~* '';
> ERROR: regcomp failed with error empty (sub)expression
>
> This is correct according to the POSIX definition of regular
> expressions
> ("A (modern) RE is one or more non-empty branches ...").
That sounds like what I'm seeing! And now that you point it out, there
is a bug in the PHP that calls this function that passes an empty string
('') instead of a NULL. Thanks!
Any possibility that that error message could be made more informative?
(i.e. ERROR: regcomp failed due to empty string in regular expression)
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco