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