Обсуждение: Bug #640: ECPG: inserting float numbers
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 errorsin ECPG translations putting into resulted sql statement unexpected characters => see fragments of sample code andECPGdebug 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@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]: 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 );
}
}
OK, I have reproduced the problem on my machine:#$ ./a.out floattestcol1: -0.000006*!*!* Error -220: No such
connectionNULL 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,
Pennsylvania19026
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 Kindnesswrote:> Content-Description: message body text> > > Bruce, the attached source reproduces this on 7.2, I don't havea> > 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> > > > resultsin:> > > > 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 ornear "a"> > [29189]: raising sqlcode -400 in line 21, ''ERROR: parser: parse error at or near "a"' in line 21.'.> > >> Regards, Lee Kindness.