agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSQL Bug?
9+ messages / 5 participants
[nested] [flat]
* SQL Bug?
@ 1998-06-26 01:54 Richard Lynch <lynch@lscorp.com>
0 siblings, 1 reply; 9+ messages in thread
From: lynch@lscorp.com @ 1998-06-26 01:54 UTC (permalink / raw)
To: pgsql-sql
I'm far from an SQL expert, but...
select field1 from table1 where table2.field2 = table1.field3;
This query, which uses tables in the where clause that aren't in the list
of tables, actually does what one would sorta expect in PostgreSQL, but
isn't it a bug for it to do that?...
Not that I mind, since it's what I accidentally coded anyways.
--
--
-- "TANSTAAFL" Rich lynch@lscorp.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] SQL Bug?
@ 1998-06-26 02:13 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Richard Lynch <lynch@lscorp.com>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 1998-06-26 02:13 UTC (permalink / raw)
To: lynch@lscorp.com; +Cc: pgsql-sql
>
> I'm far from an SQL expert, but...
>
> select field1 from table1 where table2.field2 = table1.field3;
>
> This query, which uses tables in the where clause that aren't in the list
> of tables, actually does what one would sorta expect in PostgreSQL, but
> isn't it a bug for it to do that?...
>
> Not that I mind, since it's what I accidentally coded anyways.
It is a feature we have not removed.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 9+ messages in thread
* SQL Bug
@ 2016-10-06 10:22 Bujji Babu <bujji.babu@heales.com>
0 siblings, 3 replies; 9+ messages in thread
From: Bujji Babu @ 2016-10-06 10:22 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Bug
@ 2016-10-06 10:23 Bujji Babu <bujji.babu@heales.com>
parent: Bujji Babu <bujji.babu@heales.com>
2 siblings, 0 replies; 9+ messages in thread
From: Bujji Babu @ 2016-10-06 10:23 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org; pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[application/x-trash] test.pgsql.bak (2.7K, ../../801577318.1542367.1475749392421.JavaMail.zimbra@heales.com/3-test.pgsql.bak)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Bug
@ 2016-10-06 10:44 Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
parent: Bujji Babu <bujji.babu@heales.com>
2 siblings, 0 replies; 9+ messages in thread
From: Oleksandr Shulgin @ 2016-10-06 10:44 UTC (permalink / raw)
To: Bujji Babu <bujji.babu@heales.com>; +Cc: Pg Bugs <pgsql-bugs@postgresql.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [BUGS] SQL Bug
@ 2016-10-06 10:44 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Bujji Babu <bujji.babu@heales.com>
2 siblings, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2016-10-06 10:44 UTC (permalink / raw)
To: Bujji Babu <bujji.babu@heales.com>; pgsql-bugs@postgresql.org; pgsql-sql
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 field type 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [BUGS] SQL Bug
@ 2016-10-06 10:59 Bujji Babu <bujji.babu@heales.com>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 1 reply; 9+ messages in thread
From: Bujji Babu @ 2016-10-06 10:59 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: pgsql-bugs@postgresql.org; pgsql-sql
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 field type 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL Bug
@ 2016-10-06 11:07 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Bujji Babu <bujji.babu@heales.com>
0 siblings, 1 reply; 9+ messages in thread
From: Heikki Linnakangas @ 2016-10-06 11:07 UTC (permalink / raw)
To: Bujji Babu <bujji.babu@heales.com>; +Cc: pgsql-bugs@postgresql.org; pgsql-sql
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [BUGS] SQL Bug
@ 2016-10-06 11:19 Bujji Babu <bujji.babu@heales.com>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 0 replies; 9+ messages in thread
From: Bujji Babu @ 2016-10-06 11:19 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: pgsql-bugs@postgresql.org; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2016-10-06 11:19 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-06-26 01:54 SQL Bug? Richard Lynch <lynch@lscorp.com>
1998-06-26 02:13 ` Bruce Momjian <maillist@candle.pha.pa.us>
2016-10-06 10:22 SQL Bug Bujji Babu <bujji.babu@heales.com>
2016-10-06 10:23 ` Re: SQL Bug Bujji Babu <bujji.babu@heales.com>
2016-10-06 10:44 ` Re: SQL Bug Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
2016-10-06 10:44 ` Re: [BUGS] SQL Bug Heikki Linnakangas <hlinnaka@iki.fi>
2016-10-06 10:59 ` Re: [BUGS] SQL Bug Bujji Babu <bujji.babu@heales.com>
2016-10-06 11:07 ` Re: SQL Bug Heikki Linnakangas <hlinnaka@iki.fi>
2016-10-06 11:19 ` Re: [BUGS] SQL Bug Bujji Babu <bujji.babu@heales.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox