[BUGS] Bug report: selects rope in tables they shouldn't
Sorry for the unformatted bug report, I can't find the bug form on postgresql.org. Version: 7.0 Platform: Debian Linux (woody) running kernel 2.2.14 Question: Is it valid syntax to refer to a table when it's not in the FROM of the select? It's producing awfully wierd output: CREATE TABLE A ( x integer ); CREATE TABLE B( x integer ); insert into A values (1); insert into B values (2); select * from A where B.x=2; x --- 1 (1 row) svan=# explain select * from A where B.x=2; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..322.50 rows=1 width=8) -> Seq Scan on b (cost=0.00..22.50 rows=10 width=4) -> Seq Scan on a (cost=0.00..20.00 rows=1000 width=4) EXPLAIN -- ,,, (. .) +--ooO-(_)-Ooo --- -- - - - - | Stephen van Egmond http://bang.dhs.org/
[BUGS] Queries with multiple USINGs crash backend
Hello, The following SQL demonstrates a crash in the backend. It occurs in both the current Debian Linux 7.0.2 package, and in a separately compiled version of the same. --8<-- CREATE TABLE A ( A INT4, B INT4, C INT4 ); CREATE TABLE B ( A INT4, B INT4, C INT4 ); CREATE TABLE C ( A INT4, B INT4, C INT4 ); /* The inserts are optional, but demonstrate that the first two queries * produce correct results. */ INSERT INTO A VALUES (1,2,3); INSERT INTO A VALUES (1,2,5); INSERT INTO B VALUES (1,2,3); INSERT INTO B VALUES (1,2,5); INSERT INTO B VALUES (1,2,6); INSERT INTO C VALUES (1,2,4); /* This runs OK */ SELECT * FROM A,B,C WHERE A.A = B.A AND A.B = B.B AND A.C = B.C AND B.A = C.A AND B.B = C.B; /* As does this */ SELECT * FROM A JOIN B ON (A.A = B.A AND A.B = B.B AND A.C = B.C) JOIN C ON (B.A = C.A AND B.B = C.B); /* * This makes the backend close the channel unexpectedly (die), * but should be equivalent to the example using ON. */ SELECT * FROM A JOIN B USING (A,B,C) JOIN C USING (A,B); --8<-- Regards, Chris
[BUGS] joins on the same table with aggregates
POSTGRESQL BUG REPORT TEMPLATE
Your name : Darcy
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) :Intel Pentium III
Operating System (example: Linux 2.0.26 ELF) : FreeBSD 3.4-STABLE
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0) : gcc version 2.7.2.3
Please enter a FULL description of your problem:
When doing a join on the same table involving count there apears to be a
cartsian product happeing on the counts returned
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
CREATE TABLE "logging_real" (
"propertyid" int4 NOT NULL,
"search" bool,
"service" bool
);
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(2,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(3,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(3,'t','f');
SELECT propertyid,count(propertyid),search,service FROM logging_real GROUP
BY propertyid,search,service;
-- notice the corret values
CREATE VIEW current AS SELECT propertyid,count(propertyid),search,service
FROM logging_real GROUP BY propertyid,search,service;
CREATE VIEW current2 AS SELECT propertyid,count(propertyid),search,service
FROM logging_real GROUP BY propertyid,search,service;
SELECT current.propertyid,current.count AS searchtrue ,current2.count AS
searchfalse FROM current,current2 WHERE
current.propertyid=current2.propertyid AND current.search='t' AND
current2.search='f' AND current.service='f' AND current2.service='f';
-- the results expected here are:
-- propertyid | searchtrue | searchfalse
-- ++-
--1 | 5 | 2
--3 | 1 | 1
SELECT
a.propertyid AS apropertyid ,count(a.propertyid) AS acount ,a.search AS
asearch ,a.service AS aservice,
b.propertyid AS bpropertyid ,count(b.propertyid) AS bcount ,b.search AS
bsearch ,b.service AS bservice
FROM
logging_real A, logging_real B
WHERE
a.propertyid = b.propertyid
AND a.search = 't'
AND b.search = 'f'
AND a.service= 'f'
AND b.service= 'f'
GROUP BY apropertyid,bpropertyid,asearch,bsearch,aservice,bservice;
-- the results expected here are:
--apropertyid | acount | asearch | aservice | bpropertyid | bcount |
bsearch | bservice
--
--
-- 1 | 5 | t |f | 1 | 2 |
f |f
-- 3 | 2 | t |f | 3 | 1 |
f |f
If you know how this problem might be fixed, list the solution below:
-
[BUGS] \d* won't work in 7.0.2?
Dear PostgreSQL people, I'm baffled at this: \d (and friends) won't give me the definition of a table, view, function, etc: $ initdb /var/lib/pgsql ...stuff... $ psql template1 Welcome to psql, the PostgreSQL interactive terminal. ...stuff... template1=# \dS List of relations Name | Type | Owner +-+-- pg_aggregate | table | postgres pg_am | table | postgres pg_amop| table | postgres pg_amproc | table | postgres ...stuff... template1=# \d pg_am ERROR: getattproperties: no attribute tuple 1259 -2 template1=# create table test (id int, val int); CREATE template1=# \dt List of relations Name | Type | Owner --+---+-- test | table | mgiovann (1 row) template1=# \d test ERROR: getattproperties: no attribute tuple 1259 -2 template1=# \da ERROR: getattproperties: no attribute tuple 1247 -2 template1=# \dd ERROR: get_attdisbursion: no attribute tuple 16932 -2 template1=# \q $ The system I'm using is a fresh installation of the 7.0.2-2.ppc.rpm's on an MkLinux DR3 running on a PowerMac 8100/80. More specifically: * No exported shell variables were set, the location of pgsql files (bin, lib, database) and directories is exactly as the distribution expects: /var/lib/pgsql * No command line options were set, a simple "postmaster -D /var/lib/pgsql" command was issued. * Installation was, basically, "rpm -i postgresql*" * "createuser mgiovann" was executed, with create database/user permissions. * Version is: PostgreSQL 7.0.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2 * I've installed from the PPC RPM's, version 7.0.2-2 (found in ftp://ftp.postgresql.org//pub/binary/v7.0.2/redhat-RPM/RPMS/PPC/ as of today). * Platform info: PowerMac 8100/80, PowerPC 601/80MHz/32MB RAM $ cat /proc/cpuinfo processor : 0 cpu : 601 revision: 0.1 bogomips: 78.85 machine : PowerMac $ cat /proc/meminfo total:used:free: shared: buffers: cached: Mem: 33554432 27328512 62259200 1118208 7114752 Swap: 83881984 651264 83230720 MemTotal: 32768 kB MemFree: 6080 kB MemShared:0 kB Buffers: 1092 kB Cached:6948 kB SwapTotal:81916 kB SwapFree: 81280 kB $ cat /proc/version Linux version 2.0.37-osfmach3 ([EMAIL PROTECTED]) (gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release)) GENERIC_08alpha-20 Fri Jul 30 11:07:38 PDT 1999 $ gcc -v Reading specs from /usr/lib/gcc-lib/ppc-redhat-linux/2.95.2/specs gcc version 2.95.2 19990906 (prerelease) Please note, I *did not* compile PostgreSQL myself. I've tried grepping the documentation for something along the lines of "getattproperties", or "\d", but I could find nothing relevant to my problem. I sincerely hope it's not something stupid I did, the last thing I'd want is to waste your time. If that's the case, please accept my apologies in advance. Thank you, and best regards, Matías Giovannini Script S.A. +5411-4325-6546 [EMAIL PROTECTED] -- Microsoft shouldn't be broken up. It should be shut down. -- Phil Agre
