虫子或愚蠢 [英] Bug or stupidity
问题描述
您好，
我想，我发现了一个错误，但也许这只是我的愚蠢。当然：我做的是什么？b $ b我的错误，但我仍然认为，PostgreSQL不应该这样做。
做它做的事情。
我已经创建了一个简单的测试用例：
popscan_light =>创建没有oids的表a（id serial，name varchar（10），primary
key（id））;
注意：CREATE TABLE将创建隐式序列a_id_seq for
" serial"列a.id
注意：CREATE TABLE / PRIMARY KEY将为表格a创建隐式索引a_pkey
创建表
popscan_light => create table b（id int4引用a（id）on delete
cascade，name2 varchar（15），primary key（id））without oids;
注意：创建表/ PRIMARY KEY将为表格b创建隐式索引b_pkey
创建表
popscan_light =>插入（名称）值（''gnegg''）;
INSERT 0 1
popscan_light =>插入（名称）值（''blepp''）;
INSERT 0 1
popscan_light =>插入b值（1，''gnegglink''）;
INSERT 0 1
popscan_light =>插入b值（2，''blepplink''）;
INSERT 0 1
popscan_light =>从左边连接b中选择a.name，b.name2使用（id）
按b.name2顺序选择;
name | name2
------- + -----------
blepp | blepplink
gnegg | gnegglink
（2行）
popscan_light =>选择aliasa.name，aliasb.name2来自aliasa left join
b aliasb using（id）order by b.name2;
注意：为表添加缺少的FROM子句条目" b"
name | name2
------- + -----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
（4行）
popscan_light =>从aliasa中选择aliasa.name，aliasb.name2加入
b aliasb使用（id）命令aliasb.name2;
name | name2
------- + -----------
blepp | blepplink
gnegg | gnegglink
（2行）
popscan_light => \ q
fangorn~ $ psql --version
psql（PostgreSQL）7.4.3
包含对命令行编辑的支持
在第二个SELECT - -Query中，我已按第二个表的
name-column命令结果集，但我还没有用过别名aliasb
我创建了，但我使用了完整的表名。我知道这不是真的好吗，但我还是想知道为什么Postgres会向我抛出4个结果。
如果我使用正确的在order by子句中的列，我得到了正确的
加入的结果。
查看我的第二个查询，我认为错误的按顺序排列似乎在没有适当条件的情况下加入表b的另一个副本中拉出
。我不认为这是正确的事情。
或者它是什么？
任何人？
菲利普
---------------------- -----（广播结束）---------------------------
提示9：规划师如果您的
加入列的数据类型不匹配，将忽略您选择索引扫描的愿望
psql --version
psql（PostgreSQL）7.4.3
包含对命令行编辑的支持
In第二个SELECT - 查询我已经按照第二个表的
name-column命令了结果集，但是我没有使用别名aliasb
我创建了，但我使用了完整的表名。我知道这不是真的好吗，但我还是想知道为什么Postgres会向我抛出4个结果。
如果我使用正确的在order by子句中的列，我得到了正确的
加入的结果。
查看我的第二个查询，我认为错误的按顺序排列似乎在没有适当条件的情况下加入表b的另一个副本中拉出
。我不认为这是正确的事情。
或者它是什么？
任何人？
菲利普
---------------------- -----（广播结束）---------------------------
提示9：规划师如果您的
加入列的数据类型不匹配，将忽略您选择索引扫描的愿望
周六， 2004年10月23日02:17:16 PM +0000，Philip Hofstetter写道：你好，
我想，我发现了一个bug，但也许它只是我的愚蠢。当然：我做了什么是我的错误，但我仍然认为，PostgreSQL不应该做它做的事情。
.... snip ...
popscan_light =>选择aliasa.name，aliasb.name2来自aliasa left join
b aliasb using（id）order by b.name2;
注意：为表添加缺少的FROM子句条目" b"
名称| name2
------- + -----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
（4行）
看到NOTIVCE？它告诉你它已将你的查询转换为：
从b中选择aliasa.name，aliasb.name2，aliasa left join
b aliasb使用（id）命令b.name2;
既然你现在在B表上有一个无约束的连接，那你得到两次
尽可能多的行。
它基本上归结为，如果你做了一个别名，你必须使用
别名。您不能使用原始表名*和*别名。对原始表的
引用将成为同一个
表的另一个副本。
至于什么是SQL标准，我认为通过严格的定义你的查询
根本不应该被允许，引用一个未定义的表。
希望这有帮助，
-
Martijn van Oosterhout< kl ***** @ svana.org> http://svana.org/kleptog/ 专利。 ñ。天才是5％的灵感和95％的汗水。专利是一种工具，用于完成5％的工作，然后坐在那里等待某人其他95％，这样你就可以起诉他们。
----- BEGIN PGP SIGNATURE -----
版本：GnuPG v1.0.6（GNU / Linux）
评论：有关信息请参阅 http://www.gnupg.org
iD8DBQFBemr / Y5Twig3Ge + YRAlIYAKC6DjO0oikssfd + WdC0LIQTlngvxQCeMn tm
jtYktqJ / FuAfqLShKD7Sga0 =
= xKg9
----- END PGP SIGNATURE-- ---
你好，
Martijn van Oosterhout写道：< blockquote class =post_quotes> popscan_light =>选择aliasa.name，aliasb.name2来自aliasa left join
b aliasb using（id）order by b.name2;
注意：为表添加缺少的FROM子句条目" b"
名称| name2
------- + -----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
（4行）
看到NOTIVCE？它告诉你它已经将你的查询转换为：
实际上，我已经监督了它。但是，我在邮件中的假设是
无论如何正确。
从b中选择aliasa.name，aliasb.name2，aliasa left join
b aliasb using （id）按b.name2排序;
既然你现在在B表上有一个无约束的连接，那么你得两次
。
这就是我的想法。
至于什么是SQL标准，我认为严格的定义你的查询不应该''完全允许，引用一个未定义的表。
这正是我的想法。我的意思是：我知道我在
查询中出错了。如果PostgreSQL实际上已经告诉我了，那就更容易找到了（我没有从PHP那里得到那些通知......）。
如果错了，应该禁止，不要因为假设
完全错误而变得更糟。
无论如何，谢谢你的快速反应。
菲利普
------------------------- - （广播结束）---------------------------
提示1：订阅和取消订阅命令 ma*******@postgresql.org
Hello,
I think, I found a bug, but maybe it''s just my stupidity. Granted: What
I did was an error on my part, but I still think, PostgreSQL should not
do what it does.
I''ve already created a simple testcase:
popscan_light=> create table a (id serial, name varchar(10), primary
key(id)) without oids;
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for
"serial" column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"
for table "a"
CREATE TABLE
popscan_light=> create table b (id int4 references a (id) on delete
cascade, name2 varchar(15), primary key (id)) without oids;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey"
for table "b"
CREATE TABLE
popscan_light=> insert into a (name) values (''gnegg'');
INSERT 0 1
popscan_light=> insert into a (name) values (''blepp'');
INSERT 0 1
popscan_light=> insert into b values (1, ''gnegglink'');
INSERT 0 1
popscan_light=> insert into b values (2, ''blepplink'');
INSERT 0 1
popscan_light=> select a.name, b.name2 from a left join b using (id)
order by b.name2;
name | name2
-------+-----------
blepp | blepplink
gnegg | gnegglink
(2 rows)
popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
b aliasb using (id) order by b.name2;
NOTICE: adding missing FROM-clause entry for table "b"
name | name2
-------+-----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
(4 rows)
popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
b aliasb using (id) order by aliasb.name2;
name | name2
-------+-----------
blepp | blepplink
gnegg | gnegglink
(2 rows)
popscan_light=> \q
fangorn ~ $ psql --version
psql (PostgreSQL) 7.4.3
contains support for command-line editing
In the second "SELECT"-Query I''ve ordered the result set by the
name-column of the second table, but I have not used the alias "aliasb"
I created, but I used the full table name. I know this is not really
correct, but I''d still like to know why Postgres throws 4 results at me.
If I use the correct column in the order by clause, I get the correctly
joined result.
Looking at my second query, I think the false "order by" seems to pull
in another copy of table b joining it without a proper condition. I
don''t think, this is the right thing to do.
Or ist it?
Anyone?
Philip
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
psql --version
psql (PostgreSQL) 7.4.3
contains support for command-line editing
In the second "SELECT"-Query I''ve ordered the result set by the
name-column of the second table, but I have not used the alias "aliasb"
I created, but I used the full table name. I know this is not really
correct, but I''d still like to know why Postgres throws 4 results at me.
If I use the correct column in the order by clause, I get the correctly
joined result.
Looking at my second query, I think the false "order by" seems to pull
in another copy of table b joining it without a proper condition. I
don''t think, this is the right thing to do.
Or ist it?
Anyone?
Philip
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
On Sat, Oct 23, 2004 at 02:17:16PM +0000, Philip Hofstetter wrote:Hello,
I think, I found a bug, but maybe it''s just my stupidity. Granted: What
I did was an error on my part, but I still think, PostgreSQL should not
do what it does.
.... snip ...
popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
b aliasb using (id) order by b.name2;
NOTICE: adding missing FROM-clause entry for table "b"
name | name2
-------+-----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
(4 rows)
See that NOTIVCE? It''s telling you that it''s converted your query to:
select aliasa.name, aliasb.name2 from b, a aliasa left join
b aliasb using (id) order by b.name2;
Since you now have an unconstrained join on the B table, you get twice
as many rows.
It basically comes down to, if you make an alias, you have to use the
alias. You can''t use the original table name *and* the alias. The
reference to the original table is becomes another copy of the same
table.
As for what''s SQL standard, I think by a strict definition your query
shouldn''t be allowed at all, referencing an undefined table.
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBemr/Y5Twig3Ge+YRAlIYAKC6DjO0oikssfd+WdC0LIQTlngvxQCeMn tm
jtYktqJ/FuAfqLShKD7Sga0=
=xKg9
-----END PGP SIGNATURE-----
Hi,
Martijn van Oosterhout wrote:popscan_light=> select aliasa.name, aliasb.name2 from a aliasa left join
b aliasb using (id) order by b.name2;
NOTICE: adding missing FROM-clause entry for table "b"
name | name2
-------+-----------
gnegg | gnegglink
blepp | blepplink
gnegg | gnegglink
blepp | blepplink
(4 rows)
See that NOTIVCE? It''s telling you that it''s converted your query to:
actually, I''ve overseen it. But then, my assumption in my mail was
correct anyway.
select aliasa.name, aliasb.name2 from b, a aliasa left join
b aliasb using (id) order by b.name2; Since you now have an unconstrained join on the B table, you get twice
as many rows.
This is what I thought.
As for what''s SQL standard, I think by a strict definition your query
shouldn''t be allowed at all, referencing an undefined table.
This is exactly what I think too. I mean: I know I made an error in my
query. It would just have been easier to find if PostgreSQL actually had
told me so (I''m not getting those NOTICEs from PHP...).
If it''s wrong, it should be disallowed, not made worse by assuming a
completely wrong thing.
Thanks for your fast response anyway.
Philip
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
这篇关于虫子或愚蠢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
