Difference in Boolean condition generated SQL between 5.x and 6.5.3.Final for equal operator causes MySQL query not using indexed

Hibernate ORM
1

I have noticed a difference in the generated SQL for the “=” (equal) operator when using Boolean Java between the 5.x and 6.5.3.Final versions, which is causing issues with MySQL not utilizing indexes properly.

For instance, in the 5.x version, the SQL generated is: “SELECT * FROM sampleTable WHERE colBoolean = 1”

However, in the 6.5.3.Final version, the generated SQL changes to: “SELECT * FROM sampleTable WHERE colBoolean” This implies a comparison without using the “=” operator, which is necessary for the optimizer to recognize the index.

2

What index are we talking about here? I’m surprised that if the expression colBoolean evaluates to true, MySQL would treat it differently than an explicit check with = 1. Could you please share the column definition for said table and the version of MySQL which you are using? Thanks.

3

The index is for the colBoolean column, which is of type DECIMAL(1,0) in the database. In Java, the type for colBoolean is Boolean. I am using version 8.x of MySQL. Thank you in advance.

4

Please paste the output of these statements:

EXPLAIN SELECT * FROM sampleTable WHERE colBoolean;

EXPLAIN SELECT * FROM sampleTable WHERE colBoolean IS TRUE;

EXPLAIN SELECT * FROM sampleTable WHERE colBoolean = 1;

Does the result change if the colBoolean is defined using the bool (alias of tinyint(1)) column type instead?

5

below is EXPLAIN result.

image
image999×459 13.9 KB

If colBoolean is defined with the tinyint(1) type, the result of EXPLAIN will remain unchanged.

6

Right, so it looks like MySQL does indeed treat bool (i.e. tinyint(1)) specially when it comes to boolean predicates. At this point, I would suggest either:

  1. migrating your schema to use the indicated type;
  2. changing your mappings to be either explicitly an int property with a @Column(length = 1), but that would mean also changing the logic of your application. Another, possibly less impacting route, is to use a @Convert annotation and keep the column as boolean, but having the converter explicitly transform true / false values to 0 and 1 .
7

Thank you for your assistance. The @Convert annotation with the NumericBooleanConverter functions effectively. The generated SQL correctly includes = 1.