Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
Tom Lane wrote: > Well, the case that is bothering me is stuff like > > (select '1' union select '1 ') union all select 2; > > The first union produces 1 row if you resolve the constants as > integers, but 2 rows if you resolve as text, which I think is what > the spec would expect here. The way I have read it, the spec would make those first two literals char(1) and char(2), and the trailing space would be ignored in an equality comparison between those. But you could make your point with a leading space, I think. > Now in this particular case we'd fail with "UNION types text and > integer cannot be matched" so you never get as far as noticing > what the runtime behavior is. Right, which makes it OK to provide something which *does* work here as an extension. > [ experiments a bit... ] You can show a difference in results > with this: > > regression=# (select '1' union select '1 ') union all select > '2'::bpchar; > ?column? > -- > 1 > 1 > 2 > (3 rows) > > This produces 3 rows because the UNION resolves as text, but what > we're discussing here would allow it to resolve as bpchar, which > would have different behavior: > > regression=# (select '1' union select '1 '::bpchar) union all > select '2'::bpchar; > ?column? > -- > 1 > 2 > (2 rows) Which would be the right answer according to the spec, although that seems to be sort of an accident here. > It's debatable about how important this is, and one could also say > that the behavior of our bpchar is not entirely standards > compliant in the first place, so maybe this isn't a compelling > example. But I'm worried that there may be related cases where > it's a bigger deal. We are in territory where the choice to treat literals as type unknown where the spec requires bpchar will probably lead to *some* corner cases where behavior is nonstandard no matter what we do. I think the best we can do here is (in what I think is order of importance): (1) Try not to break anything which works for current PostgreSQL queries. (2) Try not to add any additional behavioral differences from the standard where a query now runs without error with standard-conforming results. (3) Try to maintain some coherent handling for unknown values. I think that's currently lacking when the first of these fails and the others work: select null union select null union select 1; select null union (select null union select 1); select null union select 1 union select null; Likewise, the first of these fails and the others don't: select '1' union select '1 ' union select 1; select '1' union (select '1 ' union select 1); select '1' union select 1 union select '1 '; Explaining that could be tough. I'm arguing that the first line should be made to work like the others in terms of type resolution. Since that case now throws and error under both the standard and current PostgreSQL releases, it's OK as an extension. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
On 13/04/11 19:32, Tom Lane wrote: "Jeff Wu" writes: The UNION construct (as noted on this page: http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will cast unknown types to TEXT, however, if you try to do three or more UNIONs the order in which the UNIONs are executed will cause some columns to be cast to TEXT prematurely. The result is a type mismatch error. Or maybe we could find out that some other products do it like that despite what the spec says? regards, tom lane I happen to have a MS SQLServer 2008 instance at work as well as a MySQL 5.1 and an Oracle 10g. With the query: SELECT 1,null,null UNION SELECT 2,3,null UNION SELECT 3,null,4 In MS SQLServer I get (NB: no column headings): -- -- 1 | | 2 | 3 | 3 | | 4 In MySQL I get: 1 | NULL | NULL 1 | | 2 | 3 | 3 | | 4 In Oracle I get a delicious error message: Error: ORA-00923: FROM keyword not found where expected SQLState: 42000 ErrorCode: 923 Position: 19 Regards, -- Mike Fowler Registered Linux user: 379787 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
Mike Fowler wrote: > SELECT 1,null,null > UNION > SELECT 2,3,null > UNION > SELECT 3,null,4 > In Oracle I get a delicious error message: > > Error: ORA-00923: FROM keyword not found where expected For Oracle, shouldn't that be: SELECT 1,null,null FROM DUAL UNION SELECT 2,3,null FROM DUAL UNION SELECT 3,null,4 FROM DUAL -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
On 14/04/11 17:05, Kevin Grittner wrote:
SELECT 1,null,null FROM DUAL
UNION
SELECT 2,3,null FROM DUAL
UNION
SELECT 3,null,4 FROM DUAL
Sadly I can't profess to knowing Oracle, however if I run the query as
suggested I get:
1 | NULL | NULL
1 | |
2 | 3 |
3 | | 4
So to summarise, Oracle and PostgreSQL need minor tweaks to run cleanly
and SQLServer and MySQL do not. Given that the change for PostgreSQL is
so minor, I vote for changing the error message as Jeff suggests in the
interim to help users while the standards argument continues. Patch
attached.
Regards,
--
Mike Fowler
Registered Linux user: 379787
*** a/src/backend/parser/parse_coerce.c
--- b/src/backend/parser/parse_coerce.c
***
*** 1161,1167 select_common_type(ParseState *pstate, List *exprs, const char *context,
(errcode(ERRCODE_DATATYPE_MISMATCH),
/*--
translator: first %s is name of a SQL construct, eg CASE */
! errmsg("%s types %s and %s cannot be matched",
context,
format_type_be(ptype),
format_type_be(ntype)),
--- 1161,1167
(errcode(ERRCODE_DATATYPE_MISMATCH),
/*--
translator: first %s is name of a SQL construct, eg CASE */
! errmsg("%s types %s and %s cannot be matched. HINT: Postgres casts unknown types to TEXT by default.",
context,
format_type_be(ptype),
format_type_be(ntype)),
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
Mike Fowler wrote: > So to summarise, Oracle and PostgreSQL need minor tweaks to run > cleanly and SQLServer and MySQL do not. The FROM DUAL in Oracle has nothing to do with the issue at hand. That is just because they always require a FROM clause on every SELECT. DUAL is a special table with one row you can use when you just want to select a literal. That means that all three of the databases you tested have extensions to the standard similar to what is being contemplated for PostgreSQL. If nothing else, adding such an extension would ease migration from those other products, but I think it would prevent some user confusion and posts to the -bugs list, too. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5979: postgres service cannot be started if database is located in NAS
The following bug has been logged online: Bug reference: 5979 Logged by: qintao Email address: qin...@huaweisymantec.com PostgreSQL version: 8.4.7 Operating system: windows server 2008 Description:postgres service cannot be started if database is located in NAS Details: steps to reproduce 1. Share a directory (say \data) from a NAS 2. map the shared directory to a Windows server 2008 R2 host to be a network drive (say Z:) 3. install postgresql to the Windows Server 2008 R2 with the database as z:\db\. 4. try to start the postgresql service from service.msc It's failed with a log in the event viewer: postgres cannot access the server configuration file "C:/symbollink/postgresql.conf": No such file or directory 5. start a Command Prompt, execute the below cmd: C:/Program Files (x86)/PostgreSQL/8.4/bin/pg_ctl.exe start -N "postgresql-8.4" -D "C:/symbollink" -w the service can be started. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
"Kevin Grittner" writes: > Mike Fowler wrote: >> So to summarise, Oracle and PostgreSQL need minor tweaks to run >> cleanly and SQLServer and MySQL do not. > That means that all three of the databases you tested have > extensions to the standard similar to what is being contemplated for > PostgreSQL. Uh, no, it proves they all extend the standard to allow NULL to be written without an immediate cast. Mike's test really fails to prove anything about the point at hand, which is what data type is being imputed to the inner UNION. I don't know those other DBMSes well enough to suggest a test that would be definitive on the point, though. We'd need something where the choice of datatype is material to the final visible result, and at least in PG that requires some knowledge of not-very-standard behaviors. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5974: UNION construct type cast gives poor error message
Tom Lane wrote: > "Kevin Grittner" writes: >> That means that all three of the databases you tested have >> extensions to the standard similar to what is being contemplated >> for PostgreSQL. > > Uh, no, it proves they all extend the standard to allow NULL to be > written without an immediate cast. Mike's test really fails to > prove anything about the point at hand, which is what data type is > being imputed to the inner UNION. The query run was: SELECT 1,null,null UNION SELECT 2,3,null UNION SELECT 3,null,4 It's a bit of a stretch to think that the columns returned from the final union weren't integer, or that integer is the default type of the union of two nulls. It's anyone's guess at this point whether the third column was unknown during the leftmost union and the type set in the next union, or the set of columns involved in the union were all evaluated as a group. If they don't have other literals of unknown type it may be hard to discern the implementation details, but either I've missed something or we're considering similar user visible behavior. > I don't know those other DBMSes well enough to suggest a test that > would be definitive on the point, though. We'd need something > where the choice of datatype is material to the final visible > result, and at least in PG that requires some knowledge of > not-very-standard behaviors. If the implementation details for the other databases are that hard to discern, how much do we care *how* they do it? It seems to me that the important point here is that they don't throw an error on that query and we do. What am I missing? -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5981: Attempt to install language pltcl fails on 64-bit installation
The following bug has been logged online: Bug reference: 5981 Logged by: Chris Price Email address: cjpr...@bigpond.net.au PostgreSQL version: 9.0.3 Operating system: Winders Server 2008 R2 Standard Description:Attempt to install language pltcl fails on 64-bit installation Details: When I try to install the pltcl language on 64-bit Windows version of PostreSQL (9.0.3) it fails with the following message: ERROR: could not access file "$libdir/pltcl": No such file or directory SQL state: 58P01 ActiveTcl is installed. It works on a 32-bit installation of PostgreSQL. I notice that the file pltcl.dll is not installed in the $libdir folder on the 64-bit installation - is there a reason for this? Is the pltcl language available on 64-bit installations on Windows? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Massive memory use for star query
I've recently seen examples of star-like queries using vast amounts of memory in one of our production systems. Here's a simplified example using synthetic data (see attached to generate if desired): SET geqo_threshold = 14; SET from_collapse_limit = 14; SET join_collapse_limit = 14; EXPLAIN SELECT 1 FROM node n JOIN nodekeyword kwn0 ON (n.nodeid = kwn0.nodeid) JOIN keyword kw0 ON (kwn0.keywordid = kw0.keywordid) JOIN nodekeyword kwn1 ON (n.nodeid = kwn1.nodeid) JOIN keyword kw1 ON (kwn1.keywordid = kw1.keywordid) JOIN nodekeyword kwn2 ON (n.nodeid = kwn2.nodeid) JOIN keyword kw2 ON (kwn2.keywordid = kw2.keywordid) JOIN nodekeyword kwn3 ON (n.nodeid = kwn3.nodeid) JOIN keyword kw3 ON (kwn3.keywordid = kw3.keywordid) JOIN nodekeyword kwn4 ON (n.nodeid = kwn4.nodeid) JOIN keyword kw4 ON (kwn4.keywordid = kw4.keywordid) JOIN nodekeyword kwn5 ON (n.nodeid = kwn5.nodeid) JOIN keyword kw5 ON (kwn5.keywordid = kw5.keywordid) WHERE kw0.keyword = 'sscghryv' AND kw1.keyword = 'sscghryv' AND kw2.keyword = 'sscghryv' AND kw3.keyword = 'sscghryv' AND kw4.keyword = 'sscghryv' AND kw5.keyword = 'sscghryv' ; Here's what a ps listing looks like: VSZ RSS SZCMD 1849524 1793680 1791144 postgres: postgres test [local] EXPLAIN So we are using 1.7G doing an *EXPLAIN* - so presumably this is gonna be the join search planning getting expensive for 13 tables. Is it expected that this much memory could/would be used? Could this be evidence of a leak? Note this is a default 9.1 (2011-04-07) build w/o asserts, with a default postgresql.conf. Clearly this particular query is a bit dumb, making the keyword predicates have different values results in much better behaved planning memory usage... and also allowing geqo to do the join search for us prevents the high memory use (however geqo has its own problems in the production variant of this query *one* of the plans it would pick liked to use >100G of temp space to execute...and there are only 100G available...sigh). However for these semi ad-hoc systems it is hard to prevent dumb queries altogether! regards Mark starjoin.tar.gz Description: GNU Zip compressed data -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5980: Installation can't running
The following bug has been logged online: Bug reference: 5980 Logged by: M709199 Email address: m709...@yandex.ru PostgreSQL version: 9.0.3-2 Operating system: Windows 7 Description:Installation can't running Details: account is an administrator UAC is OFF Firewall is OFF Antivir is OFF postgresql-9.0.3-2-windows.exe Run as administrator and I see this http://i033.radikal.ru/1104/e7/808a9036f736.jpg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
