Обсуждение: EMBEDDED BUG?!?!?!?

I am running:

[postgres@polaris pgsql]$ postmaster -V
postmaster (PostgreSQL) 7.1.3
[postgres@polaris pgsql]$

I am using a table with 609398 records made of varchar's, int's, and float's.
I am doing:

pma=> \d stck_dta_tbl_oprtnl
Table    = stck_dta_tbl_oprtnl
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| stck_sym                         | varchar()                        |     7 |
| dta_date                         | date                             |     4 |
| dta_date_num                     | int4                             |     4 |
| dta_date_dys                     | int4                             |     4 |
| opn                              | float8                           |     8 |
| hi                               | float8                           |     8 |
| lw                               | float8                           |     8 |
| cls                              | float8                           |     8 |
| vol                              | int4                             |     4 |
| unk                              | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
Index:    i_stk_date

  EXEC SQL BEGIN DECLARE SECTION;

    char  i_symbol[6];
    int   i_data_date;
    float i_open;
    float i_high;
    float i_low;
    float i_close;
    int   i_volume;
-
-
-
  EXEC SQL DECLARE stck_info_crsr CURSOR
    FOR SELECT stck_sym, dta_date_num, opn, hi, lw, cls, vol
    FROM stck_dta_tbl_oprtnl;

  EXEC SQL OPEN stck_info_crsr;
-
-
-

EXEC SQL FETCH stck_info_crsr INTO :i_symbol :i_data_date, :i_open, :i_high,
:i_low, :i_close, :i_volume;

when executing the fetch I get:

sqlcaid = SQLCA
sqlabc = 4.642502
sqlcode = 4.642506
sqlca.sqlerrm.sqlerrml = 49
sqlca.sqlerrm.sqlerrmc = Not correctly formatted int type: 29.25 line 500.
sqlerrp = NOT SET
sqlerrd = 4.642502
sqlext =
BEFORE ** A       6 20020102.000000 28.510000 29.340000 28.459999 0
SYMBOL ** 'A      '
D_DATE ** '0'
i_open ** '20020102.000000'
VOLUME ** '0'

The BEFORE, SYMBOL, D_DATE, i_open, VOLUME are from simple printf's.

!!!!! NOTE THE 6 AFTER THE 'A'!!!!!!!! and the fact that I declare
i_symbol to be char i_symbol[6];

NOW I CHANGE THE i_symbol[6] to i_symbol[5], recompile and rerun.

I get this:

sqlcaid = SQLCA
sqlabc = 4.642502
sqlcode = 4.642506
sqlca.sqlerrm.sqlerrml = 49
sqlca.sqlerrm.sqlerrmc = Not correctly formatted int type: 29.25 line 500.
sqlerrp = NOT SET
sqlerrd = 4.642502
sqlext =
BEFORE ** A     5 20020102.000000 28.510000 29.340000 28.459999 0
SYMBOL ** 'A    '
D_DATE ** '5'
i_open ** '20020102.000000'
VOLUME ** '0'

I tried changing the i_symbol[5] to char *i_symbol and got 'A     0';

Apparently postgres is misreading the declaration of i_symbol and
loading part of the declaration into the actual value of the data!!!!

The folks who do embedded stuff need to take a look at this!!!!!!

NOTE: I can read this record perfectly from within psql, so the data
itself is not corrupted.

pma=> fetch 1 in crsr;
stck_sym|  dta_date|dta_date_num|dta_date_dys|  opn|   hi|   lw|  cls|
vol|unk
--------+----------+------------+------------+-----+-----+-----+-----+-------+---
A       |2002-01-02|    20020102|      730852|28.51|29.34|28.46|29.25|2159300|
0
(1 row)


Thanks for your help!
Lynn

On Mon, Oct 27, 2003 at 06:25:46PM -0700, Lynn.Tilby@asu.edu wrote:
> [postgres@polaris pgsql]$ postmaster -V
> postmaster (PostgreSQL) 7.1.3

This is pretty old already.

> EXEC SQL FETCH stck_info_crsr INTO :i_symbol :i_data_date, :i_open, :i_high,
> :i_low, :i_close, :i_volume;

If this is taken literally from your program be aware that it cannot
work as there is no "," between ":i_symbol" and ":i_data_date" thus the
latter is considered an indicator.

> when executing the fetch I get:
>
> sqlcaid = SQLCA
> sqlabc = 4.642502
> sqlcode = 4.642506
> sqlca.sqlerrm.sqlerrml = 49
> sqlca.sqlerrm.sqlerrmc = Not correctly formatted int type: 29.25 line 500.

Okay, it seems you did use the line above and try to read a real value
into an int.

> BEFORE ** A       6 20020102.000000 28.510000 29.340000 28.459999 0
> SYMBOL ** 'A      '
> D_DATE ** '0'
> i_open ** '20020102.000000'
> VOLUME ** '0'
>
> The BEFORE, SYMBOL, D_DATE, i_open, VOLUME are from simple printf's.

This is utterly useless unless you provide the sources so we can see
what you actually printf.

> Apparently postgres is misreading the declaration of i_symbol and
> loading part of the declaration into the actual value of the data!!!!

Nope. The "6" comes beyond the limit of the variable. The string simply
seems to lack the delimitting \0.

> The folks who do embedded stuff need to take a look at this!!!!!!

They just did. They even used their spare time to create a test case
themselves as you did only provide code snippets.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!