Thread: BUG #13601: bit as quoted column in output

The following bug has been logged on the website:

Bug reference:      13601
Logged by:          Vicky Soni
Email address:      vicky.soni@quipment.nl
PostgreSQL version: 9.3.9
Operating system:   Windows
Description:

Hi,

    I am trying to return a result set using dynamic query from a postgresql
function.

Function runs ok but when returning bit data type in one of the columns from
output table, I get it as "bit" which should be bit only. Now executing same
query outside of the function in normal mode, it returns column with bit
data type only.

Please guide me.

Regards,
Vicky

On Tuesday, September 1, 2015, <vicky.soni@quipment.nl> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13601
> Logged by:          Vicky Soni
> Email address:      vicky.soni@quipment.nl <javascript:;>
> PostgreSQL version: 9.3.9
> Operating system:   Windows
> Description:
>
> Hi,
>
>     I am trying to return a result set using dynamic query from a
> postgresql
> function.
>
> Function runs ok but when returning bit data type in one of the columns
> from
> output table, I get it as "bit" which should be bit only. Now executing
> same
> query outside of the function in normal mode, it returns column with bit
> data type only.
>
> Please guide me.
>
>
Provide examples.

David J.

On Thu, Sep 3, 2015 at 12:21 AM, Vicky Soni - Quipment India
<vicky.soni@quipment.nl> wrote:
>                         Please refer following code and attached screensh=
ots.
>
> create or replace function bit_return_testing()
> returns table (OutBit bit)  as
> $BODY$
> declare SQL VARCHAR;
> Begin
> sql:=3D'select cast(1 as bit) ';
> raise notice '%',SQL;
> RETURN QUERY EXECUTE SQL;
> end;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100
>   ROWS 1000;
>
> select * from bit_return_testing();
>
> It give me output like this.
>
> [picture of PGAdmin showing a column's type as: "bit"]

In this case, Postgres is losing track of the typmod and giving your
client PQftype(...) =3D 1560 (the OID for bit) and PQfmod(...) =3D -1.
PGAdmin is then calling format_type(1560, -1) to find out how to
display that, which gives '"bit"' (bit with double quotes).

That's because format_type is designed to write out type names that
can survive round trips for use by pg_dump, and, somewhat suprisingly,
"bit" with double quotes means bit with typmod -1 whereas bit without
double quotes means bit with typmod 1 AKA bit(1).  Gory details here:

http://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/backe=
nd/utils/adt/format_type.c

> Now if I call same statement directly, i.e.
>
> select cast(1 as bit) ;
>
> It gives me output like this.
>
> [Picture of PGAdmin showing a column's type as: bit(1)]

In this case PQftype(...) =3D 1560 and PQfmod(...) =3D 1, which
format_type renders as 'bit(1)' (without double quotes).

> Now my question/doubt/concern was why did it throw =E2=80=9Cbit=E2=80=9D =
to me into first example and not simple bit?

Functions don't track the typmods of arguments or return values
(including the out arguments that are used to implement RETURNS
TABLE).  You can write them, but they're discarded, so your data type
bit AKA bit(1) was replaced with bit with typmod -1 AKA "bit".

--=20
Thomas Munro
http://www.enterprisedb.com

On Wed, Sep 23, 2015 at 6:35 PM, Vicky Soni  - Quipment India
<vicky.soni@quipment.nl> wrote:
> Hi Thomas,
>
>                           In this case, we are not able to retrieve correct datatype of returning function when
callingfrom Application. In our case it is DOTNET and we use NPGSQL. 
>
> Is this a bug? Can we expect fix for this or should we live with same?

I don't know anything about npgsql or C# sorry, but I assume that it
has the same information available to it.  I did notice from a very
quick look at that project's source[1] (if I am looking at the right
thing, which is not a given) that they treat bit(1) specially: they
use a C# bool for bit(1), but they use a BitArray class for any other
size of bit array.  At a guess, that might include values in a column
that contains bit strings of size 1 that was described as having type
bit with typmod -1, which means you'd get BitArray of size one
sometimes and bool other times, depending on whether Postgres happens
to provide typmod info.  Could that explain what you are seeing?

Otherwise, I would try raising an issue with a minimal reproduction
and description of the expected and actual behaviour over at the
npgsql project and see what they say.

[1] https://github.com/npgsql/npgsql/blob/develop/src/Npgsql/TypeHandlers/BitStringHandler.cs

--
Thomas Munro
http://www.enterprisedb.com

SGkgVGhvbWFzLA0KDQogICAgICAgICAgICAgICAgICAgICAgICAgIEluIHRoaXMgY2FzZSwgd2Ug
YXJlIG5vdCBhYmxlIHRvIHJldHJpZXZlIGNvcnJlY3QgZGF0YXR5cGUgb2YgcmV0dXJuaW5nIGZ1
bmN0aW9uIHdoZW4gY2FsbGluZyBmcm9tIEFwcGxpY2F0aW9uLiBJbiBvdXIgY2FzZSBpdCBpcyBE
T1RORVQgYW5kIHdlIHVzZSBOUEdTUUwuDQoNCklzIHRoaXMgYSBidWc/IENhbiB3ZSBleHBlY3Qg
Zml4IGZvciB0aGlzIG9yIHNob3VsZCB3ZSBsaXZlIHdpdGggc2FtZT8NCg0KICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgDQpQbGVhc2UgYWR2aWNlLg0KDQpUaGFua3MgJiBSZWdh
cmRzLA0KVmlja3kgU29uaQ0KRGF0YWJhc2UgQWRtaW5pc3RyYXRvcg0KDQoNCi0tLS0tT3JpZ2lu
YWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBUaG9tYXMgTXVucm8gW21haWx0bzp0aG9tYXMubXVucm9A
ZW50ZXJwcmlzZWRiLmNvbV0gDQpTZW50OiAyMyBTZXB0ZW1iZXIgMjAxNSAwODoyOA0KVG86IFZp
Y2t5IFNvbmkgLSBRdWlwbWVudCBJbmRpYSA8dmlja3kuc29uaUBxdWlwbWVudC5ubD4NCkNjOiBE
YXZpZCBHLiBKb2huc3RvbiA8ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb20+OyBwZ3NxbC1idWdz
QHBvc3RncmVzcWwub3JnDQpTdWJqZWN0OiBSZTogW0JVR1NdIEJVRyAjMTM2MDE6IGJpdCBhcyBx
dW90ZWQgY29sdW1uIGluIG91dHB1dA0KDQpPbiBUaHUsIFNlcCAzLCAyMDE1IGF0IDEyOjIxIEFN
LCBWaWNreSBTb25pIC0gUXVpcG1lbnQgSW5kaWEgPHZpY2t5LnNvbmlAcXVpcG1lbnQubmw+IHdy
b3RlOg0KPiAgICAgICAgICAgICAgICAgICAgICAgICBQbGVhc2UgcmVmZXIgZm9sbG93aW5nIGNv
ZGUgYW5kIGF0dGFjaGVkIHNjcmVlbnNob3RzLg0KPg0KPiBjcmVhdGUgb3IgcmVwbGFjZSBmdW5j
dGlvbiBiaXRfcmV0dXJuX3Rlc3RpbmcoKSByZXR1cm5zIHRhYmxlIChPdXRCaXQgDQo+IGJpdCkg
IGFzICRCT0RZJCBkZWNsYXJlIFNRTCBWQVJDSEFSOyBCZWdpbiBzcWw6PSdzZWxlY3QgY2FzdCgx
IGFzIGJpdCkgDQo+ICc7IHJhaXNlIG5vdGljZSAnJScsU1FMOyBSRVRVUk4gUVVFUlkgRVhFQ1VU
RSBTUUw7IGVuZDsgJEJPRFkkDQo+ICAgTEFOR1VBR0UgcGxwZ3NxbCBWT0xBVElMRQ0KPiAgIENP
U1QgMTAwDQo+ICAgUk9XUyAxMDAwOw0KPg0KPiBzZWxlY3QgKiBmcm9tIGJpdF9yZXR1cm5fdGVz
dGluZygpOw0KPg0KPiBJdCBnaXZlIG1lIG91dHB1dCBsaWtlIHRoaXMuDQo+DQo+IFtwaWN0dXJl
IG9mIFBHQWRtaW4gc2hvd2luZyBhIGNvbHVtbidzIHR5cGUgYXM6ICJiaXQiXQ0KDQpJbiB0aGlz
IGNhc2UsIFBvc3RncmVzIGlzIGxvc2luZyB0cmFjayBvZiB0aGUgdHlwbW9kIGFuZCBnaXZpbmcg
eW91ciBjbGllbnQgUFFmdHlwZSguLi4pID0gMTU2MCAodGhlIE9JRCBmb3IgYml0KSBhbmQgUFFm
bW9kKC4uLikgPSAtMS4NClBHQWRtaW4gaXMgdGhlbiBjYWxsaW5nIGZvcm1hdF90eXBlKDE1NjAs
IC0xKSB0byBmaW5kIG91dCBob3cgdG8gZGlzcGxheSB0aGF0LCB3aGljaCBnaXZlcyAnImJpdCIn
IChiaXQgd2l0aCBkb3VibGUgcXVvdGVzKS4NCg0KVGhhdCdzIGJlY2F1c2UgZm9ybWF0X3R5cGUg
aXMgZGVzaWduZWQgdG8gd3JpdGUgb3V0IHR5cGUgbmFtZXMgdGhhdCBjYW4gc3Vydml2ZSByb3Vu
ZCB0cmlwcyBmb3IgdXNlIGJ5IHBnX2R1bXAsIGFuZCwgc29tZXdoYXQgc3VwcmlzaW5nbHksICJi
aXQiIHdpdGggZG91YmxlIHF1b3RlcyBtZWFucyBiaXQgd2l0aCB0eXBtb2QgLTEgd2hlcmVhcyBi
aXQgd2l0aG91dCBkb3VibGUgcXVvdGVzIG1lYW5zIGJpdCB3aXRoIHR5cG1vZCAxIEFLQSBiaXQo
MSkuICBHb3J5IGRldGFpbHMgaGVyZToNCg0KaHR0cDovL2dpdC5wb3N0Z3Jlc3FsLm9yZy9naXR3
ZWIvP3A9cG9zdGdyZXNxbC5naXQ7YT1ibG9iO2Y9c3JjL2JhY2tlbmQvdXRpbHMvYWR0L2Zvcm1h
dF90eXBlLmMNCg0KPiBOb3cgaWYgSSBjYWxsIHNhbWUgc3RhdGVtZW50IGRpcmVjdGx5LCBpLmUu
DQo+DQo+IHNlbGVjdCBjYXN0KDEgYXMgYml0KSA7DQo+DQo+IEl0IGdpdmVzIG1lIG91dHB1dCBs
aWtlIHRoaXMuDQo+DQo+IFtQaWN0dXJlIG9mIFBHQWRtaW4gc2hvd2luZyBhIGNvbHVtbidzIHR5
cGUgYXM6IGJpdCgxKV0NCg0KSW4gdGhpcyBjYXNlIFBRZnR5cGUoLi4uKSA9IDE1NjAgYW5kIFBR
Zm1vZCguLi4pID0gMSwgd2hpY2ggZm9ybWF0X3R5cGUgcmVuZGVycyBhcyAnYml0KDEpJyAod2l0
aG91dCBkb3VibGUgcXVvdGVzKS4NCg0KPiBOb3cgbXkgcXVlc3Rpb24vZG91YnQvY29uY2VybiB3
YXMgd2h5IGRpZCBpdCB0aHJvdyDigJxiaXTigJ0gdG8gbWUgaW50byBmaXJzdCBleGFtcGxlIGFu
ZCBub3Qgc2ltcGxlIGJpdD8NCg0KRnVuY3Rpb25zIGRvbid0IHRyYWNrIHRoZSB0eXBtb2RzIG9m
IGFyZ3VtZW50cyBvciByZXR1cm4gdmFsdWVzIChpbmNsdWRpbmcgdGhlIG91dCBhcmd1bWVudHMg
dGhhdCBhcmUgdXNlZCB0byBpbXBsZW1lbnQgUkVUVVJOUyBUQUJMRSkuICBZb3UgY2FuIHdyaXRl
IHRoZW0sIGJ1dCB0aGV5J3JlIGRpc2NhcmRlZCwgc28geW91ciBkYXRhIHR5cGUgYml0IEFLQSBi
aXQoMSkgd2FzIHJlcGxhY2VkIHdpdGggYml0IHdpdGggdHlwbW9kIC0xIEFLQSAiYml0Ii4NCg0K
LS0NClRob21hcyBNdW5ybw0KaHR0cDovL3d3dy5lbnRlcnByaXNlZGIuY29tDQo=