Query doesn't work correctly

Help
1

When changing the value of boolean field in the table of memsql, the count query doesn’t show results correctly,but when viewing the data in the table,everything is correct,after around 30-40 seconds,the count query works correctly and return correct results.Did any of you encountered this issue?

2

I haven’t observed anything like that. Some clarifying questions… Are you able to consistently reproduce the issue? Does it only occur with the boolean (which is the same as tinyint) data type? If you’re only changing/updating the field (which won’t affect counts overall), are you counting with a where clause for a specific value, or how are you performing a count? Are you changing one record or several at a time? Is the table rowstore, columnstore, and/or reference?

1 Like
3

Yes it’s reproducable.
Generate a table like this
CREATE TABLE test (
Id INT NOT NULL AUTO_INCREMENT,
iswriting BIT(1) DEFAULT ‘0’,
PRIMARY KEY (Id)
);

Insert row in there, and change the iswriting field,and then try to run this query
select count(*) from test where IsWriting=‘True’; when you set it true,it doesn’t work correctly ,but the field value is set to True.

5

I think that you mean to use data type BOOL, BOOLEAN, or TINYINT (they are synonymous). The BIT data type is not a single bit or byte. See the MemSQL data types here.

I think the other issue is that you’re using strings with an integer data type. BIT(1) DEFAULT ‘0’ defaults to the ASCII decimal value of the 0 character, which is 48, not 0. It should just be DEFAULT FALSE or DEFAULT 0 (no quotes). Likewise, IsWriting=‘True’ is filtering on the decimal value of the string ‘True’. It should be IsWriting=TRUE (no quotes) or IsWriting=1.

Try SELECT * FROM test to see what’s actually in your table.

The following works and returns a count of 3 as expected.

CREATE TABLE test (
Id INT NOT NULL AUTO_INCREMENT,
iswriting BOOL DEFAULT FALSE,
PRIMARY KEY (Id)
);

INSERT INTO test VALUES(null,TRUE);
INSERT INTO test VALUES(null,TRUE);
INSERT INTO test VALUES(null,1);
INSERT INTO test VALUES(null,FALSE);
INSERT INTO test VALUES(null,0);

SELECT COUNT(*) FROM test WHERE iswriting = TRUE;

6

Thank you for your reply,will try to do that.

1 Like
7

Yeah, as @bvincent said the BIT datatype doesn’t store a boolean - it actually stores multiple bits (binary data), not a single bit - see https://dev.mysql.com/doc/refman/8.0/en/bit-type.html for a description.

1 Like