Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Bug

SQL Bug

From
Bujji Babu
Date:
Version string:   PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
 
Please let me know if anyone knows how to fix this. below query works fine in version 9.4.
 
 
select * from connectby('emp','empid','mgrid','1',0)
AS t(keyid text, parent_keyid text, level int);
 
 

ERROR: invalid return type DETAIL: SQL key field type text does not match return key field type integer. ********** Error ********** ERROR: invalid return type SQL state: 42804 Detail: SQL key field type text does not match return key field type integer.
 
CREATE TABLE public.emp
(
empid integer NOT NULL,
name text,
mgrid integer,
CONSTRAINT emp_pkey PRIMARY KEY (empid)
)
insert into emp(empid,mgrid,name)
values(1,0,'emp1');
insert into emp(empid,mgrid,name)
values(2,1,'emp1');
insert into emp(empid,mgrid,name)
values(3,1,'emp1');
insert into emp(empid,mgrid,name)
values(4,2,'emp1');
insert into emp(empid,mgrid,name)
values(5,2,'emp1');
 

Thanks,
M.Bujji Babu

Thanks&Regards,
M.Bujji Babu.

Re: SQL Bug

From
Bujji Babu
Date:

Version string:   PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
 
Please let me know if anyone knows how to fix this. below query works fine in version 9.4.

schema Backup file attached.
 
 
select * from connectby('emp','empid','mgrid','1',0)
AS t(keyid text, parent_keyid text, level int);
 
 

ERROR: invalid return type DETAIL: SQL key field type text does not match return key field type integer. ********** Error ********** ERROR: invalid return type SQL state: 42804 Detail: SQL key field type text does not match return key field type integer.
 
CREATE TABLE public.emp
(
empid integer NOT NULL,
name text,
mgrid integer,
CONSTRAINT emp_pkey PRIMARY KEY (empid)
)
insert into emp(empid,mgrid,name)
values(1,0,'emp1');
insert into emp(empid,mgrid,name)
values(2,1,'emp1');
insert into emp(empid,mgrid,name)
values(3,1,'emp1');
insert into emp(empid,mgrid,name)
values(4,2,'emp1');
insert into emp(empid,mgrid,name)
values(5,2,'emp1');
 

Thanks&Regards,
M.Bujji Babu.


Attachment

Re: [BUGS] SQL Bug

From
Heikki Linnakangas
Date:
On 10/06/2016 01:22 PM, Bujji Babu wrote:
> Version string: PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4),
64-bit
> Please let me know if anyone knows how to fix this. below query works fine in version 9.4.
> select * from connectby('emp','empid','mgrid','1',0)
> AS t(keyid text, parent_keyid text, level int);
>
> ERROR: invalid return type DETAIL: SQL key field type text does not match return key field type integer. **********
Error********** ERROR: invalid return type SQL state: 42804 Detail: SQL key field type text does not match return key
fieldtype integer.
 

Try:

select * from connectby('emp','empid','mgrid','1','0')
AS t(keyid integer, parent_keyid integer, level int);


connectby() was made more strict about the datatypes matching in 9.5. 
See commit 37507962c3d2123b0f21c50d6172fd0b1e059fe7.

- Heikki

Re: [BUGS] SQL Bug

From
Oleksandr Shulgin
Date:
On Thu, Oct 6, 2016 at 12:22 PM, Bujji Babu <bujji.babu@heales.com> wrote:
>
> Version string:   PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
>  
> Please let me know if anyone knows how to fix this. below query works fine in version 9.4.
>  
>  
> select * from connectby('emp','empid','mgrid','1',0)
> AS t(keyid text, parent_keyid text, level int);
>  
>  
>
> ERROR: invalid return type DETAIL: SQL key field type text does not match return key field type integer. ********** Error ********** ERROR: invalid return type SQL state: 42804 Detail: SQL key field type text does not match return key field type integer.
>  
> CREATE TABLE public.emp
> (
> empid integer NOT NULL,
> name text,
> mgrid integer,
> CONSTRAINT emp_pkey PRIMARY KEY (empid)
> )

Hello,

This is not a bug.

From the documentation at https://www.postgresql.org/docs/9.5/static/tablefunc.html

> The first two output columns are used for the current row's key and its parent row's key; they must match the type of the table's key field.

So, you need to change the type of the first two returned columns to int:

=# select * from connectby('emp','empid','mgrid','1',0) AS t(keyid int, parent_keyid int, level int);
 keyid | parent_keyid | level 
-------+--------------+-------
     1 |              |     0
     2 |            1 |     1
     4 |            2 |     2
     5 |            2 |     2
     3 |            1 |     1
(5 rows)

--
Alex

Re: [BUGS] SQL Bug

From
Bujji Babu
Date:
Dear Heikki,

Still same error msg.

select * from connectby('emp','empid','mgrid','1','0')
AS t(keyid text, parent_keyid text, level int);


ERROR:  invalid return type
DETAIL:  SQL key field type text does not match return key field type integer.
********** Error **********

ERROR: invalid return type
SQL state: 42804
Detail: SQL key field type text does not match return key field type integer.


Thanks&Regards,
M.Bujji Babu.

----- Original Message -----
From: "Heikki Linnakangas" <hlinnaka@iki.fi>
To: "Bujji Babu" <bujji.babu@heales.com>, pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org
Sent: Thursday, 6 October, 2016 11:44:51 AM
Subject: Re: [BUGS] SQL Bug

On 10/06/2016 01:22 PM, Bujji Babu wrote:
> Version string: PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4),
64-bit
> Please let me know if anyone knows how to fix this. below query works fine in version 9.4.
> select * from connectby('emp','empid','mgrid','1',0)
> AS t(keyid text, parent_keyid text, level int);
>
> ERROR: invalid return type DETAIL: SQL key field type text does not match return key field type integer. **********
Error********** ERROR: invalid return type SQL state: 42804 Detail: SQL key field type text does not match return key
fieldtype integer.
 

Try:

select * from connectby('emp','empid','mgrid','1','0')
AS t(keyid integer, parent_keyid integer, level int);


connectby() was made more strict about the datatypes matching in 9.5. 
See commit 37507962c3d2123b0f21c50d6172fd0b1e059fe7.

- Heikki

Re: [BUGS] SQL Bug

From
Heikki Linnakangas
Date:
On 10/06/2016 01:59 PM, Bujji Babu wrote:
> Dear Heikki,
>
> Still same error msg.
>
> select * from connectby('emp','empid','mgrid','1','0')
> AS t(keyid text, parent_keyid text, level int);

That's not what I typed. The keyid and parent_keyid columns need to be 
integers, not text, like in the table.

- Heikki

Re: [BUGS] SQL Bug

From
Bujji Babu
Date:
Dear Heikki,

It works.


Thanks&Regards,
M.Bujji Babu.

----- Original Message -----
From: "Heikki Linnakangas" <hlinnaka@iki.fi>
To: "Bujji Babu" <bujji.babu@heales.com>
Cc: pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org
Sent: Thursday, 6 October, 2016 12:07:27 PM
Subject: Re: [BUGS] SQL Bug

On 10/06/2016 01:59 PM, Bujji Babu wrote:
> Dear Heikki,
>
> Still same error msg.
>
> select * from connectby('emp','empid','mgrid','1','0')
> AS t(keyid text, parent_keyid text, level int);

That's not what I typed. The keyid and parent_keyid columns need to be 
integers, not text, like in the table.

- Heikki