pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: What do I do with this error?
12+ messages / 3 participants
[nested] [flat]
* Re: What do I do with this error?
@ 2001-03-19 01:39 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2001-03-19 01:39 UTC (permalink / raw)
To: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 03:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2001-03-19 03:10 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 04:09 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 12+ messages in thread
From: Josh Berkus @ 2001-03-19 04:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 04:21 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 12+ messages in thread
From: Stephan Szabo @ 2001-03-19 04:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 04:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2001-03-19 04:22 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"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 functions
Result | 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 16:05 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Josh Berkus @ 2001-03-19 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 16:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-03-19 16:23 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 17:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-03-19 17:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 17:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2001-03-19 17:46 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 19:04 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2001-03-19 19:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2001-03-19 19:08 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> 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)
You're right, this isn't a very transparent message. Perhaps
ERROR: Invalid regular expression (empty expression or subexpression)
Comments, better wordings?
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: What do I do with this error?
@ 2001-03-19 19:15 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Josh Berkus @ 2001-03-19 19:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom,
> You're right, this isn't a very transparent message. Perhaps
>
> ERROR: Invalid regular expression (empty expression or
> subexpression)
>
> Comments, better wordings?
Nope. That's great.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2001-03-19 19:15 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-19 01:39 Re: What do I do with this error? Josh Berkus <josh@agliodbs.com>
2001-03-19 03:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-19 04:09 ` Josh Berkus <josh@agliodbs.com>
2001-03-19 04:21 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-19 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-19 16:05 ` Josh Berkus <josh@agliodbs.com>
2001-03-19 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-19 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-19 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-19 19:04 ` Josh Berkus <josh@agliodbs.com>
2001-03-19 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-19 19:15 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox