pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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