MySQL Bugs: #27379: Connector/Net Inserting Comma in dataSet Select Query

Bug #27379	Connector/Net Inserting Comma in dataSet Select Query
Submitted:	22 Mar 2007 17:10	Modified:	21 Jul 2010 18:57
Reporter:	Robert Saari	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0.3, 5.0.6SVN	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	comma, dataset, select query, table adapter configuration, visual studio 2005

Description:
Using Visual Studio 2005, I'm trying to create a dataSet using the tableAdapter Configuration Wizard.  After typing the Select Query into the Query Builder window, clicking the Execute Query button generates a syntax error message. After checing the syntax, I discovered that a comma is being added to the query string.

This is the query I typed (pulls data from two databases):

SELECT t1.lastname, 
t1.firstname, 
score,
round(score/5 * 100),
timediff(start, finish)
from database1.mdl_user t1, database2.tbl_quiz_results t2
where t1.id = t2.empid
and t2.quiz = 13

After clicking the Execute Query button, it changes the query to:

SELECT t1.lastname, 
t1.firstname, 
score,
round(score/5 * 100,),
timediff(start, finish)
from database1.mdl_user t1, database2.tbl_quiz_results t2
where t1.id = t2.empid
and t2.quiz = 13

There is a comma inserted in line 4, inside the parenthesis.  Deleting the comma and executing again results in the comma returning.

If I don't use the Query builder page, and just type the query into the "Enter Query" page, the comma is not added and the query executes correctly.

How to repeat:
In a Visual Studio 2005 Website, add a new dataset.
When the TableAdapter Configuration Wizard opens, select a data connection that uses Connector/Net (or add new connection using Connector/Net).  On the Choose Command Type page, select the "Use SQL Statements" option.
In the Enter Query page, click the Query Builder button and enter a query string similar to my example, using a calculated field inside parenthesis.
Click the Execute Query button.  This is the point at which the comma is inserted.

Hi Robert and thanks for your report.

Verified just as described with latest c/NET sources. One can use even much simpler query to reproduce (1 table, 1 database ...).

We can't fix this.  The issue is that SQL Server's ROUND function takes a minimum of 2 parameters and the query builder is specifically wired for SQL Server.  One can work around this by always giving 2 parameters.