I use Base as a GUI interface to a MariaDB database where the actual records are stored.
I’m seeing that a new bug (new to me, which is somewhere after LO V5.4) in the GUI query editor now breaks outer JOINs in my SQL statements.
For example, here is a FROM with some JOINs statement (i.e. obtained from QUERIES | then right click on the query name | Edit in SQL view) that works (i.e. I can run it and get a results list). Note this was hand-edited to add line breaks before the JOIN statements so it’s easier to read:
FROM { oj
`links`.`locations` AS `locations`
LEFT OUTER JOIN `links`.`locations house areas` AS `locations house areas` ON `locations`.`Location House Area ID` = `locations house areas`.`Location House Area ID`
LEFT OUTER JOIN `links`.`locations houses` AS `locations houses` ON `locations houses`.`House ID` = `locations house areas`.`House ID`
RIGHT OUTER JOIN `links`.`battery appliances` AS `battery appliances` ON `locations`.`Location ID` = `battery appliances`.`Location ID`
LEFT OUTER JOIN `links`.`battery types` AS `battery types` ON `battery appliances`.`Battery type ID` = `battery types`.`Battery type ID`
LEFT OUTER JOIN `links`.`battery technologies` AS `battery technologies` ON `battery types`.`Battery technology ID` = `battery technologies`.`Battery technology ID`
LEFT OUTER JOIN `links`.`stuff, owners` AS `stuff, owners` ON `battery appliances`.`Owner ID` = `stuff, owners`.`Owner ID`
LEFT OUTER JOIN `links`.`locations possible` AS `locations possible` ON `locations`.`Possible Location ID` = `locations possible`.`Possible Location ID`
}
But if I first click ‘Switch Design View On/Off’ to see/and possibly edit the SQL with the GUI editor:
And then if I click ‘Switch Design View On/Off’ again to return to the SQL text view, I now have this FROM statement which is broken by inclusion of two extra }, {s:
FROM { oj
`links`.`locations` AS `locations`
LEFT OUTER JOIN `links`.`locations house areas` AS `locations house areas` ON `locations`.`Location House Area ID` = `locations house areas`.`Location House Area ID`
LEFT OUTER JOIN `links`.`locations houses` AS `locations houses` ON `locations house areas`.`House ID` = `locations houses`.`House ID`
}, { oj
`links`.`locations` AS `locations`
RIGHT OUTER JOIN `links`.`battery appliances` AS `battery appliances` ON `locations`.`Location ID` = `battery appliances`.`Location ID`
RIGHT OUTER JOIN `links`.`battery types` AS `battery types` ON `battery types`.`Battery type ID` = `battery appliances`.`Battery type ID` RIGHT OUTER JOIN `links`.`battery technologies` AS `battery technologies` ON `battery technologies`.`Battery technology ID` = `battery types`.`Battery technology ID`
RIGHT OUTER JOIN `links`.`stuff, owners` AS `stuff, owners` ON `stuff, owners`.`Owner ID` = `battery appliances`.`Owner ID`
}, { oj
`links`.`locations` AS `locations`
LEFT OUTER JOIN `links`.`locations possible` AS `locations possible` ON `locations`.`Possible Location ID` = `locations possible`.`Possible Location ID`
}
And when run it produces this error:
This bug did not exist in LO 5.4 which I used for many years. As near as I can tell the {oj ..} construct has something to do with jdbc (the Java database Connector). I think the idea is that because different databases have somewhat different syntax, it is a way to deal with translating the outer join syntax to work correctly for any given connector.
Workaround
I can use snapper to get a previous copy of the database, open this older copy in SQL mode, extract the old SQL’s FROM statement and insert it into the broken SQL and run it and my query works again. It requires that I no longer can ever use the GUI query editor, but rather now must edit my SQL only in text.
Am I the only one with this problem? I wish I had hope that I could report this as a bug and hope to get it fixed, but that hope was lost many years ago. Now it seems the only way to maintain my databases is by hand editing the SQL going forwards, which pretty much means that the flashy LO Base GUI is useless now, not entirely, but much closer to useless.
Also, for what it’s worth, the old bug in the GUI query editor that would mixup/mangle JOIN LEFT/RIGHT directions is still there after all these years. You’ll see this bug when you have more than about 3 joins. My old workaround for that was to hand check all of my joins every time before I saved.
I know this can’t be true, but it almost seems like someone is deliberately trying to destroy LO base, little by little. But that’s probably just my own paranoia. More likely is that someone is improving base for some other connector, like Fire…whatever, and accidentally breaking other things as they do that work.
The other thing that is annoying and could be improved is to not destroy white space between SQL text edits. If one is going to edit SQL as text, keeping line breaks and spacing in place is very helpful to re-editing it. Having to start parsing it all over with each new edit is easy for a computer but hard for a human.