Currently when connecting to any non-built-in database, such as Mariadb, MySQL, etc, the graphical query editor is only usable for simple queries, with simple SQL. For example, if you try to use "AND" to test two conditionals you get: "syntax error". See: https://ask.libreoffice.org/en/question/133467/base-andor-syntax-w-mysql/ The only workaround is to turn off the query interpretor (for how, see link above) and this allows your SQL to pass directly to MariaDB, but at the cost of disabling the powerful LO GUI query editor. Now you're back to writing plain old SQL text but with no join diagrams. :-( Problem summary: The LO SQL interpreter must be able to read SQL text so as to produce the GUI view. ENHANCEMENT SUGGESTION: For the GUI editor to work with all SQL varieties, I think what is needed is a smarter SQL parser (or you might say a dumber one), one that parses just enough of the SQL to produce the GUI, but not so much as to limit enhanced SQL features. In other words, it needs to be able to parse the SELECT, then break apart the comma joined field descriptions (but without full forward parsing), then find the FROM, and proceed as before. This would allow the GUI to work with complex SQL. Parser: 1) Look for SELECT, 2) then look for select fields descriptors (delimited by an un-bracketed comma (i.e. not inside (), '', or ``, etc.), 3) until FROM is found, then proceed as before. I think this should be a rather simple parser, but of course no parser is trivial. For the future I think this would be an important Base improvement and help keep LO flexible and allowing users to better connect to a wide variety of databases. I assume this is a pretty big challenge, so will manage my hopes of it coming soon. Thanks.
To protect backward compatibility, I think there should probably be a switch (a toggle button or setup parameter) to select the parser: new or old.
Here's an additional serious setback from this issue: If you do turn the "Run SQL Command Directly" toggle on, to try and run MySQL code directly, suddenly you can't use any of your other queries in your FROM statement.
Certainly not a trivial undertaking, in view of the "dialects" of SQL used by each different db engine, but from a user perspective a worthwhile one.
Query-editor should first allow all the SQL-conditions for internal databases. These are at this moment HSQLDB 1.8 and Firebird 3.0. All external server-databases, which will use the same code as the internal databases or basic SQL-code should be provided in GUI. If there is anybody who will use special code for his database he should switch to SQL. I have read the thread in ask.libreoffice.org and must say: I haven't understand where the code of this bug should be used for.
Hi Robert, >> Query-editor should first allow all the SQL-conditions for internal databases. These are at this moment HSQLDB 1.8 and Firebird 3.0. I don't expect to change your mind, but some of the things you say seem more a matter of taste rather than based on facts. So can we start with some facts. I think any comparative discussion of databases should start here, with what the world is actually using: https://db-engines.com/en/ranking https://blog.jooq.org/2013/10/03/the-10-most-popular-db-engines-sql-and-nosql/ Of note is that MySQL is #2 with a huge market saturation, way behind is Firebird at #30, and HSQLDB (not sure which version) at #49. To put this in some perspective, for every person using HyperSQL there are 200 others using MySQL. I have used MySQL for large application in a production environment and found it very good. I also think HSQLDB 1.8 and Firebird are good and fun toys, but only for students or hobbyists. They are easy to get going in LO, but they are also not very capable. And furthermore Firebird was buggy when I last tried it. This is not to say that everyone should use the same thing. My personal preference these days is open source. linux is that leader but only has 3% share of usage. see: https://www.netmarketshare.com/operating-system-market-share.aspx?qprid=10&qpcustomd=0 But the good thing about going with a leader like Linux or MySQL is that when I have a question there are lots of other folks who have answers. MySQL, and now it's binary open source twin MariaDB, are production databases with a long history behind them and far more features available. Also of note is that MariaDB is now shipped as the default open source database with Debian and it's derivatives like for example the open source leader: Ubuntu. See: https://mariadb.org/debian-9-released-mariadb-mysql-variant/ >> If there is anybody who will use special code for his database he should switch to SQL. With so many databases out there I understand your confusion, but MariaDB is MySQL which is SQL. See: https://en.wikipedia.org/wiki/MariaDB >>I have read the thread in ask.libreoffice.org and must say: I haven't understand where the code of this bug should be used for. AND and OR are the two most fundamental boolean operators from which all other boolean logic is derived. In this case they are used to join two conditional expressions inside an IF statement and produce a result as follows: If(x AND y) --- true when both x and y are true. If(x OR y) --- true when either x or y is true. Imagine if the + (plus) or - (minus) operator didn't exist? Well, AND and OR are the boolean equivalents to + and -.
MYSQL Workaround for AND OR and NOT added to https://ask.libreoffice.org/en/question/133467/base-andor-syntax-w-mysql/?answer=133676#post-id-133676
Problem for more connections: The SQL-code will differ. Internal databases have different functions. This functions should be the first which will be provided by the GUI. CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] ... [ELSE v4] END works with HSQLDB and Firebird and also with MySQL/MariaDB. You could use AND or OR inside this code and it will work in GUI. No need to change to direct SQL and to get a query, where you couldn't input any data. Neither HSQLDB nor Firebird do use IF. MySQL (and MariaDB) aren't the same as 'SQL', it's one kind of SQL. Many functions will work in the internal and external databases the same way, but there are also functions, which doesn't. The same you have found for logical OR (||) in MySQL is used for concatenate in HSQLDB and Firebird - so it won't work as expected with the GUI, only with direct SQL. Don't know if there is any developer, who will provide an external special SQL-code for a database where a direct connection isn't provided by LO at this time without an extension. The only external SQL database with a direct connection to LO, provided by LO by default, at this moment is PostgreSQL. I'm also using MariaDB in production environment. But I won't expect Base will provide all functions or special code for it.
Who would have thought a few years ago that a rocket could be landed, yes on land without crashing? But Space-X now lands them just the reverse of how they take off. Elion Musk and his Space-X has done the impossible. So we can make a much better Base? I think so. It starts by imagining the unthinkable. I believe that it's quite possible to write a parser for the Query GUI editor which will allow much more widespread SQL varieties to be used. SQL parsing is not that hard. When I was a younger guy I wrote several advanced parsers that would do the sort of thing I'm suggesting. This enhancement suggestion is hopefully for some younger hot-shot software guy who will take up this challenge. Here are a couple places that describe the SQL language structure in enough detail to write such a parser: https://mariadb.com/kb/en/library/sql-language-structure/ https://www.ansi.org/news_publications/news_story?menuid=7&articleid=753a952d-1244-415b-bb92-0010750bb8cd