Query Transformation playing with syntactically wrong SQL.

In Oracle whenever any SQL didn’t adhere syntax defined, it is bound to give some error. Any SQL is processed after syntax check is validated.

In current blog will try to walk you through some SQL, that seems incorrect but will processed successfully.

Case 1 :
Whenever we are using aggregation(MIN/MAX/SUM/AVG) and selecting others columns from a table (Not a scalar column) then that column should be defined in Group by clause.

Running below SQL is bound to give an error.

SQL>SELECT MAX(sal) msal, deptno FROM emp WHERE deptno = 10;

ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"

Now lets try to run below SQL.

SQL>SELECT msal FROM
(
SELECT MAX(sal) msal, deptno FROM emp WHERE deptno = 10
);
SQL Executed successfully.

Output :
5000

Surprisingly SQL got executed, but previously run SQL was error out due to “not a single-group group function”.
Since SQL got Executed, Optimizer must have transformed SQL to be executable as a part of Query Transformation.

Lets analyze 10053 trace for above SQL to check transformation.
Final query after transformations:
******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."MSAL" "MSAL"
FROM
(
SELECT MAX("EMP"."SAL") "MSAL"
FROM "C##PERF12C"."EMP" "EMP"
WHERE "EMP"."DEPTNO"=10
) "from$_subquery$_001";

Column “deptno” refer in actual inline select clause is missing in final transform SQL, hence SQL got executed and no Error.

SQL we are trying to run is actually equivalent as if running below SQL.
SELECT MAX(sal) msal FROM emp WHERE deptno = 10

As in final outer select, we are not projecting “deptno” column oracle realized we are not selecting inline projected column so it transform SQL to be executable i.e. ignoring column deptno!!

But practically speaking such code is developer bug and should not be in practice.For above SQL, developer missed to define any association through GROUP BY Clause for column deptno with aggregation function (MAX). Later same was not projected in outer part. Hence, Oracle ignored same and with Query transformation SQL work fine without any error.
Query Transformation behaviour vary from oracle version to version hence for some version it will error out.

If we are selecting all inline columns, then Error will be reported.

SELECT * --selecting all inline column
FROM
(
SELECT MAX(sal) msal, deptno FROM emp WHERE deptno = 10
);
ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"

Later will update one more interesting case.!

Note :
Test Env : 12c version.
Same scenario might not get simulated in Pre 11g version, due to query transformations logic for that version.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s