MySQL v8 COLLATE BUG

mysql_collate_bug

#!/bin/bash

# MySQL Docker image details
MYSQL_IMAGE="mysql:8.0.30"
MYSQL_ROOT_PASSWORD="your_root_password"

# MySQL database details
MYSQL_DATABASE="test"
MYSQL_USER="root"

# SQL commands
SQL_COMMANDS=$(cat <<EOF

CREATE TABLE IF NOT EXISTS bad ( col varchar(512) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE TABLE IF NOT EXISTS good ( col varchar(512) );
ALTER TABLE good CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

INSERT INTO good (col) VALUES ('tag_1'), ('tag_4'), ('tag.x');
INSERT INTO bad (col) VALUES ('tag_1'), ('tag_4'), ('tag.x');
EOF
)

# Create a temporary container to run MySQL
docker run --name temp-mysql-container -e MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD -d $MYSQL_IMAGE > /dev/null

# Active waiting for MySQL to start up
echo "Waiting for MySQL to start up..."
until docker exec temp-mysql-container mysql -u$MYSQL_USER -p$MYSQL_ROOT_PASSWORD -e "SELECT 1" &> /dev/null; do
    echo -n "."
    sleep 1
done
printf "\nMySQL started.\n"

# Create the database
docker exec temp-mysql-container mysql -u$MYSQL_USER -p$MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS $MYSQL_DATABASE;" 2>&1 | grep -v "Using a password"

# Run the SQL commands
docker exec -i temp-mysql-container mysql -u$MYSQL_USER -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE <<< "$SQL_COMMANDS" 2>&1 | grep -v "Using a password"

GOOD_QUERY="SELECT col FROM good GROUP BY col ORDER BY col DESC;"
BAD_QUERY="SELECT col FROM bad GROUP BY col ORDER BY col DESC;"

printf "\n> $GOOD_QUERY (produces the expected result)"
docker exec -i temp-mysql-container mysql -u$MYSQL_USER -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE -e "$GOOD_QUERY" 2>&1 | grep -v "Using a password"
printf "\n> $BAD_QUERY (ignores the COLLATE clause and produces the wrong sorting order)"
docker exec -i temp-mysql-container mysql -u$MYSQL_USER -p$MYSQL_ROOT_PASSWORD $MYSQL_DATABASE -e "$BAD_QUERY" 2>&1 | grep -v "Using a password"

# Clean up: Stop and remove the temporary container
docker stop temp-mysql-container > /dev/null
docker rm temp-mysql-container > /dev/null

echo "Script execution completed."