MySQL Bugs: #49747: ERROR CREATE VIEW

Bug #49747	ERROR CREATE VIEW
Submitted:	16 Dec 2009 20:03	Modified:	17 Dec 2009 15:46
Reporter:	roy valencia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
sorry my english is so-so or bad.

This sentences is execute correct independence:

+///
		select
						(select
							if(
								(
									(
										(`ap`.`utilityID` = 2) and (`deliverycharge`.`amountDue` = 0.00)
									)
									or 
									(`deliverycharge`.`amountDueFromEdi` = 0.00)
								),
								(`deliverycharge`.`currentCharges` + `deliverycharge`.`otherCharges`),
								if	(
									isnull(`deliverycharge`.`amountDueFromEdi`),`deliverycharge`.`amountDue`,`deliverycharge`.`amountDueFromEdi`
									)
							) AS `if(
										ap.utilityid = 2 and amountdue = 0.00 or amountduefromedi = 0.00, currentcharges + othercharges,
										if	(
												amountduefromedi is null, amountdue, amountduefromedi
											)	
									)`
						from `deliverycharge` 
						where 
						(`deliverycharge`.`id` = max(`dc`.`id`)))
					AS `accountBalance`
					from (
							(
								(`accountproductagreement` `apa` join `accountproduct` `ap`) join `account` `a`
							) join `deliverycharge` `dc`
						)
					where
						(
							(`apa`.`accountProductID` = `ap`.`id`)
							and (`ap`.`accountProductStatusID` in (_latin1'PR_OK',_latin1'PR_EXP',_latin1'PR_DRP',_latin1'PR_DEL'))
							and (`a`.`id` = `ap`.`accountID`)
							and (`dc`.`accountProductAgreementId` = `apa`.`id`)
							and (`dc`.`accountingSubmissionStatus` = _latin1'SS_SUBMITTED'))
					group by `ap`.`accountNumberPartner`;
////*

the sentence execute ok.

but , when i want to create on view ; the anwser is error:

create view prueba as

		select
						(select
							if(
								(
									(
										(`ap`.`utilityID` = 2) and (`deliverycharge`.`amountDue` = 0.00)
									)
									or 
									(`deliverycharge`.`amountDueFromEdi` = 0.00)
								),
								(`deliverycharge`.`currentCharges` + `deliverycharge`.`otherCharges`),
								if	(
									isnull(`deliverycharge`.`amountDueFromEdi`),`deliverycharge`.`amountDue`,`deliverycharge`.`amountDueFromEdi`
									)
							) AS `if(
										ap.utilityid = 2 and amountdue = 0.00 or amountduefromedi = 0.00, currentcharges + othercharges,
										if	(
												amountduefromedi is null, amountdue, amountduefromedi
											)	
									)`
						from `deliverycharge` 
						where 
						(`deliverycharge`.`id` = max(`dc`.`id`)))
					AS `accountBalance`
					from (
							(
								(`accountproductagreement` `apa` join `accountproduct` `ap`) join `account` `a`
							) join `deliverycharge` `dc`
						)
					where
						(
							(`apa`.`accountProductID` = `ap`.`id`)
							and (`ap`.`accountProductStatusID` in (_latin1'PR_OK',_latin1'PR_EXP',_latin1'PR_DRP',_latin1'PR_DEL'))
							and (`a`.`id` = `ap`.`accountID`)
							and (`dc`.`accountProductAgreementId` = `apa`.`id`)
							and (`dc`.`accountingSubmissionStatus` = _latin1'SS_SUBMITTED'))
					group by `ap`.`accountNumberPartner`
;

is error in:

Incorrect column name 'if(
										ap.utilityid = 2 and amountdue = 0.00 or amountduefromedi = 0.00, currentcharges + ot'

i don't know why this error , because the sentences independeces execute is ok.

How to repeat:
when i create the view

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Name `if(
          ap.utilityid = 2 and amountdue = 0.00 or amountduefromedi = 0.00,
currentcharges + othercharges,
          if (
            amountduefromedi is null, amountdue, amountduefromedi
           ) 
         )` is too long to be used as column name, so can not be used in VIEW

this view with the sintax equal exist in bd production 5.0.56 Enterprise.

because i dont know this error is for different version or migration ?¿.

thanks.

Please see at http://dev.mysql.com/doc/refman/5.0/en/news-5-0-67.html starting from "Incompatible Change: It was possible to create a view having a column whose name consisted of an empty string or space characters only. "