MySQL Bugs: #39078: LIMIT clause stops query parameter identification in Visual Studio

Bug #39078	LIMIT clause stops query parameter identification in Visual Studio
Submitted:	27 Aug 2008 18:36	Modified:	29 Aug 2008 16:29
Reporter:	Mark Rabjohn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	limit, Visual Studio, windows

Description:
I am trying to use LIMIT in a test query, and I note that several reports of this bug have been recorded as Not A Bug, but I can't get this to work.

Basically, I can enter LIMIT in my query, click past the error, and then preview data, which is fine, but the DLL won't compile even though preview data works.

I also note that when I use parameters, the code generator doesn't declare my queries.

How to repeat:
1) Add a query using the wizard, something like "SELECT * FROM contents WHERE subject = ?subjectid ORDER BY title LIMIT 10

2) Click past the error

3) You can now see that the function e.g. GetFirstTenPages is displayed as GetFirstTenPages(), instead of GetFirstTenPages(?subjectid)

4) Compile the DLL project - it only partially compiles - the table adapter for the contents table has many functions missing.

Suggested fix:
I can't think of a work around for this - Surely the connector needs to work with the Dataset designer and allow LIMIT clauses.

Thank you for the bug report. Try using the mysql.exe client and if you get the correct result provide the test case (code and table dump file) otherwise if you are using an older server try with the latest release and provide here its version. Thanks.

As instructed, I have created a simple single table database to demonstrate the limit problem.

I have accessed this using the command line client -

I have executed a describe to show how simple the table is.
I have executed a select to show the data that I have inserted.
I have executed a simple query with limits which works as expected.
As you can see, I am using Mysql 5.0.67-community-nt.

I am also using Connector/Net 5.1.7 with Visual Studio 2005.

The problems start in the Dataset designer - as follows:

1) Open connection to data in server explorer.
2) Add a new dataset - call it limittest.xsd.
3) Drag the contents table from server explorer into xsd.
4) Right-click Add->Query.
5) Select "Use SQL Statements" -> Next.
6) Select "Select which returns rows" -> Next.
7) Set query to "SELECT * FROM `limittest`.`contents` WHERE `subjectid` = ?subjectid LIMIT 4" -> Next.
8) Accept the name -> Next.
9) Error. "Generated SELECT Statement. Error in WHERE clause near 'LIMIT'. Unable to parse query text." -> Finish.
-------------
10) Right-click Preview Data...
11) Click Preview.
12) Error. "limittest.DataTable.FillBy, GetDataBy() could not be previewed. Parameter '?subjectid' must be defined."
-------------
13) Click OK.
14) Click Close.
15) Access parameters collection in properties for command.
16) Add ?subjectid and configure as int32 -> OK
17) Right-click Preview Data...
18) This now works.
19) Compile - view object in object browser - it appears that the object has generated correctly.
20) Test with Code:

        Dim tacontents As New limittestTableAdapters.DataTableTableAdapter
        Dim dtcontents As limittest.DataTableDataTable = tacontents.GetDataBy(1)

        For Each drcontent As limittest.DataTableRow In dtcontents.Rows
            Console.WriteLine("{0} {1} {2}", drcontent.contentid, drcontent.title, drcontent.subjectid)
        Next

SUMMARY:

I am surprised to find that this now seems to work - I didn't get as far as this in my msin application, but I know what steps to take now.

I was wrong to say that this doesn't work, because it is clearly possible to work around the errors that are thrown.

THE BUG: It is clear that the only bug is at step 9, since the issue at step 12 is a direct consequence of the connector not being able to determine the parameters if it can't parse the LIMIT clause.

--------------------

Y:\MYSQL\bin>mysql -h**** -u**** -p
Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.67-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use limittest
Database changed
mysql> describe contents;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| contentid | int(11)      | NO   | PRI | NULL    | auto_increment |
| title     | varchar(255) | YES  |     | NULL    |                |
| subjectid | int(11)      | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from contents;
+-----------+---------+-----------+
| contentid | title   | subjectid |
+-----------+---------+-----------+
|         1 | Page 1  |         1 |
|         2 | Page 2  |         1 |
|         3 | Page 3  |         1 |
|         4 | Page 4  |         1 |
|         5 | Page 5  |         1 |
|         6 | Page 6  |         1 |
|         7 | Page 7  |         1 |
|         8 | Page 8  |         1 |
|         9 | Page 9  |         2 |
|        10 | Page 10 |         2 |
|        11 | Page 11 |         2 |
|        12 | Page 12 |         2 |
|        13 | Page 13 |         2 |
|        14 | Page 14 |         2 |
|        15 | Page 15 |         2 |
|        16 | Page 16 |         2 |
+-----------+---------+-----------+
16 rows in set (0.00 sec)

mysql> select * from contents where subjectid = 1 limit 4;
+-----------+--------+-----------+
| contentid | title  | subjectid |
+-----------+--------+-----------+
|         1 | Page 1 |         1 |
|         2 | Page 2 |         1 |
|         3 | Page 3 |         1 |
|         4 | Page 4 |         1 |
+-----------+--------+-----------+
4 rows in set (0.00 sec)

mysql>

Mark, how do you wish me to proceed on this? Especially in light of Bug36566 and Bug#36622 and fact that MS hardcoded some stuff in their framework failing to recognize other vendors specifics (or that others even exist).

Hi Tonci,

I haven't examined the sources for the connector, nor am I aware of the hoops that Microsoft make you jump through to get all this stuff to work (although I have to workaround many a Microsoft issue during my career).

Don't misunderstand me, I think that you guys have done a great job - I'm  now able to code using Microsoft N-Tier Patterns instead of building Datasets and Dataadapters by hand!

If you really want my ideas (like you haven't considered all the angled before) I would imagine that if Microsofts underlying environment is incompatible with MySQL, there would seem to be several directions:

1) Rewrite an entire alternative Dataset designer tool that is MySQL centric - i.e. so that a programmer can select the build tool in the XSD file properties.

2) Make the connector reparse T-SQL into MySQL, but obviously MySQL's LIMIT is much simpler than T-SQL ranking.

3) Add intrinsic functions for all items that cannot be directly accessed, a possible example for LIMIT may be:

  Select limitfunc(10,10), contents.* from contents

Which could be recognised and converted to:

  Select contents.* from contents LIMIT 10 OFFSET 10

Perhaps the function should just return NULL. Programmers would still have to skip schema mismatch errors or allocate an extra column for the limitfunc though, so it wouldn't be perfect.

I think that the main thing that should be done is to add notes to all the bugs of this type such that all users know what to expect. i.e. 

1) The wizard will crash ---- But that's okay so long as:
2) You set up your parameters using the parameter collection in the properties window and:
3) Fully test using "Preview Data..."

Armed with the above 3 points of knowledge, I am able to continue unhindered.

Best Regards,

Mark

Mark, sorry, there was no offense taken, nor all angles already checked. That is not my job. I'm simply stating a fact that lead c/NET developer already discarded this idea and I have no means to proceed on this report.

However, being in my position, I can certainly ask Reggie to take another look.

Thanks for great report and your interest in MySQL.

I'm marking this as not a bug since it is not a bug in our connector.  I've talked with MS and, AIUI, the data adapter wizard only understands TSQL and doesn't interact with the DDEX providers at all.  We possibly could create our own data adapter wizard but that will take time.

For right now, my recommendation would be to use the @ parameter syntax and to leave off the limit clause to use the wizard but then hand edit the XML to add in the LIMIT clause.

Thanks Reggie.