oracle ignores invalid identifier error in subquery(oracle 忽略子查询中的无效标识符错误)
问题描述
我不明白为什么以下查询有效,尽管子查询给出了无效标识符"错误.
I do not understand why the following query works, although the subquery gives an "invalid identifier" error.
SELECT *
FROM aircraft
WHERE airc_manufact IN (SELECT airc_manufact FROM flight);
我的表格如下所示(缩写):
My tables look the following (abbreviated):
AIRCRAFT(airc_model (PK),airc_manufact)
AIRCRAFT (airc_model (PK), airc_manufact)
飞行(flt_no (PK),airc_model (FK))
如果我自己运行子查询,我会收到一个无效标识符"错误,因为 airc_manufact 不是飞行表中的列.
If I run the subquery on its own, then I receive an "invalid identifier" error like it should since the airc_manufact is not a column in the flight table.
如果我运行整个查询,则不会收到错误消息.Oracle 似乎忽略了子查询,因此给了我飞机表中的所有行.
If I run the whole query, then I do not receive an error. Oracle seems to ignore the subquery and thus give me all row in the aircraft table.
对我来说,这似乎是一个错误,因为查询中有一个明显的错误.为什么查询会运行?我的理解是,Oracle 会先运行或评估子查询,然后再运行外部查询.
To me, this seems to be a bug because there is an obvious error in the query. Why does the query run? My understanding is that Oracle would first run or evaluate the subquery, and then run the outer query.
推荐答案
你没有限定你的列名.所以,你认为你在跑步:
You have not qualified your column names. So, you think you are running:
SELECT a.*
FROM aircraft a
WHERE a.airc_manufact IN (SELECT f.airc_manufact FROM flight f);
如果 f.airc_manufact
不存在,则范围规则说要查看外部查询.所以,你真正在运行的是:
If f.airc_manufact
doesn't exist, then the scoping rules say to look in the outer query. So, what you are really running is:
SELECT a.*
FROM aircraft a
WHERE a.airc_manufact IN (SELECT a.airc_manufact FROM flight f);
这作为一个过滤子句非常无用.
That is pretty useless as a filtering clause.
道德:总是限定查询中的列名,特别是当查询引用多个表时.
Moral: Always qualify column names in a query, particularly if the query refers to more than one table.
这篇关于oracle 忽略子查询中的无效标识符错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:oracle 忽略子查询中的无效标识符错误
基础教程推荐
- SQL Server 2016更改对象所有者 2022-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01