List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:April 12 2001 10:30am
Subject:col_name(length) index and INNOBASE problem
View as plain text  
Bonjour,

  We  found a bug in INNOBASE tables with col_name(length) indexes.

  Here is the table :

create table test (
  symbole char(20) not null primary key,
  libelle char(100) not null,
  index id_lib (libelle(1))
) TYPE=INNOBASE;


  For  the test I insert one row but there can be many, the result is
  the same :

insert into test values('AAAAA','ABCDEF');

  I check that the row is in the table :

mysql> select * from test;
+---------+---------+
| symbole | libelle |
+---------+---------+
| AAAAA   | ABCDEF  |
+---------+---------+
1 row in set (0.00 sec)

  I check the indexes in the table :

mysql> show index from test;
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality |
Sub_part | Packed | Comment |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+---------+
| test  |          0 | PRIMARY  |            1 | symbole      | A         |        NULL | 
   NULL | NULL   |         |
| test  |          1 | id_lib   |            1 | libelle      | A         |        NULL | 
      1 | NULL   |         |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.00 sec)

  But here is the problem :

mysql> select * from test where libelle like 'A%';
Empty set (0.00 sec)
  
mysql> select * from test where libelle like 'a%';
Empty set (0.00 sec)


  This bug does not occur with a MyISAM table.

  We  use  Myslq-3.23.26  with latest Solaris patch from Heikki, under
  Solaris 2.8 and SPARC 4 CPU.

  Regards,
  Alex.


Thread
MyODBC, NT 4.0 WS, and Access 2000Fenn Rider11 Apr
  • Re: MyODBC, NT 4.0 WS, and Access 2000Adrian D'Costa12 Apr
    • col_name(length) index and INNOBASE problemBAUMEISTER Alexandre12 Apr
      • Re: col_name(length) index and INNOBASE problemMiguel Angel Sol√≥rzano12 Apr
    • Weird Index usage n 3.23.36Artem Koutchine12 Apr
      • Re: Weird Index usage n 3.23.36Sinisa Milivojevic12 Apr