Oracle Merge statement syntax check is not strict

The test found that even in 11.2, the problem still existed:

Sql> SELECT * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

sql> CREATE TABLE T1

2 (ID number,

3 NAME VARCHAR2 (30));

Table has been created.

sql> CREATE TABLE T2

2 (ID number,

3 NAME VARCHAR2 (30));

Table has been created.

Sql> INSERT into T1 VALUES (1, ' A ');

1 lines have been created.

Sql> INSERT into T1 VALUES (2, ' A ');

1 lines have been created.

Sql> INSERT into T2 VALUES (1, ' B ');

1 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> MERGE into T2

2 USING (SELECT ID, NAME from T1) T1

3 on (t1.id = t2.id)

4 when matched THEN UPDATE

5 SET t2.name = T2. T1.name

6 when not matched THEN INSERT

7 VALUES (T1.id, t1.name);

2 lines have been merged.

Sql> SELECT * from T2;

ID NAME

---------- ------------------------------

1 A

2 A

In the update set syntax for the merge, T2. T1. Name This error is also received, and the T2 prefix is ignored. This does not result in an error if T2 is not replaced by any other character:

This column more highlights: http://www.bianceng.cn/database/Oracle/

Sql> MERGE into T2

2 USING (SELECT ID, NAME from T1) T1

3 on (t1.id = t2.id)

4 when matched THEN UPDATE

5 SET t2.name = BDW. T1.name

6 when not matched THEN INSERT

7 VALUES (T1.id, t1.name);

2 lines have been merged.

Sql> MERGE into T2

2 USING (SELECT ID, NAME from T1) T1

3 on (t1.id = t2.id)

4 when matched THEN UPDATE

5 SET t2.name = SYS. T1.name

6 when not matched THEN INSERT

7 VALUES (T1.id, t1.name);

2 lines have been merged.

Obviously in a select or UPDATE statement, such a writing cannot be checked by a grammar:

Sql> SELECT T2. T1.name

2 from T1, T2

3 WHERE t1.id = t2.id;

SELECT T2. T1.name

*

Line 1th Error:

ORA-00904: "T2". T1 "." NAME ': Invalid identifier

Sql> UPDATE T2

2 SET NAME =

3 (

4 SELECT T2. T1.name

5 from T1

6 WHERE t1.id = t2.id

7);

SELECT T2. T1.name

*

Line 4th Error:

ORA-00904: "T2". T1 "." NAME ': Invalid identifier

The more new characteristics of the bug is more, although the merge syntax in the 9i appeared, but compared or used less, less people, experienced a variety of extreme test less, hidden bugs more likely.