| Bug #11347 | comparison is not working in ndb of mysql-5.0.6-beta | ||
|---|---|---|---|
| Submitted: | 15 Jun 2005 10:00 | Modified: | 16 Jun 2005 12:29 |
| Reporter: | Carl Humphrey | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | mysql-5.0.6-beta | OS: | FreeBSD (freebsd 5.4 amd64) |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
[16 Jun 2005 12:29]
Martin Skold
Cannot reproduce. Did you build mysql yourself from source? Is your FreeBSD running in 64-bit mode? Does mysql-test-run run with no problems? I tried the following on 5.0.7 and x86/RH 9, AMD64/Suse 9.2/64-bit, x86/FreeBSD 4.7: (please check it is what you have problem with) create table party_relationship (party_relationship_id int(11) primary key auto_increment, parent_party_role_id int(11) default 0, child_party_role_id int(11) default 0, parent_party_id int(11) default 0, child_party_id int(11) default 0,start datetime default '0000-00-00 00:00:00', finish datetime default '0000-00-00 00:00:00', type_enum_id int(11) default 0, flags varchar(254) default NULL, value varchar(254) default NULL, comment varchar(254) default NULL) engine = ndbcluster; Query OK, 0 rows affected (0.73 sec) mysql> mysql> insert into party_relationship (type_enum_id, child_party_id) values(11030, 1163300), (11030, 512217), (11030, 3),(11030, 7),(11030, 512210), (11030, 13),(11030, 1217244), (11030, 1217247), (11030, 10); Query OK, 9 rows affected (0.07 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> create index x1 on party_relationship(type_enum_id); Query OK, 9 rows affected (1.91 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> create index x2 on party_relationship(child_party_id); Query OK, 9 rows affected (2.45 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.04 sec) mysql> mysql> drop index x1 on party_relationship; Query OK, 9 rows affected (2.37 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> drop index x2 on party_relationship; Query OK, 9 rows affected (1.78 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> create index x3 on party_relationship(type_enum_id, child_party_id); Query OK, 9 rows affected (1.88 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.03 sec) mysql> mysql> set engine_condition_pushdown=on; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.04 sec) mysql> mysql> drop index x3 on party_relationship; Query OK, 9 rows affected (1.77 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.03 sec) mysql> mysql> drop table party_relationship; Query OK, 0 rows affected (0.47 sec)
Description: this: works in mysql 4.1 innodb and ndb , and works in 5.0 innodb but NOT in ndb SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; heres the info you need mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 1163300 | | 11030 | 512217 | | 11030 | 3 | | 11030 | 7 | | 11030 | 512210 | | 11030 | 13 | | 11030 | 1217244 | | 11030 | 1217247 | | 11030 | 10 | +--------------+----------------+ 9 rows in set (0.00 sec) mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; Empty set (0.01 sec) The odd thing is that the <= and the >= both work mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id <= 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.00 sec) mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id >= 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.00 sec) Lastly heres the table definition mysql> describe party_relationship -> ; +-----------------------+--------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------------------+-------+ | party_relationship_id | int(11) | NO | PRI | 0 | | | parent_party_role_id | int(11) | NO | MUL | 0 | | | child_party_role_id | int(11) | NO | MUL | 0 | | | parent_party_id | int(11) | NO | MUL | 0 | | | child_party_id | int(11) | NO | MUL | 0 | | | start | datetime | NO | MUL | 0000-00-00 00:00:00 | | | finish | datetime | NO | MUL | 0000-00-00 00:00:00 | | | type_enum_id | int(11) | NO | MUL | 0 | | | flags | varchar(254) | YES | | NULL | | | value | varchar(254) | YES | | NULL | | | comment | varchar(254) | YES | | NULL | | +-----------------------+--------------+------+-----+---------------------+-------+ 11 rows in set (0.00 sec) If i SWITCH into innodb with the SAME DATA and run the SAME QUERY Database changed mysql> SELECT type_enum_id, child_party_id FROM party_relationship WHERE type_enum_id = 11030 and child_party_id = 3; +--------------+----------------+ | type_enum_id | child_party_id | +--------------+----------------+ | 11030 | 3 | +--------------+----------------+ 1 row in set (0.00 sec) Much Thanks. Carl. How to repeat: SELECT * FROM atable WHERE column1 = 3 and column2 = 14;