Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.4.3
Description
Long VARCHAR:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_nopad_ci) ENGINE=ColumnStore; |
INSERT INTO t1 VALUES ('a'); |
INSERT INTO t1 VALUES ('ã'); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
SELECT quote(a) FROM t1 WHERE a<='a '; |
+----------+
|
| quote(a) |
|
+----------+
|
| 'a' |
|
+----------+
|
Looks wrong. The condition should match all records.
Short VARCHAR:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_general_nopad_ci) ENGINE=ColumnStore; |
INSERT INTO t1 VALUES ('a'); |
INSERT INTO t1 VALUES ('ã'); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
SELECT quote(a) FROM t1 WHERE a<='a '; |
+----------+
|
| quote(a) |
|
+----------+
|
| 'a' |
|
| 'a ' |
|
| 'a ' |
|
+----------+
|
It returns a different result set, but it is still wrong.
With other engines it works as expected:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_nopad_ci) ENGINE=MyISAM; |
INSERT INTO t1 VALUES ('a'); |
INSERT INTO t1 VALUES ('ã'); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
SELECT quote(a) FROM t1 WHERE a<='a '; |
+----------+
|
| quote(a) |
|
+----------+
|
| 'a' |
|
| 'ã' |
|
| 'a ' |
|
| 'ã ' |
|
| 'a ' |
|
| 'ã ' |
|
+----------+
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_general_nopad_ci) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('a'); |
INSERT INTO t1 VALUES ('ã'); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
INSERT INTO t1 VALUES ('a '); |
INSERT INTO t1 VALUES ('ã '); |
SELECT quote(a) FROM t1 WHERE a<='a '; |
+----------+
|
| quote(a) |
|
+----------+
|
| 'a' |
|
| 'ã' |
|
| 'a ' |
|
| 'ã ' |
|
| 'a ' |
|
| 'ã ' |
|
+----------+
|
Attachments
Issue Links
- is blocked by
-
MCOL-4422 Remove mariadb.h and my_sys.h dependency from collation.h
- Closed
- relates to
-
MCOL-495 Make string comparison not case sensitive
- Closed
-
MCOL-4064 Make JOIN collation aware
- Closed
-
MCOL-4498 LIKE is not collation aware
- Closed
-
MCOL-4065 DISTINCT is case sensitive
- Closed
-
MCOL-4388 Equality does not respect the NOPAD collation attribute
- Closed
-
MCOL-4428 WHERE char6col <='STR' OR char6col IS NULL -- returns a wrong result after MCOL-495
- Closed
-
MCOL-4539 WHERE short_char_column='literal' ignores the collation on a huge table
- Closed