8 Replies Latest reply: Mar 17, 2011 10:42 AM by chrismarx RSS

    arithmetic subtraction fails in 10g, works in 11g

    chrismarx
      With this setup:

      create table person (id number, version number, groupscount number);

      create table groupfollower (followerid number, followingid number);

      insert into person values (1,1,0);
      insert into person values (2,1,0);
      insert into groupfollower values (1,2);


      UPDATE Person
      SET version =version+1,
      groupsCount=
      *(SELECT COUNT(followingg1_.followerId)*
      FROM GroupFollower followingg1_
      WHERE Person.id=followingg1_.followerId
      *)-1*
      WHERE id IN
      (SELECT groupfollo2_.followerId
      FROM GroupFollower groupfollo2_
      WHERE groupfollo2_.followingId=2
      )

      10g produces an error -- ora-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" Cause: Action: Error at Line:16 Column:4 Line 16 is the ")-1"

      should this not be valid? The query actually works just fine you put the 1- in front, but these queries are being generated by hibernate, and I'm not sure how to override this behavior-
        • 1. Re: arithmetic subtraction fails in 10g, works in 11g
          TPD-Opitz
          Why don't doing aretmethict within the subselect:
          UPDATE Person
          SET version =version+1,
          groupsCount=
          (SELECT COUNT(followingg1_.followerId) -1
          FROM GroupFollower followingg1_
          WHERE Person.id=followingg1_.followerId
          )
          WHERE id IN
          (SELECT groupfollo2_.followerId
          FROM GroupFollower groupfollo2_
          WHERE groupfollo2_.followingId=2
          )
          bye
          TPD
          • 2. Re: arithmetic subtraction fails in 10g, works in 11g
            BluShadow
            What is 10g? That's just a package of Oracle products.

            Perhaps you could actually specify your Oracle Database version for us.. e.g. 10.2.0.1
            • 3. Re: arithmetic subtraction fails in 10g, works in 11g
              BluShadow
              T.PD wrote:
              Why don't doing aretmethict within the subselect:
              UPDATE Person
              Because he's using hibersh!te. The product that's incapable of producing logical SQL.
              • 4. Re: arithmetic subtraction fails in 10g, works in 11g
                Solomon Yakobson
                It is a known issue in 10g. Put constans an the beginning of subquery expression:
                SQL> UPDATE Person
                  2  SET version =version+1,
                  3  groupsCount=
                  4  (SELECT COUNT(followingg1_.followerId)
                  5  FROM GroupFollower followingg1_
                  6  WHERE Person.id=followingg1_.followerId
                  7  )-1
                  8  WHERE id IN
                  9  (SELECT groupfollo2_.followerId
                 10  FROM GroupFollower groupfollo2_
                 11  WHERE groupfollo2_.followingId=2
                 12  )
                 13  
                 14  
                 15  /
                )-1
                 *
                ERROR at line 7:
                ORA-00933: SQL command not properly ended
                
                SQL> UPDATE Person
                  2  SET version =version+1,
                  3  groupsCount= -1 +
                  4  (SELECT COUNT(followingg1_.followerId)
                  5  FROM GroupFollower followingg1_
                  6  WHERE Person.id=followingg1_.followerId)
                  7  WHERE id IN
                  8  (SELECT groupfollo2_.followerId
                  9  FROM GroupFollower groupfollo2_
                 10  WHERE groupfollo2_.followingId=2
                 11  )
                 12  /
                
                1 row updated.
                
                SQL> 
                SY.
                • 5. Re: arithmetic subtraction fails in 10g, works in 11g
                  Ganesh Srivatsav
                  You can use a subquery or directly do an arithematic withing the select as T.PD said.
                  UPDATE Person
                     SET VERSION = VERSION + 1,
                         groupsCount =
                           (SELECT cnt - 1
                              FROM (SELECT COUNT (followingg1_.followerId) cnt
                                      FROM GroupFollower followingg1_
                                     WHERE Person.ID = followingg1_.followerId))
                   WHERE ID IN (SELECT groupfollo2_.followerId
                                  FROM GroupFollower groupfollo2_
                                 WHERE groupfollo2_.followingId = 2)
                  • 6. Re: arithmetic subtraction fails in 10g, works in 11g
                    chrismarx
                    wow,
                    thanks for all the quick responses, yes, since I'm using hibernate I'd need to break away from using db agnostic code in order to implement this specific fix. The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

                    If this is a known bug, is there a patch for it?
                    • 7. Re: arithmetic subtraction fails in 10g, works in 11g
                      BluShadow
                      user551908 wrote:
                      wow,
                      thanks for all the quick responses, yes, since I'm using hibernate I'd need to break away from using db agnostic code in order to implement this specific fix. The version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

                      If this is a known bug, is there a patch for it?
                      LOL, I think 11g was the patch. ;)
                      • 8. Re: arithmetic subtraction fails in 10g, works in 11g
                        chrismarx
                        ok, thanks for the bug confirmation, I think I found where this being generated, moving the subtraction to the front of the query works!