[12.0.181 Pro] - Q: Unit Testing

[12.0.181 Pro] - Q: Unit Testing

There are 5 distinct methods for result validation in Unit Testing.

#1 Do not check results, only check for errors
#2 Check first value returned, fail test if it doesn't match the condition
#3 Check full result returned, fail test if it doesn't match the condition
#4 Validate using SQL script, fail test if exception raised
#5 Four different options for validating results using custom Pascal, Visual Basic, C++, or

#1 is plain and simple, as is #2.

#3 Occasionally I have issues with this one. I run the query in the Execute tab and then copy the result set from the Result pane and when I try to paste the stuff on the clipboard to the expected value field, it sometimes refuses to accept data from the clipboard. Nothing happens. Sometimes clicking the input field does not even make it active, as if it was it was disabled completely. I also have a question regarding #3. Am I correct when I say #3 can compare one single result set and it is unable to compare multiple results sets some stored procedures might return?

#4 works like a charm in SQL Server. On the other hand, things get complicated when trying the same in MariaDB. This method requires raising an exception to fail the test. That's easily done in SQL Server and I can also SIGNAL in MariaDB, but there are no compound statements that can be used outside of stored programs (at least none before MariaDB 10.1.1), hence it either never throws an exception or it always does. Do you have any hints/tips/ideas for keeping #4 validation simple for MariaDB (besides moving the whole logic into a stored program)?

May I ask to capture screenshot of that dialog when "clicking the input field does not even make it active" if you are able to reproduce it.

For #3, can you create a function in the database with a name that would mean something to you and make it divide 1 by 0 to trigger an exception, and call this function conditionally SELECT test_xyz_failed('Some error text here') WHERE ... condition. Maybe this can help with faking an exception and capturing test_xyz_failed('Some error text here') text in the log - it should be visible in the error message.

	SysOp wrote:
	May I ask to capture screenshot of that dialog when "clicking the input field does not even make it active" if you are able to reproduce it.

Sure, I recorded the whole process, check this short video here. It first demonstrates how it works with a generic result set and then how the black hole wreaks havoc when having a "singlenullarity" in the result set.

When copying from the result set, if the result set consists of multiple rows/columns, the header is copied too. If it is only one row and one column only that value is copied, thus the stuff on the clipboard does not conform to the tabs separated table structure the expect value field expects, as it has a single value instead of the column name (header) + value row pair. The hit shits the fan when you have a result set that only has a single NULL column. This instantly kills the input field. Near the end of the video, you can see me copying such a result set and pasting that into the expected value field at first nothing happens but when I click the lines in that field they vanish, one by one. It kills any input field, I successfully reproduced the behavior in the SQL field on Execute tab. I guess the NULL column/value would mess with every input it is pasted into.

SysOp wrote:

For #3, can you create a function in the database with a name that would mean something to you and make it divide 1 by 0 to trigger an exception, and call this function conditionally SELECT test_xyz_failed('Some error text here') WHERE ... condition. Maybe this can help with faking an exception and capturing test_xyz_failed('Some error text here') text in the log - it should be visible in the error message.

This won't work. I'm on the fossilized 5.5 of MariaDB and dividing by 0 simply yields NULL there. You can set SQL_MODE to ERROR_FOR_DIVISION_BY_ZERO but its name is misleading, it only adds a warning to the crowd, it does not throw an exception. The only way I can make a query fail is to select from a non-existing table but since there is no IF...THEN outside stored programs, it would be unconditional, failing every time. I guess that leaves me with no other choice but to put everything into a stored procedure...

Maybe something like this would work for a conditional error handling?

	Code:
	SET sql_mode = strict_trans_tables; CREATE FUNCTION raise_error(err_msg VARCHAR(255)) RETURNS INTEGER DETERMINISTIC BEGIN DECLARE err INTEGER; SET err := err_msg; -- this is going to fail RETURN err; END

	Code:
	SELECT raise_error('This is an error') WHERE 1 = 1 UNION SELECT raise_error('This is not an error') WHERE 1 = 0

Or if the error message isn't really important, just need a method to trigger errors, it could be simplified to

	Code:
	SET sql_mode = strict_trans_tables; CREATE FUNCTION raise_error() RETURNS INTEGER DETERMINISTIC BEGIN DECLARE err INTEGER; SET err := 'error wanted here'; -- this is going to fail and we want it to fail RETURN err; END