Bug #640: ECPG: inserting float numbers

On Monday, 22 April 2002 18:41, you wrote:

Edward (edx@astercity.net) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
ECPG: inserting float numbers

Long Description
Inserting records with single precision real variables having small value
(range 1.0e-6 or less) frequently results in errors in ECPG translations
putting into resulted sql statement unexpected characters => see fragments
of sample code and ECPGdebug log where after value of rate variable the
unexpected character '^A' appears

Sample Code
/* fragment of the program code */
exec sql begin declare section;
/* ... */
float rate; /* level change rate */
/* ... */
exec sql end declare section;
/* ... */
sprintf(buf,"INSERT: %.8s.%.8s @%.32s -> %08x/%08x %f %f %f %f %f", loc,
name, ts, devstat, meastat, relative, level, ullage, rate, volume );
dbg_message( __FILE__, __LINE__, DBG_DBI, MSG_SQL, buf );
exec sql INSERT INTO rdr_meas ( name, loc, ts, devstat, meastat, relative,
level, ullage, levelrate, volume ) VALUES ( :name, :loc, 'now', :devstat,
:meastat, :relative, :level, :ullage, :rate, :volume ) ; /* ... */
---
The above produces in ECPG debug :
...
[2782]: ECPGexecute line 1042: QUERY: insert into rdr_meas ( name , loc ,
ts , devstat , meastat , relative , level , ullage , levelrate ,
volume ) values ( 'NR1 ' , 'Swedwood' , 'now' , 0 , 4096 ,
37.388961791992 , 0.71039032936096 , 1.1896096467972 , -5.5060195336409e-06
^A , 3.4871203899384 ) on connection radar [2782]: ECPGexecute line 1042:
Error: ERROR: parser: parse error at or near "^A" [2782]: raising sqlcode
-400 in line 1042, ''ERROR: parser: parse error at or near "^A"' in line
1042.'.

No file was uploaded with this report

----------
I am fighting with this bug on "PostgreSQL 7.2 on i686-pc-linux-gnu, compiled
by GCC 2.96". It appears also on 7.1.x, which was reported previously
(see buglist -> ecpg: unstable INSERT operation) on August 2001.
The temporary workaround I apllied here is "if" statement before INSERT:
if( fabs( rate ) < 1.0e-3 ) rate = 0.0;

Edward

Has this been addressed? Can you supply a reproducable example?

---------------------------------------------------------------------------

Edward Pilipczuk wrote:

On Monday, 22 April 2002 18:41, you wrote:

Edward (edx@astercity.net) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
ECPG: inserting float numbers

Long Description
Inserting records with single precision real variables having small value
(range 1.0e-6 or less) frequently results in errors in ECPG translations
putting into resulted sql statement unexpected characters => see fragments
of sample code and ECPGdebug log where after value of rate variable the
unexpected character '^A' appears

Sample Code
/* fragment of the program code */
exec sql begin declare section;
/* ... */
float rate; /* level change rate */
/* ... */
exec sql end declare section;
/* ... */
sprintf(buf,"INSERT: %.8s.%.8s @%.32s -> %08x/%08x %f %f %f %f %f", loc,
name, ts, devstat, meastat, relative, level, ullage, rate, volume );
dbg_message( __FILE__, __LINE__, DBG_DBI, MSG_SQL, buf );
exec sql INSERT INTO rdr_meas ( name, loc, ts, devstat, meastat, relative,
level, ullage, levelrate, volume ) VALUES ( :name, :loc, 'now', :devstat,
:meastat, :relative, :level, :ullage, :rate, :volume ) ; /* ... */
---
The above produces in ECPG debug :
...
[2782]: ECPGexecute line 1042: QUERY: insert into rdr_meas ( name , loc ,
ts , devstat , meastat , relative , level , ullage , levelrate ,
volume ) values ( 'NR1 ' , 'Swedwood' , 'now' , 0 , 4096 ,
37.388961791992 , 0.71039032936096 , 1.1896096467972 , -5.5060195336409e-06
^A , 3.4871203899384 ) on connection radar [2782]: ECPGexecute line 1042:
Error: ERROR: parser: parse error at or near "^A" [2782]: raising sqlcode
-400 in line 1042, ''ERROR: parser: parse error at or near "^A"' in line
1042.'.

No file was uploaded with this report

----------
I am fighting with this bug on "PostgreSQL 7.2 on i686-pc-linux-gnu, compiled
by GCC 2.96". It appears also on 7.1.x, which was reported previously
(see buglist -> ecpg: unstable INSERT operation) on August 2001.
The temporary workaround I apllied here is "if" statement before INSERT:
if( fabs( rate ) < 1.0e-3 ) rate = 0.0;

Edward

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce, the attached source reproduces this on 7.2, I don't have a
later version at hand to test if it's been fixed:

createdb floattest
echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest
ecpg insert-float.pgc
gcc insert-float.c -lecpg -lpq
./a.out floattest

results in:

col1: -0.000006
*!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21.

and in epcgdebug:

[29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near "a"' in line 21.'.
[29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near "a"' in line 21.'.
[29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near "a"' in line 21.'.

Regards, Lee Kindness.

Bruce Momjian writes:

OK, I have reproduced the problem on my machine:

#$ ./a.out floattest
col1: -0.000006
*!*!* Error -220: No such connection NULL in line 21.

Wow, how did that "A" get into the query string:

insert into tab1 ( col1 ) values ( -6.0000002122251e-06A )

Quite strange. Michael, any ideas?

---------------------------------------------------------------------------

Lee Kindness wrote:
Content-Description: message body text

Bruce, the attached source reproduces this on 7.2, I don't have a
later version at hand to test if it's been fixed:

createdb floattest
echo "CREATE TABLE tab1(col1 FLOAT);" | psql floattest
ecpg insert-float.pgc
gcc insert-float.c -lecpg -lpq
./a.out floattest

results in:

col1: -0.000006
*!*!* Error -400: 'ERROR: parser: parse error at or near "a"' in line 21.

and in epcgdebug:

[29189]: ECPGexecute line 21: QUERY: insert into tab1 ( col1 ) values ( -6.0000002122251e-06A ) on connection floattest
[29189]: ECPGexecute line 21: Error: ERROR: parser: parse error at or near "a"
[29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near "a"' in line 21.'.

Regards, Lee Kindness.

Bruce Momjian writes:

Has this been addressed? Can you supply a reproducable example?
Edward Pilipczuk wrote:

On Monday, 22 April 2002 18:41, you wrote:

Edward (edx@astercity.net) reports a bug with a severity of 1
ECPG: inserting float numbers
Inserting records with single precision real variables having small value
(range 1.0e-6 or less) frequently results in errors in ECPG translations
putting into resulted sql statement unexpected characters => see fragments
of sample code and ECPGdebug log where after value of rate variable the
unexpected character '^A' appears

Sample Code
[ snip ]

#include <stdlib.h>

EXEC SQL INCLUDE sqlca;

int main(int argc, char **argv)
{
EXEC SQL BEGIN DECLARE SECTION;
char *db = argv[1];
float col1;
EXEC SQL END DECLARE SECTION;
FILE *f;

if( (f = fopen("ecpgdebug", "w" )) != NULL )
ECPGdebug(1, f);

EXEC SQL CONNECT TO :db;
EXEC SQL BEGIN;

col1 = -6e-06;
printf("col1: %f\n", col1);
EXEC SQL INSERT INTO tab1(col1) VALUES (:col1);
if( sqlca.sqlcode < 0 )
{
fprintf(stdout, "*!*!* Error %ld: %s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
EXEC SQL ABORT;
EXEC SQL DISCONNECT;
return( 1 );
}
else
{
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return( 0 );
}
}

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce, after checking the libecpg source i'm fairly sure the problem
is due to the malloc buffer that the float is being sprintf'd into
being too small... It is always allocated 20 bytes but with a %.14g
printf specifier -6e-06 results in 20 characters:

-6.0000000000000e-06

and the NULL goes... bang! I guess the '-' wasn't factored in and 21
bytes would be enough. Patch against current CVS (but untested):

Index: src/interfaces/ecpg/lib/execute.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/lib/execute.c,v
retrieving revision 1.36
diff -r1.36 execute.c
703c703
< 				if (!(mallocedval = ECPGalloc(var->arrsize * 20, stmt->lineno)))
---

if (!(mallocedval = ECPGalloc(var->arrsize * 21, stmt->lineno)))

723c723
< if (!(mallocedval = ECPGalloc(var->arrsize * 20, stmt->lineno)))
---

if (!(mallocedval = ECPGalloc(var->arrsize * 21, stmt->lineno)))

Lee.

Bruce Momjian writes:

Bruce, this error and the one in your earlier post are not indicative
of the bug, but rather of the connection failing - looking at the
created ecpgdebug file should confirm this.

I have since compiled 7.3 with the patch locally and cannot recreate
the bug (after messing around with the HBA cfg file - I was getting
the same error as you).

My command line (with 7.3 sitting in /database/pgsql-test on port 5433
and LD_LIBRARY_PATH setup):

/database/pgsql-test/bin/ecpg insert-float.pgc
gcc insert-float.c -I/database/pgsql-test/include -L/database/pgsql-test/lib -lecpg -lpq
./a.out floattest@localhost:5433

Regards, Lee Kindness.

Bruce Momjian writes:

On Tue, Jun 11, 2002 at 06:58:54AM -0400, Bruce Momjian wrote:

Wow, how did that "A" get into the query string:

Buffer overrun. :-(

I just committed Lee's patch. Thanks a lot.

BTW. I didn't even know that this bug was reported against ecpg. Somehow
I missed it. I guess I need to rescan the bug archive.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes wrote:

On Tue, Jun 11, 2002 at 06:58:54AM -0400, Bruce Momjian wrote:

Wow, how did that "A" get into the query string:

Buffer overrun. :-(

I just committed Lee's patch. Thanks a lot.

BTW. I didn't even know that this bug was reported against ecpg. Somehow
I missed it. I guess I need to rescan the bug archive.

Thanks. No problem. It is impossible to catch all the bug reports.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026