Description:
I get a wrong result set when having a query with a
qualification for the PRIMARY KEY of type DECIMAL.
How to repeat:
testcase derived from NIST tests: cdr001
snip of my protocol:
# GRADE BIGINT instead of DECIMAL is harmless
CREATE TABLE t1 (EMPNUM CHAR(3) NOT NULL,
EMPNAME CHAR(20) DEFAULT NULL,
GRADE DECIMAL(4) NOT NULL ,
CITY CHAR(15), PRIMARY KEY (GRADE))
ENGINE = Falcon;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`EMPNUM` char(3) NOT NULL,
`EMPNAME` char(20) DEFAULT NULL,
`GRADE` decimal(4,0) NOT NULL,
`CITY` char(15) DEFAULT NULL,
PRIMARY KEY (`GRADE`)
) ENGINE=Falcon DEFAULT CHARSET=latin1
COMMIT WORK;
INSERT INTO t1 (EMPNUM,EMPNAME,GRADE)
VALUES ('E3','Bill',151);
SELECT * FROM t1;
EMPNUM EMPNAME GRADE CITY
E3 Bill 151 NULL
# !!! I miss the record within the next result set.
SELECT * FROM t1 WHERE GRADE= 151;
EMPNUM EMPNAME GRADE CITY
#-------------------------------------------------
# Removing the PRIMARY KEY removes the problem
ALTER TABLE t1 DROP PRIMARY KEY;
SELECT * FROM t1;
EMPNUM EMPNAME GRADE CITY
E3 Bill 151 NULL
SELECT * FROM t1 WHERE GRADE= 151;
EMPNUM EMPNAME GRADE CITY
E3 Bill 151 NULL
Description: I get a wrong result set when having a query with a qualification for the PRIMARY KEY of type DECIMAL. How to repeat: testcase derived from NIST tests: cdr001 snip of my protocol: # GRADE BIGINT instead of DECIMAL is harmless CREATE TABLE t1 (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20) DEFAULT NULL, GRADE DECIMAL(4) NOT NULL , CITY CHAR(15), PRIMARY KEY (GRADE)) ENGINE = Falcon; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `EMPNUM` char(3) NOT NULL, `EMPNAME` char(20) DEFAULT NULL, `GRADE` decimal(4,0) NOT NULL, `CITY` char(15) DEFAULT NULL, PRIMARY KEY (`GRADE`) ) ENGINE=Falcon DEFAULT CHARSET=latin1 COMMIT WORK; INSERT INTO t1 (EMPNUM,EMPNAME,GRADE) VALUES ('E3','Bill',151); SELECT * FROM t1; EMPNUM EMPNAME GRADE CITY E3 Bill 151 NULL # !!! I miss the record within the next result set. SELECT * FROM t1 WHERE GRADE= 151; EMPNUM EMPNAME GRADE CITY #------------------------------------------------- # Removing the PRIMARY KEY removes the problem ALTER TABLE t1 DROP PRIMARY KEY; SELECT * FROM t1; EMPNUM EMPNAME GRADE CITY E3 Bill 151 NULL SELECT * FROM t1 WHERE GRADE= 151; EMPNUM EMPNAME GRADE CITY E3 Bill 151 NULL