title: 'parentheses not balanced' regexp error continues to occur - I cannot identify the pattern of failure
body: <p><strong>Originally I thought this problem was a general failure of my understanding or could be generecized to a case that would be useful to others. I still haven't solved the problem, but upon learning more about the boundaries of the problem I see it is probably something highly specific and not of use to the community. Thank you for any and all help and time taken to help.</strong></p>
  
  <hr>
  
  <p><strong>Summary</strong></p>
  
  <p>When I pass the value <code>(Unit-101|00-102|Unit_103|Unit 104)</code> to my stored procedure (as a <code>VARCHAR</code>, to use as comparison in <code>RLIKE</code> in a <code>WHERE</code> clause) - it will generate the error <code>'parentheses not balanced'</code>. However, the stored procedure works perfectly when other values are passed in - three or less in the capture group (e.g. <code>(Unit-101|00-102|Unit_103)</code>), wildcard (<code>.*</code>) or wildcard-component (<code>Unit-1.*</code>) values work perfectly. Also, a very similar stored procedure works perfectly when more than three values are in the capture group... <em>(Please see below for more details. Thank you.)</em></p>
  
  <p>Edit: At least that's what I thought. I tried <code>(a|b|c|d)</code> (I've tried other values before which did not work) - and it worked. So I'm once again at a complete loss. </p>
  
  <hr>
  
  <p><strong>Problem Context</strong></p>
  
  <p>I have a stored procedure which includes some regex in a <code>WHERE</code> clause - it aggregates some totals for entries only where a <code>Unit</code> name matches the regex passed in to the procedure (<code>_r_unit</code>, an <code>IN</code> <code>VARCHAR(100)</code>). </p>
  
  <p>This has worked as expected for many cases of <code>_r_unit</code> - <code>.*</code>, <code>Unit-B.*</code>, <code>Unit-101</code>, etc. - however in one case, when I used parentheses to capture Unit-101, or 00-102, or Unit_103, or Unit 104  - <code>(Unit-101|00-102|Unit_103|Unit 104)</code> - then the query fails with the error <code>#1139 - Got error 'parentheses not balanced' from regexp</code>.</p>
  
  <hr>
  
  <p><strong>Steps attempted to find solution so far</strong> </p>
  
  <ul>
  <li><p><em>I first discovered this problem while passing in the regexp value from php, using <code>preg_quote</code> to escape the <code>-</code> characters in the unit names. However, a commenter helpfully pointed out that the <code>-</code> character should not need to be escaped here, and php will mean it needs to be 'double-escaped' anyway. So, I have tried some php-related things, but this does not appear to be the issue - now that I've eliminated that as the cause I'm just passing in values by hand using phpmyadmin to examine the conditions which cause an error.</em> For reference, the variable which was passed in as a value to the stored procedure from php was set to - </p>
  
  <p><code>"(" . preg_quote("Unit-101") . "|" . preg_quote("00-102") . "|" 
  . preg_quote("Unit_103") . "|" . preg_quote("Unit 104") . ")";</code></p>
  
  <p>The literal contents of the variable (examined by echoing it out) was <code>(Unit\-101|00\-102|Unit_103|Unit 104)</code>, as expected. </p></li>
  <li><p>I have also examined the regex in <a href="https://regex101.com/" rel="nofollow">regex101</a> to check it 'does what it says on the tin' - it's as expected, looking for <code>Unit-101</code>, <code>00-102</code>, <code>Unit_103</code>, or <code>Unit 104</code>. </p></li>
  <li><p>Variations on regex input I have tried. </p>
  
  <p><strong>Regex which does not generate the error</strong></p>
  
  <p><code>CALL StoredProcedure('(Unit-101)');     // Or any other of the four units, non-escaped dash</code></p>
  
  <p><code>CALL StoredProcedure('(Unit\-101)');    // Or any other of the four units, escaped dash</code></p>
  
  <p><code>CALL StoredProcedure('(Unit-101|00-102)')</code>  </p>
  
  <p><code>CALL StoredProcedure('(Unit_103|Unit 104)')</code>  </p>
  
  <p><code>CALL StoredProcedure('(Unit-101|Unit_103|Unit 104)')</code>   </p>
  
  <p><code>CALL StoredProcedure('(00-102|Unit_103|Unit 104)')</code></p>
  
  <p><code>CALL StoredProcedure('(Unit-101|00-102|Unit 104)')</code></p>
  
  <p><strong>Regex which generates the error</strong> </p>
  
  <p><code>CALL StoredProcedure('(Unit-101|00-102|Unit_103|Unit 104)')</code></p>
  
  <p><code>CALL StoredProcedure('(Unit 104|00-102|Unit-101|Unit_103)')</code></p>
  
  <p><code>CALL StoredProcedure('(Unit-101|00-102)|(Unit_103|Unit 104)')</code></p>
  
  <p>I tried this with some random values too - and it seems to break with 4 values, regardless of what they are.  </p></li>
  </ul>
  
  <hr>
  
  <ul>
  <li>I just tried running another stored procedure which takes in the same value (i.e. <code>_r_unit</code>) and it works correctly with 4 values. They are <em>very</em> similar queries so I'm trying to find a difference in the <code>WHERE</code> clauses where the regex is used but I can't find any ... </li>
  </ul>
  
  <p><strong>Stored Procedure 1 WHERE clauses - Generates Error</strong> </p>
  
  <pre><code>/* first WHERE clause, to retrieve results FROM one database */ 
  
  WHERE
      `Date` BETWEEN _startDate AND _endDate 
       AND `Unit Type` = _unitType
       AND `Unit Serial` RLIKE _r_unit
       AND `Driver` RLIKE _r_driver 
       AND `Error` != ""
       AND `Error` RLIKE _r_errorCode 
  
  /* second WHERE clause, to retrieve results FROM a second (relational) database */
  /* (there are two WHERE clauses because the results are unioned together) */
  
  WHERE 
      DB._Records.Date BETWEEN _startDate AND _endDate
      AND DB.UnitTypes.Module = _unitType
      AND DB.Units.Serial RLIKE _r_unit 
      AND DB.Drivers.Driver RLIKE _r_driver 
      AND DB.ErrorCodes.ErrorCode != ""
      AND DB.ErrorCodes.ErrorCode RLIKE _r_maintCode
  </code></pre>
  
  <p><strong>Stored Procedure 2 WHERE clauses - Does not generate error</strong> </p>
  
  <pre><code>/* first WHERE clause, to retrieve results FROM one database */ 
  
  WHERE 
      `Date` BETWEEN _startDate AND _endDate 
      AND `Unit Type` = _unitType
      AND `Unit Serial` RLIKE _r_unit
      AND `Driver` RLIKE _r_driver 
  
  /* second WHERE clause, to retrieve results FROM a second (relational) database */
  /* (there are two WHERE clauses because the results are unioned together) */
  
  WHERE 
      DB._Records.Date BETWEEN _startDate AND _endDate
      AND DB.UnitTypes.Module = _unitType
      AND DB.Units.Serial RLIKE _r_unit 
      AND DB.Drivers.Driver RLIKE _r_driver 
  </code></pre>
  
  <p><em>(I am aware some fields are badly named however this is an old system and a lot of things are dependent on it so they would be difficult to change)</em></p>
  
  <hr>
  
  <p>I'm at my wit's end! Any advice much appreciated! </p>