MySQL Bugs: #36566: IFNULL SELECT STATEMENT not recognised in XSD

Bug #36566	IFNULL SELECT STATEMENT not recognised in XSD
Submitted:	7 May 2008 14:47	Modified:	20 May 2008 21:23
Reporter:	R Roberts	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Hello

I am using VS 2005 C#. I am using SQLYog Community Edition v6.16. MySql is 5.2.1.

I have an SQL statement that needs to be incorporated in the XSD.

I found that I get an error on the tableadapter wizard when clicking 'Next>' after putting the SQL query.

The error states "'SELECT' is not recognized. Please check your MySql...'.

I think it is a bug because it seems not to be accepting it. I have tried the sql text directly on the SQLYog Community Edition, it seems to be working alright and brings out the records fine.

Any ideas or suggestions please?

Thanks

How to repeat:
1.Create new table adapter on XSD
2. Put SQL text

i.e. SELECT a.ID,a.Desc,
IFNULL((SELECT URL FROM table x where a.id = x.id) as url,
IFNULL((SELECT Price FROM table d where a.id = d.id) as Price
From table a

3. Click "Next>", error appears.

Error

Attachment: Bug36566.JPG (image/jpeg, text), 34.20 KiB.

Hi and thanks for your report. Verified as described using c/NET 5.2.1 on WinXP Pro SP2 localhost with MySQL server 5.0.58 and VS 2005.
Query is functioning in mysql cl client.
mysql> SELECT d.Rbr, d.Konto, IFNULL((SELECT k.Opis FROM solusd.kontplan AS k WH
ERE d.Konto = k.Konto AND d.Godina = k.Godina), "SomeText") AS AddTxt FROM solus
d.dnevnik AS d WHERE d.Godina = 2007;
Empty set (0.05 sec)

Investigating.  Nothing yet.

This is not a bug.  Unfortunately Visual Studio (2005 & 2008) is hard coded to examine T-SQL in this wizard.  The wizard attempts to "understand" the sql in order to construct the columns and to attempt to create the update/insert/delete statements.  IFNULL is seen as a function and causes the failure.  The table adapter is still created (this is just a warning) and the fill method of the adapter still works.  I have confirmed this with my contact at MS.