EXPLAIN

SELECT COUNT(*)FROM dig_lotes WHERE lote_tipo='r' AND sit_spa!='mov'  

EXPLAIN

SELECT COUNT(*)FROM dig_lotes WHERE lote_tipo='r' AND sit_spa IN ('','d','s','p','a')

column 'sit_spa' is ENUM('d','s','p','a','mov') NOT NULL DEFAULT 'd'

WHERE a != 10

WHERE a < 10 OR a > 10

  /* 

	MDEV-4419

	IMPOSSIBLE WHERE FOR ENUM FIELDS / REWRITE OF ENUM FIELDS OPERATOR

	any enum_field OP const_value should be executed in all enum values,

	to optimize the query, since ENUM is a 'bitmap' of all possible values

	we can rewrited it to IN/NOT IN operator, this allow index usage

	examples:

	for NULL possible fields:

		a ENUM ('a','b','c')

		internally we have: NULL,0=>'',1=>'a',2=>'b',3=>'c'

	for NOT NULL fields:

		a ENUM ('a','b','c') NOT NULL

		internally we have: 0=>'',1=>'a',2=>'b',3=>'c'

	example 1) (impossible where)

	WHERE a = 'd'

	'' = 'd'  ? false

	'a' = 'd' ? false

	'b' = 'd' ? false

	'c' = 'd' ? false

	rewrite to  

	(1=0) - always false, impossble where

	example 2) (allways true)

	a < 'd' will result:

	'' < 'd'  ? true

	'a' < 'd' ? true

	'b' < 'd' ? true

	'c' < 'd' ? true

	rewriting to :

	a IN ('','a','b','c') - always true

	example 3) (some values of enum, using IN)

	a LIKE '%a' will result:

	'' LIKE '%a'  ? false

	'a' LIKE '%a' ? true

	'b' LIKE '%a' ? false

	'c' LIKE '%a' ? false

	we have 1 true, and 3 falses, better use IN ()

	rewriting to :

	using IN:     a IN ('a')

	using NOT IN: a NOT IN ('','b','c')

	or using ENUM INDEX:

	using IN:     a IN (1)

	using NOT IN: a NOT IN (0,2,3)

	example 4) (some values of enum, using NOT IN)

	a != 'b' will result:

	'' != 'b'  ? true

	'a' != 'b' ? true

	'b' != 'b' ? false

	'c' != 'b' ? true

	we have 3 true, and 1 false, better use NOT IN ()

	rewriting to:

	using IN:     a IN ('','a','c')

	using NOT IN: a NOT IN ('b')

	or using ENUM INDEX:

	using IN:     a IN (0,1,3)

	using NOT IN: a NOT IN (2)

  */

  if (field->real_type() == MYSQL_TYPE_ENUM)

  {

    uint count_true_list =0;

    uint count_false_list=0;

    bool true_false_list[];

    uint enum_field_counter=0;

    char buffer[255];

    String enum_item(buffer, sizeof(buffer), res.charset());

    /* seek all values and check how many true/false we got */

    resize_true_false_list[]

    set true_false_list to false;

    /* true_false_list start from 0 to enum values +1, NULL = index 0, */

    uint *len= field->typelib->type_lengths;

    for (enum_field_counter=0,const char **pos= field->typelib->type_names; *pos; pos++, len++, enum_field_counter++)

    {

      enum_string_value= enum_item.copy(*pos, *len, charset(), res.charset(), &dummy_errors);

      if (type == Item_func::IN_FUNC)

      {

	/* search in each IN value */

	while( .. in_values .. )

	{

  	  if ((enum_str_value == in_str_value && in_value_is_string && compare_function(enum_str,in_str)) || 

              (enum_int_value == in_int_value && !in_value_is_string && compare_function(enum_int,in_int))) {

	    count_true_list ++;

	    true_false_list[enum_field_counter]=true;

 	  } else {

	    count_false_list ++;

	  }

	}

      } else if (type == Item_func::BETWEEN) {

        /* two args */

        // don't know what to do...

        goto end;

      } else {

        /* only one value */

  	if ((enum_str_value == str_value && value_is_string && compare_function(enum_str,value_str)) || 

              (enum_int_value == int_value && !value_is_string && compare_function(enum_int,value_int))) {

	  count_true_list ++;

	  true_false_list[enum_field_counter]=true;

	} else {

	  count_false_list ++;

	}

      }

    }

    /* check about always true (discart operation) / false (impossible where) */

    if (count_false_list == 0)

    {

      tree= &null_element;	/* always true */

      goto end;

    }

    if (count_true_list == 0)

    {

      tree->type= SEL_ARG::IMPOSSIBLE;	/* impossible where */

      goto end;

    }

    /* rewrite the operation using IN / NOT IN () */

    if (count_false_list > count_true_list)

	inv=true; /* add NOT before IN () */

    type=Item_func::IN_FUNC;

    for each true_false_list

    {

      if(inv)  

        add respective enum_value of true_false_list when it's false

      else

        add respective enum_value of true_false_list when it's true

    }

    goto end;

  }