Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-20824

ezoracle: db driver - $db->generateSQLINStatement - not working correctly for items > 1000

    Details

      Description

      The restriction for oracle database, which only allows a total amount of 1000 elements in an IN statement is generating a where clause that is not safely encapsulated in braces *( ... )*

      In consequence, when the return value of this method is combined in other *and* clauses on query construction (such as the one in kernel/classes/notification/handler/ezsubtree/ezsubtreenotificationrule.php) the result set won't be the desired one.

      Steps to reproduce:
      • On an environment with eZOracle and over 1000 user accounts, all of them set up to be notified on changes to an object,
      • perform the changes
      • run the notification cronjob (active in *frequent*, by default)
      • The outcome may vary into a fatal error, if your data is large enough to provoke it or an undesired list of users being notified.

        Activity

        Joaquim Cavalleri (Inactive) created issue -
        Joaquim Cavalleri (Inactive) made changes -
        Field Original Value New Value
        Status Open [ 1 ] Backlog [ 10000 ]
        Joaquim Cavalleri (Inactive) made changes -
        Description The restriction for oracle database, which only allows a total amount of 1000 elements in an IN statement is generating a where clause that is not safely encapsulated in braces **( ... )**

        In consequence, when the return value of this method is combined in other **and** clauses on query construction (such as the one in kernel/classes/notification/handler/ezsubtree/ezsubtreenotificationrule.php) the result set won't be the desired one.

        h5.Steps to reproduce:
        * On an environment with eZOracle and over 1000 user accounts, all of them set up to be notified on changes to an object,
        * perform the changes
        * run the notification cronjob (active in **frequent**, by default)
         * The outcome may vary into a fatal error, if your data is large enough to produce or an undesired list of users being notified.
        The restriction for oracle database, which only allows a total amount of 1000 elements in an IN statement is generating a where clause that is not safely encapsulated in braces **( ... )**

        In consequence, when the return value of this method is combined in other **and** clauses on query construction (such as the one in kernel/classes/notification/handler/ezsubtree/ezsubtreenotificationrule.php) the result set won't be the desired one.

        h5.Steps to reproduce:
        * On an environment with eZOracle and over 1000 user accounts, all of them set up to be notified on changes to an object,
        * perform the changes
        * run the notification cronjob (active in **frequent**, by default)
         * The outcome may vary into a fatal error, if your data is large enough to provoke it or an undesired list of users being notified.
        Hide
        Felix Woldt added a comment - - edited

        An outher example. If you create a custom sql query with an instatement

        $db = eZDB::instance();
        $idList = array( 1,2,3,4 .... 2000 );
        $sqlINString = $db->generateSQLINStatement( $idList, ' table_name.id', false, false, 'int' );

        Select * From table_name
        where $sqlINString
        AND table_name.id < 900;

        => wrong result

        Select * From table_name
        where table_name.id IN (1...,1000) OR table_name.id IN ( 1001, ...2000 )
        AND table_name.id < 900;

        after patch => correct result:

        Select * From table_name
        where ( table_name.id IN (1...,1000) OR table_name.id IN ( 1001, ...2000 ) )
        AND table_name.id < 900;

        To find this out took me some hours

        Cheers Felix

        Show
        Felix Woldt added a comment - - edited An outher example. If you create a custom sql query with an instatement $db = eZDB::instance(); $idList = array( 1,2,3,4 .... 2000 ); $sqlINString = $db->generateSQLINStatement( $idList, ' table_name.id', false, false, 'int' ); Select * From table_name where $sqlINString AND table_name.id < 900; => wrong result Select * From table_name where table_name.id IN (1...,1000) OR table_name.id IN ( 1001, ...2000 ) AND table_name.id < 900; after patch => correct result: Select * From table_name where ( table_name.id IN (1...,1000) OR table_name.id IN ( 1001, ...2000 ) ) AND table_name.id < 900; To find this out took me some hours Cheers Felix
        Gunnstein Lye made changes -
        Fix Version/s Customer request [ 11018 ]
        Gunnstein Lye made changes -
        Status Backlog [ 10000 ] InputQ [ 10001 ]
        Yannick Roger (Inactive) made changes -
        Assignee Yannick Roger [ yannick.roger@ez.no ]
        Yannick Roger (Inactive) made changes -
        Status InputQ [ 10001 ] Development [ 3 ]
        Show
        Yannick Roger (Inactive) added a comment - I merged the pull request : https://github.com/ezsystems/ezoracle/commit/3fff95005cf911cef7d3a2c4d151115ed06fb1c8
        Yannick Roger (Inactive) made changes -
        Status Development [ 3 ] Devlopment done [ 5 ]
        Yannick Roger (Inactive) made changes -
        Status Devlopment done [ 5 ] Documentation done [ 10011 ]
        Pedro Resende (Inactive) made changes -
        Status Documentation done [ 10011 ] QA [ 10008 ]
        Assignee Yannick Roger [ yannick.roger@ez.no ] Pedro Resende [ pedro.resende@ez.no ]
        Pedro Resende (Inactive) made changes -
        Status QA [ 10008 ] InputQ [ 10001 ]
        Assignee Pedro Resende [ pedro.resende@ez.no ]
        Yannick Roger (Inactive) made changes -
        Comment [ . ]
        Yannick Roger (Inactive) made changes -
        Status InputQ [ 10001 ] Development [ 3 ]
        Assignee Yannick Roger [ yannick.roger@ez.no ]
        Show
        Yannick Roger (Inactive) added a comment - ezoracle 5.0: https://github.com/ezsystems/ezoracle/commit/efdc116d98bdadc91505428402fd129c8c617859 ezoracle 2.4: https://github.com/ezsystems/ezoracle/commit/ad835853dcc464f38ec8ec02bb6a0db6a3f022b4
        Yannick Roger (Inactive) made changes -
        Status Development [ 3 ] Devlopment done [ 5 ]
        Yannick Roger (Inactive) made changes -
        Status Devlopment done [ 5 ] Documentation done [ 10011 ]
        Pedro Resende (Inactive) made changes -
        Status Documentation done [ 10011 ] QA [ 10008 ]
        Assignee Yannick Roger [ yannick.roger@ez.no ] Pedro Resende [ pedro.resende@ez.no ]
        Pedro Resende (Inactive) made changes -
        Remaining Estimate 0 minutes [ 0 ]
        Time Spent 1 hour [ 3600 ]
        Worklog Id 34656 [ 34656 ]
        Pedro Resende (Inactive) logged work - 30/May/13 7:07 PM
        • Time Spent:
          1 hour
           

          Analyse and prepare environment to reproduce issue

        Pedro Resende (Inactive) made changes -
        Rank Ranked higher
        Pedro Resende (Inactive) made changes -
        Status QA [ 10008 ] QA done [ 10007 ]
        André Rømcke made changes -
        Assignee Pedro Resende [ pedro.resende@ez.no ]
        Status QA done [ 10007 ] Closed [ 6 ]
        Fix Version/s 5.0-SP [ 11287 ]
        Resolution Fixed [ 1 ]
        André Rømcke made changes -
        Workflow eZ Engineering Scrumban Workflow [ 53668 ] EZ* Development Workflow [ 83779 ]
        Alex Schuster made changes -
        Workflow EZ* Development Workflow [ 83779 ] EZEE Development Workflow [ 122395 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Backlog Backlog
        2m 17s 1 joaquim.cavalleri@ez.no 06/May/13 12:43 PM
        Backlog Backlog InputQ InputQ
        8d 22h 45m 1 Gunnstein Lye 15/May/13 11:29 AM
        QA QA InputQ InputQ
        49s 1 pedro.resende@ez.no 29/May/13 12:43 PM
        InputQ InputQ Development Development
        3d 4h 42m 2 yannick.roger@ez.no 30/May/13 10:19 AM
        Development Development Development Done Development Done
        3d 20h 11m 2 yannick.roger@ez.no 30/May/13 10:20 AM
        Development Done Development Done Documentation Review done Documentation Review done
        26s 2 yannick.roger@ez.no 30/May/13 10:20 AM
        Documentation Review done Documentation Review done QA QA
        8d 3h 16m 2 pedro.resende@ez.no 30/May/13 3:40 PM
        QA QA QA Done QA Done
        3d 17h 34m 1 pedro.resende@ez.no 03/Jun/13 9:15 AM
        QA Done QA Done Closed Closed
        7d 9h 8m 1 André Rømcke 10/Jun/13 6:24 PM

          People

          • Assignee:
            Unassigned
            Reporter:
            Joaquim Cavalleri (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - Not Specified
              Not Specified
              Remaining:
              Remaining Estimate - 0 minutes
              0m
              Logged:
              Time Spent - 1 hour
              1h