|
DISTINCT, as in SELECT DISTINCT c1 from t1 is currently a binary compare in Columnstore. It needs to be utf and case insensitive (for collations that are case insensitve).
SELECT DISTINCT
This script demonstrates the problem:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES ('a'),('b'),('A'),('B');
|
SELECT DISTINCT a FROM t1;
|
+------+
|
| a |
|
+------+
|
| a |
|
| b |
|
| A |
|
| B |
|
+------+
|
Notice, all four rows returned.
The expected result should consist of two rows only:
+------+
|
| a |
|
+------+
|
| a |
|
| b |
|
+------+
|
SELECT COUNT(DISTINCT)
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES ('a'),('b'),('A'),('B');
|
SELECT COUNT(DISTINCT a) FROM t1;
|
+-------------------+
|
| COUNT(DISTINCT a) |
|
+-------------------+
|
| 4 |
|
+-------------------+
|
Looks wrong. The expected result is 2.
SELECT..GROUP BY
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1) ENGINE=ColumnStore;
|
INSERT INTO t1 VALUES ('a'),('b'),('A'),('B');
|
SELECT a FROM t1 GROUP BY a;
|
+------+
|
| a |
|
+------+
|
| A |
|
| a |
|
| B |
|
| b |
|
+------+
|
Looks wrong. The expected result should contain only two rows.
|