Ask Your Question
0

MariaDB & MySQL SQL syntax: using boolean AND or OR in Base queries

asked 2017-10-01 08:24:45 +0200

EasyTrieve gravatar image

updated 2017-11-24 21:37:31 +0200

In Base query design what's the SQL syntax for AND and OR when connected to MySQL?


Typical usage examples:

Select True And False As `x` From `Foo`;
Select 1=1 And 2=2 As `x` From `Foo`;
Select if(1=1 And 2=2, 5, 6) As `x` From `Foo`;
Select if((1=1) And (2=2), 5, 6) As `x` From `Foo`;

I've tried everything I can think of including ...AND(1=1; 2=2)..., but keep getting a syntax error.


? Also, why is it again that there is this SQL interpretation process that goes on?

BTW, in the Tools | SQL window (uninterpreted) the following work as expected:

SELECT 1=1 And 2=2 As `x` From `Foo`;
SELECT (1=1 And 2=2) As `x` From `Foo`;
SELECT (1=1) And (2=2) As `x` From `Foo`;
SELECT if((1=1) And (2=2),5,6) As `x` From `Foo`;

Also of note is that

SELECT 1=1 As `x` ...

does not work as expected. Instead you must write

SELECT if(1=1,true, false) As `x` ..

Thanks.

Using LO 5.3.3.2

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-10-03 22:47:29 +0200

EasyTrieve gravatar image

updated 2017-11-24 21:45:48 +0200

Here are some workaround's:


OR:

|| provides OR functionality, i.e. it returns true for

True || True
True || False
False || True, but false for
False || False.


NOT:

If(condition,FALSE,TRUE) -- This is messy, but it works, i.e. if condition is true this returns false.


AND:

This boolean identity can then be used to produce AND from OR:

x AND y = NOT (NOT x OR Not y)

Therefore

If( If(x,FALSE,TRUE) || If(y,FALSE,TRUE), FALSE, TRUE)

For example:

If( If( x=1, FALSE, TRUE ) || If( y=1, FALSE, TRUE ), FALSE, TRUE )

And this works in MySQL, but oh, what a mess.


As I recall, in HSQLDB this requires CASEWHEN rather than simply IF. But unfortunately, || does not provide an OR function in HSQLDB.

From the HSQLDB1.8 User Guide:

SQL Expression

[NOT] condition [{ OR | AND } condition]"

But I can't seem to get it to work, not even in HSQLDB.


If testing for nulls:

Try concat([list of your values]), and then test for IS NOT EMPTY - often much simpler than using if(isnull(...), ..)

edit flag offensive delete link more
1

answered 2017-10-01 10:05:49 +0200

Jim K gravatar image

updated 2017-10-01 10:15:12 +0200

If starting from the design view, click the toolbar icon to Switch Design View On/Off. Then click the toolbar icon to Run SQL command directly to avoid the interpretation process. Now everything works as expected.

query SQL view

Note: I do not know what benefit the interpretation process provides. Invariably, my queries are run directly. This makes it simple because the same behavior occurs from a MySQL prompt as from a Base query.

edit flag offensive delete link more

Comments

1

The interpreter preforms some basic syntax verification before passing on. However, this is for generally for HSQL v1.8. It does provide for a user input parameter such as :enter_something. With Run directly on, this is not available.

Ratslinger gravatar imageRatslinger ( 2017-10-01 16:03:13 +0200 )edit

Thanks guys! Also just created this enhancement suggestion: https://bugs.documentfoundation.org/s...

EasyTrieve gravatar imageEasyTrieve ( 2017-10-01 19:42:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-01 08:24:45 +0200

Seen: 466 times

Last updated: Nov 24 '17