Home » SQL & PL/SQL » SQL & PL/SQL » Two MAX Functions in a Single SELECT (Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0)  () 1 Vote
Two MAX Functions in a Single SELECT [message #685398] Thu, 23 December 2021 18:01 Go to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
If I run the following code:
SELECT WO.fldWoID, WO.fldUnitNo
FROM usrWo.tblWoMstOrderMaster WO
WHERE WO.fldWoID = 1850230;
I see this:
1850230, NULL

But if I run this:
SELECT MAX(WO.fldWoID), MAX(WO.fldUnitNo)
FROM usrWo.tblWoMstOrderMaster WO
WHERE WO.fldWoID = 1850230;
I see this:
NULL, NULL

I would have expected 1850230, NULL for the second query.

fldWoID is NUMBER(11), and fldUnitNo is VARCHAR2(20).

Can anyone explain why?

Thanks.

Wayne
Re: Two MAX Functions in a Single SELECT [message #685400 is a reply to message #685398] Fri, 24 December 2021 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 09 March 2020 19:03

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
SQL> create table t (c1 number, c2 number);

Table created.

SQL> insert into t values(1850230, NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> select c1, c2 from t where c1=1850230;
        C1         C2
---------- ----------
   1850230

1 row selected.

SQL> select max(c1), max(c2) from t where c1=1850230;
   MAX(C1)    MAX(C2)
---------- ----------
   1850230

1 row selected.
Check if you have any VPD/RLS policy on the table.

Re: Two MAX Functions in a Single SELECT [message #685403 is a reply to message #685400] Fri, 24 December 2021 11:03 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
The anomaly arises after adding an index.

Here's how to reproduce the problem:

CREATE TABLE TestMax(
nTestMaxID NUMBER, cTestName VARCHAR2(255));

CREATE INDEX IDXTestMax ON TestMax
(nTestMaxID, cTestName);

INSERT INTO TestMax(nTestMaxID, cTestName)
VALUES (1, NULL);

COMMIT;

SELECT nTestMaxID, cTestName FROM TestMax WHERE nTestMaxID = 1;

SELECT MAX(nTestMaxID)
     , MAX(cTestName)
FROM TestMax
WHERE nTestMaxID = 1;

Result:  NULL, NULL

But if you now drop the index, the results will change:

DROP INDEX IDXTestMax;

SELECT MAX(nTestMaxID)
     , MAX(cTestName)
FROM TestMax
WHERE nTestMaxID = 1;

Result:  1, NULL

Why should an index change the result set?

Wayne
icon4.gif  Re: Two MAX Functions in a Single SELECT [message #685404 is a reply to message #685403] Fri, 24 December 2021 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know, this smells a bug, it can be reproduced in all versions I tested from 11gR2 (11.2.0.4.181016) to 21c (21.3.0.0.0).

The following is correct:
SQL> col MAX(CTESTNAME) format a30
SQL> set null '<null>'
SQL> SELECT MAX(nTestMaxID) FROM TestMax WHERE nTestMaxID = 1;
MAX(NTESTMAXID)
---------------
              1

1 row selected.

SQL> SELECT MAX(cTestName) FROM TestMax WHERE nTestMaxID = 1;
MAX(CTESTNAME)
------------------------------
<null>

1 row selected.
but not:
SQL> SELECT MAX(cTestName), MAX(nTestMaxID) FROM TestMax WHERE nTestMaxID = 1;
MAX(CTESTNAME)                 MAX(NTESTMAXID)
------------------------------ ---------------
<null>                         <null>

1 row selected.
/forum/fa/1620/0/
and this one gives the correct result:
SQL> SELECT MAX(nTestMaxID), MAX(cTestName) FROM TestMax WHERE nTestMaxID = 1 group by nTestMaxID;
MAX(NTESTMAXID) MAX(CTESTNAME)
--------------- ------------------------------
              1 <null>

1 row selected.
/forum/fa/917/0/

A clue: compare the execution plan of both statements:
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |   142 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |            |      1 |   142 |            |          |
|   2 |   FIRST ROW                  |            |      1 |   142 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDXTESTMAX |      1 |   142 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id  | Operation            | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |   142 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|            |      1 |   142 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | IDXTESTMAX |      1 |   142 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
So it seems to me there is a bug in the "INDEX RANGE SCAN (MIN/MAX)" operation.

You have know a workaround: add a dummy "GROUP BY":
SQL> SELECT MAX(nTestMaxID), MAX(cTestName) FROM TestMax WHERE nTestMaxID = 1 group by null;
MAX(NTESTMAXID) MAX(CTESTNAME)
--------------- ------------------------------
              1 <null>

1 row selected.
If you raise a SR, please post us the result.


Re: Two MAX Functions in a Single SELECT [message #685405 is a reply to message #685404] Sat, 25 December 2021 08:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I put it into MOS, and it looks like these
Bug 9155467 - Wrong results with MIN/MAX functions (Doc ID 9155467.Cool
Bug 12888949 - wrong results with min/max optimization (Doc ID 12888949.Cool
which is supposed to be fixed. If you mention them in your TAR, it might speed things up a bit.

I did find (not a lot to do today...) that if I set optimizer_features_enable='9.2.0.8' I get the INDEX RANGE SCAN (MIN/MAX) exec plan with the correct result. It goes wrong at the next level, 10.1.0. So I guess 10.x is when they introduced the (clearly not fixed) bug.
Re: Two MAX Functions in a Single SELECT [message #685410 is a reply to message #685405] Tue, 28 December 2021 16:34 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
In the past, when I've wanted to capture values into local variables, I've used MAX and INTO to avoid testing for error ORA-01403 ("no data found"). Now I'll have to re-evaluate that practice.

If I prepare the table as before, with an index:
CREATE TABLE TestMax
(
   nTestMaxID    NUMBER
 , cTestName     VARCHAR2(255)
);

CREATE INDEX IDXTestMax
   ON TestMax(nTestMaxID
            , cTestName);

INSERT INTO TestMax(nTestMaxID
                  , cTestName)
VALUES (1
      , NULL);

COMMIT;

I can run this successfully:
SET SERVEROUTPUT ON 
DECLARE
   cMaxTestName    TestMax.cTestName%TYPE;
BEGIN
   SELECT MAX(cTestName)
   INTO cMaxTestName
   FROM TestMax
   WHERE nTestMaxID = 1;
   
   DBMS_OUTPUT.PUT_LINE('cMaxTestName:  ' || cMaxTestName);
END;
Output:
cMaxTestName:


Even if the WHERE clause hits no records, it still stores a NULL in cMaxTestName:
SET SERVEROUTPUT ON 
DECLARE
   cMaxTestName    TestMax.cTestName%TYPE;
BEGIN
   SELECT MAX(cTestName)
   INTO cMaxTestName
   FROM TestMax
   WHERE nTestMaxID = 2;
   
   DBMS_OUTPUT.PUT_LINE('cMaxTestName:  ' || cMaxTestName);
END;
Output:
cMaxTestName:


However, if I want to store two such MAX values using a single SELECT, and if I use a dummy GROUP BY as you suggested, it works only when the WHERE clause has a record to aggregate:
SET SERVEROUTPUT ON 
DECLARE
   nMaxTestMaxID   TestMax.nTestMaxID%TYPE;
   cMaxTestName    TestMax.cTestName%TYPE;
BEGIN
   SELECT MAX(nTestMaxID)
        , MAX(cTestName)
   INTO nMaxTestMaxID
      , cMaxTestName
   FROM TestMax
   WHERE nTestMaxID = 1
   GROUP BY NULL;
   
   DBMS_OUTPUT.PUT_LINE('nMaxTestMaxID:  ' || nMaxTestMaxID);
   DBMS_OUTPUT.PUT_LINE('cMaxTestName:  ' ||cMaxTestName);
END;
Output:
nMaxTestMaxID: 1
cMaxTestName:


However, if the WHERE clause has no record to aggregate, I'll get error ORA-01403 ("no data found") with the GROUP BY clause:
SET SERVEROUTPUT ON 
DECLARE
   nMaxTestMaxID   TestMax.nTestMaxID%TYPE;
   cMaxTestName    TestMax.cTestName%TYPE;
BEGIN
   SELECT MAX(nTestMaxID)
        , MAX(cTestName)
   INTO nMaxTestMaxID
      , cMaxTestName
   FROM TestMax
   WHERE nTestMaxID = 2
   GROUP BY NULL;
   
   DBMS_OUTPUT.PUT_LINE('nMaxTestMaxID:  ' || nMaxTestMaxID);
   DBMS_OUTPUT.PUT_LINE('cMaxTestName:  ' ||cMaxTestName);
END;

Question: Is there another workaround that would allow me to SELECT multiple values INTO local variables, even if the WHERE clause has no records to aggregate (and would then store NULL values in the locals)? If not, I guess I'll have to run two SELECTs: one to identify whether the WHERE clause hits a record and another to run only if the first SELECT returned a value.

Thanks.

Wayne
Re: Two MAX Functions in a Single SELECT [message #685411 is a reply to message #685410] Wed, 29 December 2021 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you trap the "no_data_found" exception? This is the correct way to do.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     nMaxTestMaxID   TestMax.nTestMaxID%TYPE;
  3     cMaxTestName    TestMax.cTestName%TYPE;
  4  BEGIN
  5    BEGIN
  6     SELECT MAX(nTestMaxID)
  7          , MAX(cTestName)
  8     INTO nMaxTestMaxID
  9        , cMaxTestName
 10     FROM TestMax
 11     WHERE nTestMaxID = 2
 12     GROUP BY NULL;
 13    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
 14    END;
 15
 16     DBMS_OUTPUT.PUT_LINE('nMaxTestMaxID:  ' || nMaxTestMaxID);
 17     DBMS_OUTPUT.PUT_LINE('cMaxTestName:  ' ||cMaxTestName);
 18  END;
 19  /
nMaxTestMaxID:
cMaxTestName:

PL/SQL procedure successfully completed.
Re: Two MAX Functions in a Single SELECT [message #685413 is a reply to message #685411] Thu, 30 December 2021 09:34 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
I suppose I'll have to do that.

Thanks.

Wayne
Re: Two MAX Functions in a Single SELECT [message #685423 is a reply to message #685398] Tue, 04 January 2022 02:57 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
This is one very useful post. I suggest adding a feature to allow users to save a certain post to their favorite list for future review.

Thanks,
Ferro
Re: Two MAX Functions in a Single SELECT [message #685424 is a reply to message #685423] Tue, 04 January 2022 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Already done: at the top of each topic you have a link "Bookmark topic", you can then retrieve your bookmarked topics in your control panel (link at top of each page), tab "Bookmarks".

Re: Two MAX Functions in a Single SELECT [message #685425 is a reply to message #685424] Tue, 04 January 2022 04:29 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks Michel
Re: Two MAX Functions in a Single SELECT [message #685427 is a reply to message #685425] Tue, 04 January 2022 09:04 Go to previous messageGo to next message
whdyck
Messages: 25
Registered: May 2017
Junior Member
I might add that I'm almost always selecting values into variables where the WHERE clause filters on the table's PK, which ensures no more than one row returned. If not, trapping the TOO_MANY_ROWS exception might also be necessary.

It's disappointing that using the MAX function doesn't always work, since it would be a nice shorthand way of doing this (when I know that there's no more than one row returned). Using Michel's suggestion, I'm now also setting the local variables to NULL in the EXCEPTION block to ensure the same result as I intended using the MAX function.

Wayne
Re: Two MAX Functions in a Single SELECT [message #685429 is a reply to message #685427] Wed, 05 January 2022 07:33 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
To me it sounds like you run the risk of hiding errors if you (have to) use max if you only want to return a single value. Without knowing the details of your specific situation, it sounds like slapping distinct on every query, rather then fixing the query, fixing the datamodel or fixing the data.
Initially you mentioned that you do it to not having to catch no_data_found, however with Michel's approach you now have both. Might as well lose the max then.
Just my opinion though.
Previous Topic: Oracle porting a natural join
Next Topic: Query Performance
Goto Forum:
  


Current Time: Thu Mar 28 23:42:07 CDT 2024