Issues with complex queries in Query Builder and protected identifiers

There are so many edge cases such as this where CI's query builder will bork that the db library would be absolutely huge to make it work for all cases, and then it would have to be replicated for each DB driver so everything works the same across platforms. Then what do you do with Platform specific functions that don't have a similar counterpart in other platforms? It would be a monumental task.

Most of the code responsible for the issues I described here is not contained in the individual drivers. Most of the functionality in the drivers handles:
- connecting to/disconnecting from the database
- configuring the connection
- retrieving some metadata (version, list_tables, list_columns, field_data)
- retrieving error messages
- retrieving the number of affected rows or inserted ID

One relevant method in the drivers is _escape_str(), though there is far more going on within DB_driver.php when calling protect_identifiers() and/or escape_identifiers() than in the individual _escape_str() methods.

Platform-specific functions without a similar counterpart in other platforms are largely irrelevant for a cross-platform system. For anything of that nature that couldn't be run through query() directly, you probably can't do it in the existing database layer, anyway.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide