Steps to reproduce the issue

I uncovered this one troubleshooting an older component that had stopped working after a site was updated to the newest version of Joomla and PHP 7.

I was able to observe the issue because the code that had been written into the component utilized JModelList and it was pulling in the default list limit set in the Global Config of 20 for the site. The model's getListQuery() method was fine and the JDatabaseQuery object was being created along with a variable that had been bound to the query object (this was utilizing the new OCI8 Oracle Driver I had written over here in this PR: #12588).

Normally for the queries I typically write I'm not setting any sort of limit, but because of the use of JModelList in this case, the call to getItems() utilizes and sets a limit automatically.

Additionally, instead of using the select(), from(), where() from the query object, I was simply using $query->setQuery() and passing in the whole query directly, which was setting the internal sql property with the query string.

What I discovered was that this combination is problematic right now, at least for the Oracle driver (for both the proposed Oracle driver update in the PR above and the existing PDO version currently in the Joomla codebase since I based my version on what was already in the codebase last summer). You can't have a bound variable along with a limit/offset.

This is primarily due to the following line in the PDO driver:
https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/driver/pdo.php#L708
and the corresponding line in the new driver in the PR above:
https://github.com/orware/joomla-cms/blob/e1785ac2a96ef43892fb188ba981fc6198d0262f/libraries/joomla/database/driver/oracle.php#L731

The main reason why that line causes an issue is because processLimit() returns a string and in both of the lines above, that string is overwriting the query object so any variables that might been bound are then lost (executing the query results in an error).

Current potential solution

On my end, I ended up fixing this by changing the line above to use the query's setLimit() method instead, which leaves the bound variables intact. However this doesn't fully fix the issue.

Since the raw query had been set directly within the query object, when the query is converted to a string it ignores the limit settings that have been set. This is because the __toString() method just returns the entire raw query:
https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/query.php#L223

With my notes further below about how Joomla 3.2.1 was still utilizing the setLimit() method correctly, I think this is why it never surfaced as an issue before, because even though a limit was being set via JModelList, it was then ignored when the query object was converted to a string and the query was executed so it ended up returning the expected full result set anyway.

However, since we want the correct behavior in all cases, if someone did set a raw query and did set a limit they should receive a limited result set as expected and that currently won't happen unless we also adjust the query object's __toString() method to include that information, or if we override that method with some additional code in at least the Oracle query classes (in order to prevent any potential issues for other drivers...this would have to be tested in a bit more detail).

Expected result

The limited result set for the query with bound variables.

Actual result

Query execution error (tested so far with the Oracle driver only, potentially also would apply to other drivers utilizing the JDatabaseDriverPdo class).

System information (as much as possible)

I'm testing this on PHP 7.0.x with Joomla 3.6.5 (though I'm pretty sure the same issue applies to 3.7).

Additional comments

This issue probably remained undetected for a while simply because for most of us that use these other drivers available in Joomla, if they are like me, we rarely need to set a limit directly in our code, and don't typically utilize the JModel functionality (we just write our own methods to retrieve the data we need from these other databases and integrate that into an extension we might be building for our organization).

What's a bit weird to me, is that this issue wasn't there before as far as I can tell, but mainly cropped up after I had upgraded the site to the latest Joomla version (I think it was on a 3.2.x release before I did the update) so even though that existing code was still using JModelList, it never seemed to be utilizing the Global Config list limit of 20 since it was always returning the full result as we expected in the past.

In taking a closer look at the Joomla 3.2.1 tagged release code, it appears that the issue must have been introduced at a later time, because the code then was correctly utilizing the query's setLimit() method:
https://github.com/joomla/joomla-cms/blob/3.2.1/libraries/joomla/database/driver/pdo.php#L697

Looks like the exact commit where this change was introduced was this one:
f11f4eb#diff-ec0f1d11cc854648938d5f4774f1fa0b