# How to golf evaluation of math expression in MySQL?

The challenge given to me is comparatively easy, and specific to MySQL only. I'm given a table expressions which have mathematical calculations done by a kid. Basically, I've to select all the right calculations!

The table have the following properties,

• a : an integer, the left operand.
• b : an integer, the right operand.
• operation : a char, any one operation from '+', '-', '*' and '/' (pure-div).
• c : an integer, the result given by the kid.

# Example

a  b  operation  c
------------------
2  3  +          5
4  2  /          3
6  1  *          9
8  5  -          3


As we can see, the second and third operations are wrongly calculated, so they need to be filtered out. Here is what I've done till now,

# MySQL, 88 chars

Rule: 88 chars is calculated by removing all spaces (\s+) from the given code. This is MySQL, so they simply take amount of chars (calculated here).

select * from expressions
where if(
'+' = @o := operation,
a + b,
if(
@o = '-',
a - b,
if(
@o = '*',
a * b,
a / b
)
)
) = c


But, I'm sure that this can be golfed further, as many other submissions are of 73 chars, which is 15 bytes fewer than mine!

I think I need to change the idea of my solution, but I can't find anything which can evaluate expressions directly in MySQL. Any ideas?

# Rules

• The names, specific to table, included in the code isn't subject to change.
• select is considered to be the only method to output/result.
• For multiple statements, it should be wrapped inside begin ... end, as the whole code is the body of a function, defined as,
CREATE PROCEDURE expressionsVerification()
• Generally language-specific challenges work because of a unique feature inherent to the language (for example). This might fall under a [tips] challenge, but in that case it needs to have the tips tag and you need to include what can and can't be golfed. For example, changing operation or expressions in your code would reduce bytes, are they acceptable golfs? Nov 24 '20 at 13:38
• @cairdcoinheringaahing - thanks for informing me, I've included any necessary rules. Also, can I add mysql tag, as in the example post? Nov 24 '20 at 13:45
• You talk about oper at the top, but operation in your rextester code and link. Which of the two should it be? Also, I barely know anything about MySQL, but maybe you can somehow create an array [a+b,a-b,a*b,a/b], get the index of the operator-character in "+-*/" and use it to index into the created value array? Not sure how to accomplish it, and not sure if it's shorter though. Just an idea. Nov 24 '20 at 14:15
• @KevinCruijssen - It was a typo, edited it. Though, arrays are not a type in mysql, but I was also thinking over thinking some kind of map or hash. Nov 24 '20 at 14:27
• Do you allow a view definition? This will provide the ultimative solution: select * from e where c = r. Note e is view on expressions that defines a column r with the rigth answer. This is the database approach. Nov 24 '20 at 22:45

# Score 69

select * from expressions
where elt(ord(operation)/2-20,a*b,a+b,a-b,a/b)=c


Takes inspiration from Marmite Bomber's use of elt, but uses a magic formula. Indexes into the list via ord(operation)2/-20, which buckets the four character codes across 1 to 4 by abusing elt's rounding behavior.

-1 thanks to Arnauld, who improved on the modulo reduction.

-1 thanks to Bubbler, who found a better non-modulo formula.

## Score 73

select * from expressions
where elt(instr('*/-+',operation),a*b,a/b,a-b,a+b)=c


This is my best guess as to the 73 you mention other people have gotten in the question.

• where elt(ord(operation)*3%7+1,a*b,a/b,a-b,a+b)=c saves a byte. Nov 25 '20 at 0:09
• @Arnauld Very nice! Nov 25 '20 at 2:36
• I think where elt(ord(operation)/2-20,a*b,a+b,a-b,a/b)=c saves a byte further, abusing elt's behavior on half-integers (it rounds them up before indexing). Nov 25 '20 at 2:50
• @Bubbler that's extremely clever. I would not have guesses such a simple task would have so much golf potential! Nov 25 '20 at 3:17
• @ElPedro Normally I'd agree but the post says it's scored after removing spaces so I left them in for clarity. Nov 25 '20 at 22:32

# MySQL, 82 74 bytes

select * from expressions
where
elt(locate(operation,'+-/*'),a+b,a-b,a/b,a*b)=c


output

a           b           operation c
----------- ----------- --------- -----------
2           3           +         5
8           5           -         3


Explanation

ELT() returns the Nth element of the list of strings (the expressions are evaluated and converted to strings)

LOCATE Return the position of the first occurrence of substring

• Welcome to the site, and nice first answer! Nov 24 '20 at 22:36
• I think using LOCATE is shorter than using FIELD: locate(operation,'+-/*'). Nov 24 '20 at 23:37
• It's really amazing to know how you'd used elt!! Also, locate would be shorter, as suggested by @FryAmTheEggman. Nov 25 '20 at 3:29
• @FryAmTheEggman thanks for the hint - updated Nov 25 '20 at 8:38

# Score 32

Solution using a database view

select a,b,operation
from e
where c=r


Unfortunately a select * is not possible as the view contains an extra column with the correct result.

Requires a view creation in the database

create or replace view e as
select
a,b,operation,c,
elt(field(operation,'+','-','/','*'),a+b,a-b,a/b,a*b) r
from  expressions

• why doesn't the view count in the score? Nov 25 '20 at 17:02
• The view is defined in the database - not part of the query (= program) @user253751 create table and insert + commitalso do not count here;) Nov 25 '20 at 17:18
• It seems a bit like answering every challenge with "first, define v as the list of prime numbers where the last 3 digits form a square [or whatever the challenge is]". Then, print(v). 8 bytes. Nov 25 '20 at 17:34
• Nope @user253751 this is a database chalange and a database view is a sort of module or library in a programming language. In your 8 bytes you also do not count the bytes required to implement the print function. But I have no problem if this solution is not accepted. I've only posted it because IMO a) it can't be topped and b) this is the way how in the database you attack the problems. Nov 25 '20 at 21:01
• Judging by the Rextester link by OP, you can't change the code before the submission section (you're supposed to start with one table and no views), so you should include the view definition in the submission (and therefore in the score). And the fact that "the whole code is the body of the function defined with CREATE PROCEDURE" only makes it worse. Nov 26 '20 at 9:21