[Support Bulletin 09] - Trailing Space 에 따른 SQL 결과 차이

 

안녕하세요! 에이플랫폼 Support Bulletin의 아홉 번째 이야기입니다. 😊

오늘은 SingleStore에서 공백 하나가 SQL 결과를 어떻게 바꿀 수 있는지 알아보겠습니다.

 

---

Trailing Space란? 데이터 비교에서 중요한 이유

데이터를 다룰 때 공백(Whitespace)은 단순한 빈 공간처럼 보이지만, 때로는 결과에 중요한 영향을 미칠 수 있습니다.

특히 Trailing Space는 문자열 끝에 위치하는 공백을 의미하며, 데이터 저장이나 비교 시 예상치 못한 차이를 만들 수 있습니다.

Trailing Space의 예제

예를 들어, 아래 두 개의 문자열을 비교한다고 가정해 봅시다.

 

'hello' 
'hello '

 

사람이 볼 때는 같은 값처럼 보이지만, 일부 시스템에서는 이 둘을 다른 값으로 인식하거나 자동으로 공백을 제거할 수 있습니다.

어떤 데이터베이스에서는 'hello '를 'hello'와 동일하게 처리하고, 또 다른 데이터베이스에서는 서로 다른 값으로 인식합니다.

데이터 비교에 미치는 영향

Trailing Space가 어떻게 데이터 비교에 영향을 미치는지 살펴볼까요?

1️⃣ 문자열 비교에서 차이를 만들 수 있습니다.

• 데이터베이스나 프로그래밍 언어에 따라 'hello' == 'hello '의 결과가 다를 수 있습니다.
• SQL에서 SELECT DISTINCT를 사용할 때 일부 DBMS는 Trailing Space를 자동으로 제거하여 결과가 달라집니다.

2️⃣ 데이터 정합성 문제가 발생할 수 있습니다.

• 사용자 입력 데이터에서 공백이 포함될 경우 중복 데이터 처리나 검색 결과가 달라질 수 있습니다.
• 로그 데이터나 고정 길이 문자열을 다룰 때 Trailing Space가 중요할 수 있습니다.

3️⃣ DBMS마다 동작 방식이 다를 수 있습니다.

• 일부 데이터베이스는 문자열 비교 시 Trailing Space를 자동으로 제거합니다.
• 반면, Mysql이나 GPDB 같은 DBMS는 공백을 유지한 채 비교를 수행합니다.

이처럼 Trailing Space는 데이터 비교의 핵심 요소로 작용할 수 있으며, 이를 명확히 이해하고 처리하는 것이 중요합니다.

 

---

 

SingleStore vs. MySQL – Trailing Space 비교 및 차이 분석

이 예시에서는 SingleStore 와 MySQL의 문자열 비교 방식 차이를 확인하기 위해 동일한 SQL 쿼리를 실행하고 결과를 비교합니다.

SingleStore Server Version: 8.9.22

Mysql Version: 8.0.41

# 예시 쿼리
SELECT CONCAT('[', k, ']')  
FROM (  
    SELECT DISTINCT c1 AS k  
    FROM (  
        SELECT 'a   ' AS c1  
        UNION ALL  
        SELECT 'b ' AS c1  
        UNION ALL  
        SELECT 'a' AS c1  
        UNION ALL  
        SELECT ' a' AS c1  
    ) AS sub1  
) AS sub2;

Mysql 결과

Mysql의 DEFAULT_COLLATION_NAME은 utf8mb4_0900_ai_ci 이고

utf8mb4_0900_ai_ci 의 PAD_ATTRIBUTE는 NO PAD 입니다.

NO PAD 는 Trailing Space를 포함한 문자열을 서로 다른 값으로 인식 합니다.

NO PAD collations treat trailing spaces as significant in comparisons, like any other character.
출처: https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html#charset-binary-collations-trailing-space-comparisons

# 예시 쿼리 실행 결과
[a   ]
[b ]
[a]
[ a]
---
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;
>>>
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| mysql              | utf8mb4                    | utf8mb4_0900_ai_ci     |
| information_schema | utf8mb3                    | utf8mb3_general_ci     |
| performance_schema | utf8mb4                    | utf8mb4_0900_ai_ci     |
| sys                | utf8mb4                    | utf8mb4_0900_ai_ci     |
+--------------------+----------------------------+------------------------+
4 rows in set (0.00 sec)
---
SELECT COLLATION_NAME, PAD_ATTRIBUTE  
FROM information_schema.COLLATIONS  
WHERE COLLATION_NAME = 'utf8mb4_0900_ai_ci';
>>>
+--------------------+---------------+
| COLLATION_NAME     | PAD_ATTRIBUTE |
+--------------------+---------------+
| utf8mb4_0900_ai_ci | NO PAD        |
+--------------------+---------------+
1 row in set (0.00 sec)

SingleStore 결과

DISTINCT 적용 시 'a '와 'a'를 동일한 값으로 인식하고 하나로 통합되었습니다.

하지만 ' a'는 다른 값으로 인식하여 유지되었습니다.

Comparison operators ignore the trailing spaces at the end of the string being compared, except for the
LIKE operator.
출처: https://docs.singlestore.com/db/v8.9/reference/sql-reference/data-types/string-types/

# 예시 쿼리 실행 결과
[a   ]
[b ]
[ a]

---

 

SingleStore의 Trailing Space 상세 동작

SingleStore의 동작을 조금 더 자세히 알아보겠습니다.

먼저 아래와 같이 테스트 테이블을 생성 후 데이터를 넣어보겠습니다.

# 예시 테이블 생성 및 데이터 삽입
create table test(c1 varchar(10));
insert into test values ('a'),('a ');

# = 사용 예시 쿼리
SELECT CONCAT('[', a.c1, ']'), CONCAT('[', b.c1, ']')  
FROM test a, test b  
WHERE a.c1 = b.c1;
>>>
+------------------------+------------------------+
| CONCAT('[', a.c1, ']') | CONCAT('[', b.c1, ']') |
+------------------------+------------------------+
| [a]                    | [a ]                   |
| [a]                    | [a]                    |
| [a ]                   | [a ]                   |
| [a ]                   | [a]                    |
+------------------------+------------------------+
4 rows in set (0.11 sec)

# like 사용 예시 쿼리
select count(*) from test a, test b where a.c1 = b.c1;
SELECT CONCAT('[', a.c1, ']'), CONCAT('[', b.c1, ']')  
FROM test a, test b  
WHERE a.c1 like b.c1;
>>>
+------------------------+------------------------+
| CONCAT('[', a.c1, ']') | CONCAT('[', b.c1, ']') |
+------------------------+------------------------+
| [a ]                   | [a ]                   |
| [a]                    | [a]                    |
+------------------------+------------------------+
2 rows in set (0.05 sec)

 

앞서 설명 드렸던 SingleStore의 Trailing Space 동작과 같이 Like가 아닌 연산 ‘=’에서는 Trailing Space를 무시하여 ‘a’와 ‘a ’를 동일한 값으로 인식해 총 4개의 결과가 나왔습니다.

하지만 Like 연산에서는 Trailing Space를 인식하여 2개의 결과가 나왔습니다.

 

---

 

Trailing Space 처리 방식의 차이는 우리가 예상하지 못한 문제를 유발할 수 있습니다.

• 데이터 정합성 문제 (중복 데이터 발생 가능성)
• 검색 및 필터링 오류 (예상과 다른 쿼리 결과)
• 어플리케이션 로직과의 충돌 가능성 (공백 처리 방식의 차이)
• 로그 분석 및 데이터 감사(Compliance) 문제 (공백 포함 여부에 따른 기록 차이)
• 데이터 정리(Cleaning) 과정 필요 (일관된 데이터 정제 방법 필요)

이처럼 데이터베이스마다 Trailing Space를 처리하는 방식이 다를 수 있기 때문에, 이를 사전에 이해하고 고려하는 것이 중요합니다.

따라서, SQL을 작성하거나 데이터를 검증할 때 각 DBMS의 동작 원리를 명확히 파악하면 예상치 못한 오류를 방지하고, 더욱 정확한 데이터 분석과 처리가 가능해집니다.

앞으로도 이러한 세부적인 차이를 인식하고 활용하는 것이 데이터 품질 관리의 핵심이 될 것입니다.