MySQL Forums
Forum List  »  InnoDB

Subquery in SELECT statement returns wrong(?) data type
Posted by: Roman Ovseitsev
Date: September 19, 2014 11:57PM

Hi,

I am having an issue with a subquery in SELECT statement returning unexpected data type.

Consider the following example:

CREATE TABLE foo (
id INT(11) NOT NULL AUTO_INCREMENT,
a BIT(1) NOT NULL,
PRIMARY KEY (id)
)
ENGINE=InnoDB;

CREATE TABLE bar (
id INT(11) NOT NULL AUTO_INCREMENT,
b BIT(1) NULL,
PRIMARY KEY (id)
)
ENGINE=InnoDB;

CREATE TEMPORARY TABLE temp
SELECT foo.a AS result_a, (SELECT bar.b FROM bar WHERE bar.id = foo.id LIMIT 1) AS result_b
FROM foo
LIMIT 0;
DESCRIBE temp;

+----------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+-------+
| result_a | bit(1) | NO | | NULL | |
| result_b | int(1) unsigned | YES | | NULL | |
+----------+-----------------+------+-----+---------+-------+


As can be seen from the output, result_b is an unsigned integer instead of an expected(?) BIT(1).

Generaly this wouldn't be an issue. However, since we are using Entity Framework which doesn't like mapping unsigned integers to 'bool' properties, this brings all sorts of problems. Basically bar.b is always mapped to 'true' on the code level no matter of the actual underlying value.


My question though is not about the EF, but rather why the returned datatype doesn't match the original one? Is this an expected behaviour?


As a workaround, we solved this simply by casting to a signed integer CAST(bar.b AS SIGNED INTEGER) [I suppose IF(bar.b=0,0,1) should work too].

Options: ReplyQuote


Subject
Views
Written By
Posted
Subquery in SELECT statement returns wrong(?) data type
2383
September 19, 2014 11:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.