2

I have made a java project, and use H2 for first time to test my program. I am using H2 to run my Junit & cucumber test.

I have a query which get data from few tables.

SELECT film.TITLE, film.DIRECTOR, cinema.LOCATION, cinema.THEATER_NAME, SUM(income.NUMBER_VIEW) 
FROM DRFGIRF7B_OPE.MOVIE as film
INNER JOIN DRFGIRF7B_OPE.MOVIE_THEATER as income ON film.FILM_ID = income.FILM_ID 
INNER JOIN DRFGIRF7B_OPE.THEATER as cinema ON income.THEATER_ID = cinema.THEATER_ID 
WHERE film.COUNTRY_COD = 'FRA'
GROUP BY film.TITLE, film.DIRECTOR, cinema.LOCATION;

Here, "cinema.THEATER_NAME" is missing of GROUP BY statement. When I execute this query in a SQL IDE, I have this error message (which was expected) : 

SQL Failed : Selected non-aggregate values must be part of the assiociated group

But a soon as I am running my test with Junit, H2 does not detect this issue, and all my tests pass, whereas the query is called.

Test Class

Query

Results of Test

Do you have an idea, why my test does not fail ?

Chaveex
  • 31
  • 3
  • Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer –  Feb 02 '18 at 10:44
  • Another good example why testing with a different DBMS than the one in production doesn't make sense –  Feb 02 '18 at 10:45
  • In the first part of the journey, I did not know about it. I was looking at all fonctionnalities There was bold option, Italic option. No, I didn't find the way to put coloration to my code when writing this post (it's one of my first post, I will know it now) Thank you for the post about picture I was not able to do differently or Maybe but didn't know how to do it – Chaveex Feb 08 '18 at 13:14

2 Answers2

2

Different database vendors do things differently. For example MySQL and SQLite decided to go against the specification and most other databases and allow any column in select when group by is used(one of the values will be used).

H2 doesn't document that it goes against the standard and based on the error message:

The error with code 90016 is thrown when a column was used in the expression list or the order by clause of a group or aggregate query, and that column is not in the GROUP BY clause.

Your query shouldn't be allowed and your test should fail.

However what actually happens is that when the query returns only one value for the column that is not in group by H2 allows it:

create table test(a int, b int);

insert into test values(1,1);
insert into test values(1,1);

select a,b from test group by a; -- output 1,1

When there is more than one value, it fails.

insert into test values(1,1);
insert into test values(1,2);

select a,b from test group by a; -- Error: Column "B" must be in the GROUP BY list

This looks like a bug and should be reported (I will probably do it myself in the near future if no one else will).

The problem you're having is a perfect example of why using different databases for testing and production is a terrible idea.

Oleg
  • 6,124
  • 2
  • 23
  • 40
  • Thank you for your nice answer. – Chaveex Feb 06 '18 at 16:43
  • Thank you for your nice Answer. Unfortunately, our Jenkins does have access to the databse (Teradata). So we decided to use an embedded database (H2) to run our test. We currently know that it is not a best practice to use different database in test and production. What would you have done if you were in our case ? Maybe you can give us some advice to change our way to test. Our prod database is Teradata, and we need 80% of coverage min to put in producton. – Chaveex Feb 06 '18 at 17:01
0

Probably the database you're using is more restrictive to those queries.

If you want to get closer to reality in your tests, try to configure your H2 connection like this:

http://www.h2database.com/html/tutorial.html#using_hibernate

fabioresner
  • 915
  • 14
  • 21
  • Thank you for your answer, I do not use Hibernate, and the patch provide by H2 website is blocked by my company proxy.. I think you are right about the restriction of H2, but I'm not able to correct it with this patch, I am missing something ? – Chaveex Feb 02 '18 at 13:31