vendredi 27 mai 2016

Why does outputting bits inside of an "if" or "when" always output 0? MySql 5.7

I've been working with some bit columns. I have to select one column or another based on a third column. That is not my question. Here's a simplified example to show what I've been encountering.

CREATE TABLE Testing (
ColType TINYINT(6),
Type1 BIT(1) DEFAULT b'0',
Type2 BIT(1) DEFAULT b'0'
);

Note that ColType is usually joined in via foreign key in the "real world" scenario.

INSERT INTO Testing
(ColType, Type1, Type2)
VALUES
(1, 0, 0),
(1, 0, 1),
(1, 1, 0),
(1, 1, 1),
(2, 0, 0),
(2, 0, 1),
(2, 1, 0),
(2, 1, 1);

This creates all the possible combinations of the 3 columns. The following is the queries I've run using this data.

SELECT 
*,
IF(ColType = 1, Type1, Type2) Output1,
(CASE WHEN ColType = 1 THEN Type1 ELSE Type2 END) Output2,
IF(ColType = 1, CAST(Type1 AS SIGNED), CAST(Type2 AS SIGNED)) Output3,
IF(ColType = 1, Type1=1, Type2=1) Output4
FROM
Testing;

CREATE TEMPORARY TABLE Describer
SELECT
*,
IF(ColType = 1, Type1, Type2) Output1,
(CASE WHEN ColType = 1 THEN Type1 ELSE Type2 END) Output2,
IF(ColType = 2, CAST(Type1 AS SIGNED), CAST(Type2 AS SIGNED)) Output3,
IF(ColType = 2, Type1=1, Type2=1) Output4
FROM
Testing;

DESCRIBE Describer;
SELECT * FROM Describer;

The results are:

ColType  Type1  Type2  Output1  Output2  Output3  Output4
1        0      0      0        0        0        0
1        0      1      0        0        0        0
1        1      0      0        0        1        1
1        1      1      0        0        1        1
2        0      0      0        0        0        0
2        0      1      0        0        1        1
2        1      0      0        0        0        0
2        1      1      0        0        1        1
----------
Field    Type            Null  Key  Default Extra
ColType  tinyint(6)      YES
Type1    bit(1)          YES        b'0'
Output1  int(1) unsigned YES        b'1'
Output2  int(1) unsigned YES        NULL
Output3  int(1)          YES        NULL
Output4  int(1)          YES        NULL
----------
ColType  Type1  Type2  Output1  Output2  Output3  Output4
1        0      0      0        0        0        0
1        0      1      0        0        0        0
1        1      0      1        1        1        1
1        1      1      1        1        1        1
2        0      0      0        0        0        0
2        0      1      1        1        1        1
2        1      0      0        0        0        0
2        1      1      1        1        1        1

My question is: why do certain versions of this query work and others not? I would expect the temp table to be holding the same values as shown in the first select, but that's clearly not the case. So why is there all of these mismatches of data?

Aucun commentaire:

Enregistrer un commentaire