Re: Bug #640: ECPG: inserting float numbers

Edward (edx(at)astercity(dot)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

On Monday, 22 April 2002 18:41, you wrote:
> Edward (edx(at)astercity(dot)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(at)astercity(dot)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(at)candle(dot)pha(dot)pa(dot)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]: 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(at)astercity(dot)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 ]

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(at)astercity(dot)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(at)candle(dot)pha(dot)pa(dot)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:
>
> 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.

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(at)Fam-Meskes(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026