6 Replies Latest reply on Jul 24, 2008 5:26 AM by MichaelS

    Coalesce not short-circuiting when sequences used?

    ddevienne
      Given the documented short-circuit semantic of coalesce, I expected the first insert below to not increment the sequence, since the first expression to coalesce was not null, and thus the second expression shouldn't have been evaluated. What am I missing? Isn't s.nextval an expression?

      Thanks for any insights. Best regards, --DD
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SQL> create table t (n number);

      Table created.

      SQL> create sequence s;

      Sequence created.

      SQL> select s.nextval from dual;

         NEXTVAL
      ----------
               1

      SQL> select s.currval from dual;

         CURRVAL
      ----------
               1

      SQL> insert into t values (coalesce(1, s.nextval));

      1 row created.

      SQL> select s.currval from dual;

         CURRVAL
      ----------
               2

      SQL> select * from t;

               N
      ----------
               1

      SQL> insert into t values (coalesce(NULL, s.nextval));

      1 row created.

      SQL> select s.currval from dual;

         CURRVAL
      ----------
               3

      SQL> select * from t;

               N
      ----------
               1
               3
        • 1. Re: Coalesce not short-circuiting when sequences used?
          649102
          Nextval will increment everytime it is referenced in a statement, whether evaluated or not.
          • 2. Re: Coalesce not short-circuiting when sequences used?
            Solomon Yakobson
            Nextval will increment everytime it is referenced in a statement, whether evaluated or not.
            Just to clarify, NEXTVAL will increment once per statement regardless of using short-circuiting or plain expression.

            SY.
            • 3. Re: Coalesce not short-circuiting when sequences used?
              649102
              I guess my answer was a little ambiguous when I said "everytime". Thanks for putting it into better terms.
              • 4. Re: Coalesce not short-circuiting when sequences used?
                ddevienne
                Nextval will increment everytime it is referenced
                in a statement, whether evaluated or not.
                NEXTVAL will increment once per statement regardless of
                using short-circuiting or plain expression.
                Thanks for confirming what we experimentally discovered.

                But I guess my question is why???

                Immediately followed by what other types of expressions defeat short-circuiting in this way?

                Thanks, --DD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                • 5. Re: Coalesce not short-circuiting when sequences used?
                  121256
                  NEXTVAL will increment once per statement regardless of
                  Once per resulting row.
                  But I guess my question is why???
                  Please read about [url http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements6a.htm#80533]How to Use Sequence Values in the manual.
                  • 6. Re: Coalesce not short-circuiting when sequences used?
                    MichaelS
                    Once per resulting row.
                    and as long it is not embedded in a function call...