[MDEV-24674] CREATE TABLE allows COLLATE in non-character-string data types Created: 2021-01-25  Updated: 2021-01-25

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description    

CREATE OR REPLACE TABLE t1 (a INT COLLATE latin1_bin);


Query OK, 0 rows affected (0.020 sec)

Looks wrong. COLLATE should be applicable only to character string data types:

  • CHAR
  • VARCHAR
  • TEXT (and its variants)
  • ENUM
  • SET

COLLATE with non-character-string data types should return an error.

Note, this includes binary strings. COLLATE with a binary string data type should also return an error:

  • BINARY
  • VARBINARY
  • BLOB (and its variants)

The best way to fix this is to add a new method into Type_handler, to test COLLATE clause applicability.

 Comments   
Comment by Elena Stepanova [ 2021-01-25 ]

I don't think we can realistically prohibit it, though.
Apparently it has always worked like that, still works even in MySQL 8.0, so I guess many existing applications use it, willingly or not.
Also, such CREATE is written as is into the binary log, so any OM => NS replication will break if NS starts prohibiting it.
There are probably more ways for it to break backward compatibility.

Generated at Thu Feb 08 09:31:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.