Description:
When using Forward Engineer SQL CREATE scripts, the generated SQL contains irregularities with regard to character case.
Reading from the bottom up of the attached generated script, you can see that there is a JOIN of the `user_usr` table using the alias `Q_U` for the table. However, if you look at the JOIN's restriction, the alias has had it's character case changed: `q_u`.`id_usr`
This causes an issue when running the script on a Linux based MySQL installation.
-- -----------------------------------------------------
-- View `mydb`.`queue_table_name`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `mydb`.`queue_view_name` ;
SHOW WARNINGS;
DROP TABLE IF EXISTS `mydb`.`queue_view_name`;
SHOW WARNINGS;
CREATE OR REPLACE ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `queue_view_name` AS select `spk`.`id_spk` AS `id_qtc`,`spk`.`id_spk` AS `idspk_qtc`,`q`.`id_que` AS `idque_qtc`,`q_u`.`id_usr` AS `idusr_qtc` from (((((((`our_table_spk` `SPK` left join `our_table_transaction_stx` `ST` on((`st`.`id_stx` = `spk`.`idstx_spk`))) left join `sub_txcode_stc` `STC` on((`st`.`idstc_stx` = `stc`.`id_stc`))) left join `txcode_txc` `TC` on((`stc`.`idtxc_stc` = `tc`.`id_txc`))) left join `que_txcode_mapping_qtm` `QTC_M` on((`tc`.`id_txc` = `qtc_m`.`idtxc_qtm`))) left join `queue_que` `Q` on((`qtc_m`.`idque_qtm` = `q`.`id_que`))) left join `que_user_mapping_qum` `QU_M` on((`q`.`id_que` = `qu_m`.`idque_qum`))) left join `user_usr` `Q_U` on((`qu_m`.`idusr_qum` = `q_u`.`id_usr`)));
SHOW WARNINGS;
How to repeat:
Use Windows Vista, MySQL Workbench version 5.0.2.5 OSS
Reverse engineer a Schema containing Tables and Views.
Use Forward Engineer SQL CREATE scripts...
Choose the first three check-box options:
[x] Generate DROP <object> statements
[x] Generate separate CREATE INDEX statements
[x] Generate SHOW WARNINGS after every DDL statement
Choose to export the View objects only:
[x] Object of type MySQL View
Finish.
Suggested fix:
Standardize the formatting of the table aliases and use them consistently.
Description: When using Forward Engineer SQL CREATE scripts, the generated SQL contains irregularities with regard to character case. Reading from the bottom up of the attached generated script, you can see that there is a JOIN of the `user_usr` table using the alias `Q_U` for the table. However, if you look at the JOIN's restriction, the alias has had it's character case changed: `q_u`.`id_usr` This causes an issue when running the script on a Linux based MySQL installation. -- ----------------------------------------------------- -- View `mydb`.`queue_table_name` -- ----------------------------------------------------- DROP VIEW IF EXISTS `mydb`.`queue_view_name` ; SHOW WARNINGS; DROP TABLE IF EXISTS `mydb`.`queue_view_name`; SHOW WARNINGS; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `queue_view_name` AS select `spk`.`id_spk` AS `id_qtc`,`spk`.`id_spk` AS `idspk_qtc`,`q`.`id_que` AS `idque_qtc`,`q_u`.`id_usr` AS `idusr_qtc` from (((((((`our_table_spk` `SPK` left join `our_table_transaction_stx` `ST` on((`st`.`id_stx` = `spk`.`idstx_spk`))) left join `sub_txcode_stc` `STC` on((`st`.`idstc_stx` = `stc`.`id_stc`))) left join `txcode_txc` `TC` on((`stc`.`idtxc_stc` = `tc`.`id_txc`))) left join `que_txcode_mapping_qtm` `QTC_M` on((`tc`.`id_txc` = `qtc_m`.`idtxc_qtm`))) left join `queue_que` `Q` on((`qtc_m`.`idque_qtm` = `q`.`id_que`))) left join `que_user_mapping_qum` `QU_M` on((`q`.`id_que` = `qu_m`.`idque_qum`))) left join `user_usr` `Q_U` on((`qu_m`.`idusr_qum` = `q_u`.`id_usr`))); SHOW WARNINGS; How to repeat: Use Windows Vista, MySQL Workbench version 5.0.2.5 OSS Reverse engineer a Schema containing Tables and Views. Use Forward Engineer SQL CREATE scripts... Choose the first three check-box options: [x] Generate DROP <object> statements [x] Generate separate CREATE INDEX statements [x] Generate SHOW WARNINGS after every DDL statement Choose to export the View objects only: [x] Object of type MySQL View Finish. Suggested fix: Standardize the formatting of the table aliases and use them consistently.