Toad World® Forums

LIMIT clause in MySQL code not working properly?

I am looking to extract a very large dataset (1.5 million rows, or so) to Excel files per a request from a business partner. Since this is larger than the Excel row count limit, my thought was to split the extract in the MySQL code via LIMIT clause by adding the following at the bottom to a series of scripts extracting the same data:

LIMIT 0, 250000;
LIMIT 250000,250000;
LIMIT 500000,250000;

I have set the drop down to "Fetch All Rows" at the top by execute.

The only problem is, if the LIMIT clause in the query is above 10,234 it will fetch one row less than the LIMIT clause specifies.

LIMIT 0,10000; fetches 10000 rows
LIMIT 0,11000; fetches 10999 rows
LIMIT 0,10235; fetches 10234 rows
LIMIT 0,10234; fetches 10234 rows

I'm running Toad Data Point 5.0.4.45 64 bit.

This may actually be an issue with MySQL 5.6, as I'm seeing the issue in MySQL Workbench as well. Will do some further testing and update.

MySQL Workbench has a different number where the value gets returned lower than expected - 9744. LIMIT 0,9745 on Workbench returns 9744, and so does 0,9744.

mysql.exe binary connecting to remote host has the same issue at the same limit as Toad Data Point, 10234.

This does seem to be a client related issue.

Good to know... I just spent about 10 minutes googling to see if this issue was documented... not seeing anything yet... hopefully mySQL later versions have this fixed.

It's a weird one for sure. I was able to change one of the joins which worked around it. The left join I was using previously would join multiple data points from the right, resulting in duplicate rows, so I used a SELECT DISTINCT as part of the query. Seems like that plus the high row count may have been a trigger for it. I'll need to test this with MySQL 5.7 or 8.0 at some point to see if a server side fix was put into place discreetly.

You'd make a great Support Engineer, with your troubleshooting skills... :slight_smile:

Glad you sniffed out a work-around.

That's how I started out my career - now a software/solutions architect!

Have a workaround, but have also escalated a case to Oracle. Given the difference between the threshold at which the LIMIT value behaves based on the different clients, I would think it is something on the client side.