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:
None 
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

[15 Jun 2005 10:00] Carl Humphrey
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;
[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)