On CiviCRM 4.6.31
We have seen CiviCRM errors with SQL LEFT JOINs relating to custom data sets. It appears there is a MySQL limitation on how many tables can be joined.
This impacts on a number of CiviCRM features including CiviReport and Webform-CiviCRM integration.
In our case we were using webforms to update existing grants and could see an error like so in the Drupal log messages: -
The CiviCRM "grant getsingle" API returned the error: "DB Error: unknown error" when called by function "alterForm" on line 102 of wf_crm_webform_preprocess.inc with parameters: "Array ( [id] => 1202 [check_permissions] => [version] => 3 ) "
Looking further into this it appears anything that uses CiviCRM's API will also fall over when your system reaches this limit (I think it is 61 join tables).
At present, and having liaised with others in the community and core team members, the solution is to consolidate custom data fields. i.e. move fields and then delete some custom data sets.
In future, will there be another solution, or should we state this as a MySQL limitation, and therefore CiviCRM limitation?