[BUGS] Outer joins aren't working with views
Grzegorz Mucha ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description Outer joins aren't working with views Long Description It seems outer joins are not working at all(they work as inner joins so far). For example, see below: (the result is identical for inner and outer join) - two rows fetched from db(as I recall, there should be one more row having t1.id=3) Sample Code create table t1(id serial primary key); create table t2(id2 serial primary key, id int); insert into t1 values (1); insert into t1 values (2); insert into t1 values (3); insert into t2 (id) values(1); insert into t2 (id) values(2); select t1.*, t2.* from t1 natural left outer join t2; No file was uploaded with this report
[BUGS] grant oddity
POSTGRESQL BUG REPORT TEMPLATE Your name : Bruno Wolff III Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Pentium Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.16-4 (Redhat) PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.3 Compiler used (example: gcc 2.8.0) : N/A installed from RPM Please enter a FULL description of your problem: That documentation on access to tables seems to be at odds with what happens. How grant affects access isn't very intuitive. I am not sure if the handling of access to tables or the documentation is broken. The problem is that when access is granted explicitly, the implicit access by the owner of the object (at least for select) is lost. The work around is to also give explicit access to the owner. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- Script started on Thu Dec 14 10:35:27 2000 [bruno@wolff bruno]$ psql area Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit area=> create table test (col1 int); CREATE area=> select * from test; col1 -- (0 rows) area=> grant select on test to nobody; CHANGE area=> select * from test; ERROR: test: Permission denied. area=> drop table test; DROP area=> \q [bruno@wolff bruno]$ exit exit Script done on Thu Dec 14 10:38:05 2000 If you know how this problem might be fixed, list the solution below: - I don't know how to fix this; I just have the workaround mentioned above.
[BUGS] DON'T APPROVE PREVIOUS Re: Bug in CAST() with time data types
On Fri, 15 Dec 2000, Alvaro Herrera wrote: > Your name : Alvaro Herrera > Your email address: [EMAIL PROTECTED] [...] Moderator: Sorry, I tried 7.0.3 and the bug is fixed. Please don't approve the previous post. (Anyway, there's a bug in the FTP site, but I don't know who to talk to -- do I report it here? It has to do with /pub/latest being a directory which contains a symlink called "latest" which in turn points to an inexistant entry. I will appreciate any human feedback on this) -- Alvaro Herrera ()
[BUGS] Bug in CAST() with time data types
Your name : Alvaro Herrera
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : AMD Athlon
Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.17
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0) : RPM for Mandrake Linux
Please enter a FULL description of your problem:
When inserting timestamps or interval types whose precision is one full
minute, Postgres substracts one minute from the data and add 60 seconds
(00:01:00 becomes 00:00:60). Later, when reading this data Postgres says
"Bad timestamp external representation '2000-12-14 18:25:60.00-03'" and
refuses to understand it.
I gave a look at the list of fixed bugs for 7.0.3, but it isn't listed.
(sorry, Mandrake hasn't provided upgraded packages yet, and I don't want to
mess around with the installation).
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
rwatch=> CREATE TABLE test (val1 TIMESTAMP, val2 INTERVAL);
CREATE
rwatch=> INSERT INTO test VALUES (CAST ('2000-12-14 18:25:00.00-03' AS
TIMESTAMP), CAST ('00:01:00' AS INTERVAL));
INSERT 4400170 1
rwatch=> SELECT * FROM test;
val1| val2
---+--
2000-12-14 18:25:60.00-03 | 00:00:60
(1 row)
(cutting and pasting the text from the select above):
rwatch=> INSERT INTO test VALUES (CAST ('2000-12-14 18:25:60.00-03' AS
TIMESTAMP));
ERROR: Bad timestamp external representation '2000-12-14 18:25:60.00-03'
If you know how this problem might be fixed, list the solution below:
-
Sorry, I don't even know where to look. It is probably a bug in the way
CAST() treats it's arguments, when dealing with time data types.
--
Alvaro Herrera ()
Re: [BUGS] Outer joins aren't working with views
It works for me: regression=# select t1.*, t2.* from t1 natural left outer join t2; id | id2 | id +-+ 1 | 1 | 1 2 | 2 | 2 3 | | (3 rows) What version are you using? regards, tom lane
Re: [BUGS] grant oddity
Bruno Wolff III <[EMAIL PROTECTED]> writes: > The problem is that when access is granted explicitly, the implicit access > by the owner of the object (at least for select) is lost. The work around > is to also give explicit access to the owner. Right. This is a known bug --- it's fixed in 7.1beta1. regards, tom lane
Re: [BUGS] Bug report
I think this is fixed in the current snapshot. > > POSTGRESQL BUG REPORT TEMPLATE > > > > Your name : Bruno Wolff III > Your email address: [EMAIL PROTECTED] > > > System Configuration > - > Architecture (example: Intel Pentium) : Intel Pentium > > Operating System (example: Linux 2.0.26 ELF): Linux 2.2.16-4 (Redhatisms) > > PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.3 > > Compiler used (example: gcc 2.8.0) : N/A installed via RPM > > > Please enter a FULL description of your problem: > > I am getting problems when using to_char to convert an int4 to roman numeral > and to_char to convert a timestamp to a string in a view. The errors > vary, but it looks like there is some sort of memory corruption. > > The select that has the problem is: > > select areaid, lname, fmname, aname, trim(to_char(gen, 'RN')), to_char(touched, >'-MM-DD') from cname_web order by areaid; > > Here is a sample of one kind of error I was getting with the about query in > test.sql: > Script started on Wed Dec 13 22:41:31 2000 > [bruno@wolff test]$ psql area > Welcome to psql, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help on internal slash commands >\g or terminate with semicolon to execute query >\q to quit > > area=> \i test.sql > psql:test.sql:1: pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > psql:test.sql:1: connection to server was lost > [bruno@wolff test]$ exit > exit > > Script done on Wed Dec 13 22:41:47 2000 > > This is the script I used to create the tables: > > -- Definitions for the AREA database > -- Author: Bruno Wolff III > -- Last Revision: December 9, 2000 > > -- Privacy levels > -- This table is used in views to change fields to null if the privacy > -- level of the request is less than the privacy level of the row. > > -- priv Table name > -- pname Name to be used to reference the privacy level > -- pord A number used to order the privacy levels > -- ptxt A text description that can be used in a prompt > > -- pname values: > -- any The data can be used for anything > -- web The data can be placed on public web pages > -- request The data can be given out in response to one off requests > -- memberThe data can be given to other AREA members > -- tdThe data can be given to tournament directors > -- admin The data can be always be seen by the AREA administrators > > create table priv ( > pname text primary key, > pord int4 unique constraint pord_nonnegative check (pord >= 0), > ptxt text > ); > > -- Allow access to it from the web server account > -- Doing that breaks the default, so grant access to the admin account > > grant select on priv to nobody; > grant all on priv to bruno; > > -- The number used isn't relevant as only the ordering matters. > -- However leaving room to insert new levels in without changing > -- old ones seems like a good idea. > > copy priv from stdin using delimiters ','; > any,0,No restrictions on access > web,100,Allow anonymous access via the web > request,200,Allow access via one off requests > member,300,Allow access by people believed to be AREA members > td,400,Allow access by tournament directors > admin,500,Access by AREA administrators is always allowed > \. > > -- The current name table definition. > -- This is the primary definition for AREA members > -- There will also need to be a history table to track areaid and name changes > -- so that the admins have a way to check on records to resolve problems. > -- Names are limited to US ASCII characters. They can contain letters (a-z) > -- with case being maintained. They may also have space, ', or - between two > -- letters. They will be sorted using the ASCII ordering with uppercase > -- characters treated as the lowercase equivalent. > > -- cnameTable name > -- areaid Current AREA ID of a person > -- lnameCurrent last name of a person > -- fmname Current first and middle names of a person > -- anameCurrent alternate first and middle names of a person > -- gen Generation number (Sr or I > 1, Jr or II > 2, III > 3, IV > 4, etc) > -- Note this limitation matches that of the postgres routine that > -- prints roman numerals. > -- privacy Privacy level for their name data > -- touched When the areaid or name (not privacy) information last changed > > create table cname ( > areaid text primary key constraint bad_char_in_id check > (areaid ~ '^[A-Z0-9]+(-[A-Z0-9]+)*(\\.
Re: [BUGS] Outer joins aren't working with views
What version are you using? The sample code works for me on current sources, three rows with the last one as 3|null|null Stephan Szabo [EMAIL PROTECTED] On Fri, 15 Dec 2000 [EMAIL PROTECTED] wrote: > Grzegorz Mucha ([EMAIL PROTECTED]) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Outer joins aren't working with views > > Long Description > It seems outer joins are not working at all(they work as inner joins so far). > For example, see below: > (the result is identical for inner and outer join) - two rows fetched from db(as I >recall, there should be one more row having t1.id=3) > > Sample Code > create table t1(id serial primary key); > create table t2(id2 serial primary key, id int); > insert into t1 values (1); > insert into t1 values (2); > insert into t1 values (3); > insert into t2 (id) values(1); > insert into t2 (id) values(2); > select t1.*, t2.* from t1 natural left outer join t2; > > No file was uploaded with this report >
[BUGS] JDBC Driver Authentication Bug
Cheeren Shieh ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description JDBC Driver Authentication Bug Long Description If the userid is valid, even though null password or wrong password is sent to server with JDBC Driver, the authentication still pass! What is a terrible bug! The JDBC Driver is "jdbc7.0-1.2.jar". Sample Code No file was uploaded with this report
Re: [BUGS] Outer joins aren't working with views
> It works for me: > regression=# select t1.*, t2.* from t1 natural left outer join t2; > id | id2 | id > +-+ > 1 | 1 | 1 My recollection is that SQL9x requires that the join result lose the link to the original table names. That is, select id, id2 from t1 natural left outer join t2; is legal, but select t1.id, ... is not. If one needs to label the join product, then one uses aliases, as select tx.* from (t1 natural left outer join t2) as tx; or select tx.* from (t1 natural left outer join t2) as tx (c1, c2); I could see allowing in the target list explicit reference to the underlying tables as an extension when there is no ambiguity. However, in this case should the natural join in the original example do the join on the column "id", and not have two columns of name "id" available after the join? How do you read the spec and this example? My original reading was from the Date and Darwen book, and the SQL99 spec we have is (to put it nicely) a bit harder to follow. I'll write some of this up for the syntax section of the user's guide once I'm clear on it... ref: ansi-iso-9075-2-1999.txt from the draft documents we found on the web last year. ISO/IEC 9075-2:1999 SQL - Part 2: SQL/Foundation- September 23, 1999 [Compiled using SQL3_ISO option] section 7.7 rule 7 - Thomas
Re: [BUGS] Bug in CAST() with time data types
> Compiler used (example: gcc 2.8.0) : RPM for Mandrake Linux ... > When inserting timestamps or interval types whose precision is one full > minute, Postgres substracts one minute from the data and add 60 seconds > (00:01:00 becomes 00:00:60). Later, when reading this data Postgres says > "Bad timestamp external representation '2000-12-14 18:25:60.00-03'" and > refuses to understand it. ... > Sorry, I don't even know where to look. It is probably a bug in the way > CAST() treats it's arguments, when dealing with time data types. No, unfortunately it is a bug in the way Mandrake builds their RPMs :( The default compiler flags for RPM building under Mandrake include both "-O3" (shrunk to "-O2" explicitly by the PostgreSQL RPM build) and "-ffast-math". The GNU gcc folks strongly recommend against mixing these two flags! afaict the *only* symptom of this misuse of the gcc compiler for PostgreSQL is the symptom that you see, with incorrect rounding in the date/time types. I've posted a corrected .rpmrc file on ftp.postgresql.org in the /pub/binary area if you want to repair this by rebuilding this from .src.rpm. And if you know a way to get in touch with the Mandrake RPM builders that would be helpful; I have not had the time to subscribe to the cooker mailing list to try to work out this issue. In the meantime pick up RPMs from the PostgreSQL ftp site, which have this problem corrected by building against the above-mentioned .rpmrc file. I've cross-posted this to the RPM contact address for Mandrake (hi!) and would be happy to provide more information. - Thomas
