为什么 Oracle SQL 能够神秘地解决一个连接中的歧义性，而不能解决其他连接中的歧义性

发布于 2024-09-09 23:34:33 字数 492 浏览 4 评论 0原文

我是 Oracle 10g 用户。我必须编写一些 SQL 查询，并发现了一个神秘的（正如我所看到的）行为。假设我们有一个表，它能够将自身连接成某种简单的两级树结构。下一个查询给出了“歧义错误”，这是预期的：

select title
  from table1
    left join table1 on condition

但是如果我向联接添加一个表，歧义问题就会消失：

select title
  from table1
    join table2 on other_condition
    left join table1 on condition

对此的解释是什么？我完全想念它......完整的测试用例可以在 http://pastebin.com/webf513w 找到

原文

I'm an Oracle 10g user. I had to write some SQL queries, and spotted a mysterious (as I see it) behaviour. Let's pretend we have a table, which is able to join itself in some kind of simple two-level tree structure. The next query gives me "ambiguity error", which is expected:

select title
  from table1
    left join table1 on condition

BUT if I would add one more table to the join, the ambiguity problem will simply go away:

select title
  from table1
    join table2 on other_condition
    left join table1 on condition

What would be the explanation of this? I miss it completely... The full test case can be found at http://pastebin.com/webf513w

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不打扰别人 2024-09-16 23:34:33

对于第三个查询，Oracle 10g 从第二个 TestTable1（别名 TestTable1_2）返回 field3。这似乎是一个错误，似乎已在 11g 中修复。

测试用例：

INSERT INTO TestTable1 VALUES (1,2,3,NULL);
INSERT INTO TestTable1 VALUES (2,5,6,1);
INSERT INTO TestTable2 VALUES (5,6,7);
INSERT INTO TestTable2 VALUES (2,20,30);

SELECT field3
FROM TestTable1
join TestTable2 ON TestTable1.field1 = TestTable2.field1
left join TestTable1 TestTable1_2 ON TestTable1.self_ref = TestTable1_2.id;

FIELD3
======
3
(null)

SELECT TestTable1.field3, TestTable2.field3, TestTable1_2.field3
FROM TestTable1
join TestTable2 ON TestTable1.field1 = TestTable2.field1
left join TestTable1 TestTable1_2 ON TestTable1.self_ref = TestTable1_2.id;

FIELD3 FIELD3_1 FIELD3_2
====== ======== ========
6      7        3
3      30       (null)

For the third query, Oracle 10g returns field3 from the second TestTable1 (alias TestTable1_2). This appears to be a bug, which seems to have been fixed in 11g.

Testcase:

INSERT INTO TestTable1 VALUES (1,2,3,NULL);
INSERT INTO TestTable1 VALUES (2,5,6,1);
INSERT INTO TestTable2 VALUES (5,6,7);
INSERT INTO TestTable2 VALUES (2,20,30);

SELECT field3
FROM TestTable1
join TestTable2 ON TestTable1.field1 = TestTable2.field1
left join TestTable1 TestTable1_2 ON TestTable1.self_ref = TestTable1_2.id;

FIELD3
======
3
(null)

SELECT TestTable1.field3, TestTable2.field3, TestTable1_2.field3
FROM TestTable1
join TestTable2 ON TestTable1.field1 = TestTable2.field1
left join TestTable1 TestTable1_2 ON TestTable1.self_ref = TestTable1_2.id;

FIELD3 FIELD3_1 FIELD3_2
====== ======== ========
6      7        3
3      30       (null)

回复收藏 0 原文

~没有更多了~