View Issue Details

IDProjectCategoryView StatusLast Update
0028902mantisbtdb mssqlpublic2021-07-18 23:13
Reporterbzi Assigned Toatrol  
PriorityurgentSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
PlatformSQL SERVER 2016OSWINDOWS SERVEROS Version2016
Product Version2.24.2 
Target Version2.26.0Fixed in Version2.26.0 
Summary0028902: APPLICATION ERROR #401 / Error MSSQL 4145 when view all bugs for 1000 projects or more
Description

When i want to see all bugs from all my projects, i got an error (cf. attached file).
This error occure when there is more than 999 projects. When i check the query send to my database server :

  • with less than 1000 projects : SELECT * FROM mantis_project_version_table WHERE project_id IN (@P1, @P2, @P3, ...
    ==> no error
  • with more than 999 projects : SELECT * FROM mantis_project_version_table WHERE (1, project_id) IN ((1,@P1), (1,@P2), (1,@P3), ...
    ==> error 4145, this query is not supported on TSQL

Why BT is not using the same query pattern ?
How can i fix this error please ?

TagsNo tags attached.

Activities

bzi

bzi

2021-07-09 05:33

reporter  

erreur.png (91,518 bytes)   
erreur.png (91,518 bytes)   
bzi

bzi

2021-07-09 07:17

reporter   ~0065682

Last edited: 2021-07-09 07:19

The problem is from an Oracle limitation in file "/core/classes/DbQuery.class.php" at "sql_in" function :

/**
 * Creates a string construction for an IN expression, providing:
 * - alias: is the name of the column as a valid identifier in the final sql query
 * - a label, or an array of values
 * If a label is provided, the values must be binded as separate calls to binding methods.
 *
 * The returned string would replace the sql part for: "alias IN (x,x,x)"
 *
 * To work around Oracle limit of 1000 elements in IN clauses, using a label
 * is recommended in situations that this number may be reached.
 * This construct will then automatically deal with a compatible syntax fix.
 * Using a label in this scenario is needed because the fix must be implemented
 * with a late binding of the array at execution time (this also allows correctly
 * rebinding the elements for the IN clause)
 *
 * @param string $p_alias           A valid sql column identifier
 * @param mixed $p_label_or_values  Label or values array
 * @return string   Constructed string to be added to query
 */
public function sql_in( $p_alias, $p_label_or_values ) {
    if( is_array( $p_label_or_values ) ) {
        if( count( $p_label_or_values ) > self::$oracle_in_limit ) {
            $t_sql = $this->helper_in_oracle_fix( $p_alias, $p_label_or_values );
        } elseif( count( $p_label_or_values ) == 1 ) {
            $t_sql = $p_alias . ' = ' . $this->param( reset( $p_label_or_values ) );
        } else {
            $t_sql = $p_alias . ' IN ' . $this->param( $p_label_or_values );
        }
    } else {
        # is a label
        # create placeholder for late binding
        $t_new_index = count( $this->late_binding_in_clause );
        $this->late_binding_in_clause[$t_new_index] = array();
        $this->late_binding_in_clause[$t_new_index]['alias'] = $p_alias;
        $this->late_binding_in_clause[$t_new_index]['label'] = $p_label_or_values;
        $t_sql = '$in' . $t_new_index;
    }

    return $t_sql;
}

The constant "$oracle_in_limit" has its value to "1000" at line 155 :
protected static $oracle_in_limit = 1000; # this could be a constant

I've modified the value to 1000000 and this fix my problem with MSSQL.

Suggestion :
You could check the database engine with "$g_db_type" before apply a fix for a spefic engine.

atrol

atrol

2021-07-10 09:26

developer   ~0065684

Thanks @bzi for creating the report and the analysis.

PR https://github.com/mantisbt/mantisbt/pull/1764

@dregad wouldn't it be better if such database special handling is treated in underlying ADOdb?

dregad

dregad

2021-07-14 12:52

developer   ~0065691

wouldn't it be better if such database special handling is treated in underlying ADOdb?

This is somewhat higher-level than what ADOdb currently does, so no small undertaking to implement.
I'm not saying no, but TBH this not something that I currently have time to spend on.

Related Changesets

MantisBT: master cd64d192

2021-07-10 09:20:21

atrol

Details Diff
Remove Oracle special handling for non-Oracle databases

Fixes 0028902
Affected Issues
0028902
mod - core/classes/DbQuery.class.php Diff File