Unnecessary Brackets And Database Names Using Views & Query Design

[iceman]

When I make a View and later look at the DLL it is different from what I originally wrote in the editor

Some changes are…

• Loads of unnecessary brackets have been inserted.
• Every table has the database name put in front of it.
• Every identifier is surrounded by a back quote
• AS has been removed – I know it is unnecessary but it aids clarity

eg, part of the sql that I wrote might be

from member_status as t1

inner join status_types

on t1.status_type_id = status_types.status_id

left join member_status as t2

on t1.member_id = t2.member_id

    and (t1.date_assigned < t2.date_assigned

             or (t1.date_assigned = t2.date_assigned and t1.member_id < t2.member_id))

yet the DLL of the view created becomes

from ((`TheDatabaseName`.`member_status` `t1`

 join `TheDatabaseName`.`status_types`

 on ((`t1`.`status_type_id` = `TheDatabaseName`.`status_types`.`status_id`)))

 left join `TheDatabaseName`.`member_status` `t2`

 on (((`t1`.`member_id` = `t2`.`member_id`)

 and ((`t1`.`date_assigned` < `t2`.`date_assigned`)

 or ((`t1`.`date_assigned` = `t2`.`date_assigned`)

 and (`t1`.`member_id` < `t2`.`member_id`))))))

This is really frustrating, especially when I need to copy the DLL to recreate the view in a backup database on a diferent server that has a different database name. The problem is made worse by all the definer/ algorithm/security tags that get inserted as well.

 

Similar code  'enhancements' are made when designing sql through the query designer.

 

Is there, or can there be, some sort of switch that will prevent the generation of the unwanted database name, back quotes and all the unnecessary brackets?

 

 

 

 

 

 

 

 

 

[peterlaursen]

In ‘preferences’ there is an option to turn off backquotes. There is no option to turn off brackets.

[Author]

Posts