vyvchy
    Теми розділу

    05 · Бази даних і SQL для QA

    Функції, CASE і пастки NULL

    Зміст

    Половина «багів», які QA знаходить через SQL, — насправді не баги, а нерозуміння NULL. Запит повертає менше рядків, ніж очікувалось; звіт «за сьогодні» порожній, хоча замовлення точно є; сума в SQL не збігається з сумою в UI. У всіх трьох випадках винен один і той самий механізм: NULL і тризначна логіка, за якою SQL обчислює умови.

    На співбесідах ця тема — стабільний фільтр між «писав SELECT за туторіалом» і «розуміє, що робить». Питання «що поверне NULL = NULL» ставлять чи не на кожній співбесіді з SQL, і воно миттєво показує глибину. Тому в цій главі спершу розберемо, чим NULL відрізняється від нуля й порожнього рядка, а потім — інструменти, які з ним працюють: COALESCE, NULLIF, CASE, і поруч — суміжні пастки: рядкові функції, дати з таймзонами та перетворення типів.

    NULL — це «невідомо», а не нуль і не порожній рядок

    NULL — не значення, а позначка «значення відсутнє». У колонці phone NULL означає «телефон невідомий», а не «телефон — порожній рядок» і не «телефон дорівнює нулю». Це різні стани, і база їх розрізняє: '' — відоме значення (порожнє), 0 — відоме значення (нуль), NULL — ми не знаємо, що там.

    З цього випливає головне: порівнювати з «невідомо» безглуздо, і SQL чесно це визнає. Будь-яке порівняння, де бере участь NULL, дає не TRUE і не FALSE, а третій результат — UNKNOWN. Так працює тризначна логіка (three-valued logic): у SQL логічний вираз має три можливі результати, а не два.

    ВиразРезультат
    NULL = NULLUNKNOWN
    NULL != 'active'UNKNOWN
    NULL + 10NULL
    TRUE AND UNKNOWNUNKNOWN
    FALSE AND UNKNOWNFALSE
    TRUE OR UNKNOWNTRUE
    NOT UNKNOWNUNKNOWN

    NULL = NULL — UNKNOWN, бо ми порівнюємо два невідомих: чи рівні вони, сказати неможливо. Арифметика з NULL так само «заражає» результат: ціна * NULL — NULL.

    А тепер ключова деталь, через яку губляться рядки: WHERE пропускає лише рядки, для яких умова дорівнює TRUE. FALSE і UNKNOWN відкидаються однаково.

    TRUE

    FALSE

    UNKNOWN — порівняння з NULL

    Рядок таблиці

    Умова WHERE

    Потрапляє у вибірку

    Відкидається

    TRUE

    FALSE

    UNKNOWN — порівняння з NULL

    Рядок таблиці

    Умова WHERE

    Потрапляє у вибірку

    Відкидається

    Класичний наслідок. У таблиці users 10 рядків зі status = 'active', 5 зі status = 'inactive' і 3 зі status NULL:

    SELECT COUNT(*) FROM users WHERE status != 'active';
-- Поверне 5, а не 8: для NULL-рядків умова дає UNKNOWN

    Інтуїція каже «NULL — це ж не active, отже має потрапити», але для бази NULL != 'active' — UNKNOWN, і рядок відкинуто. Щоб охопити й невідомі статуси, умову доповнюють явно:

    WHERE status != 'active' OR status IS NULL

    IS NULL та IS NOT NULL — єдині базові предикати, які чесно відповідають TRUE/FALSE на питання про NULL (ти вже бачив їх у главі про SELECT). Для порівняння двох колонок, кожна з яких може бути NULL, у PostgreSQL є IS DISTINCT FROM / IS NOT DISTINCT FROM — NULL-безпечна рівність, яка трактує два NULL як «однакові»; у MySQL ту саму роль виконує оператор <=>.

    Ще три місця, де NULL поводиться не так, як очікуєш:

    • ORDER BY. Стандарт не фіксує, куди сортувати NULL, тож СУБД розходяться: PostgreSQL за замовчуванням вважає NULL «найбільшим» (при ASC вони внизу), MySQL — «найменшим» (при ASC вони вгорі). У PostgreSQL позицію можна задати явно: ORDER BY discount DESC NULLS LAST. Для QA це означає: звіряючи відсортований список UI зі своїм запитом, спершу з'ясуй, куди твоя СУБД кладе NULL.
    • Агрегати. COUNT(phone) рахує лише не-NULL значення, тому може бути менший за COUNT(*); AVG теж ігнорує NULL, а не рахує його як нуль. Детально — у главі про агрегацію.
    • NOT IN. Один NULL у підзапиті — і NOT IN не поверне жодного рядка. Це найпідступніша пастка NULL, розбираємо її в главі про підзапити.

    Абзац глибини: попри те що NULL = NULL — UNKNOWN, DISTINCT і GROUP BY групують усі NULL разом — вони використовують не рівність, а семантику «not distinct». А CHECK-обмеження, на відміну від WHERE, UNKNOWN пропускає: рядок з NULL пройде перевірку CHECK (price > 0). Тому «в колонці з CHECK не може бути дивних значень» — хибне припущення, яке варто перевіряти окремо.

    COALESCE і NULLIF

    COALESCE повертає перший не-NULL аргумент зі списку:

    SELECT COALESCE(discount, 0)           FROM orders;   -- NULL-знижка стає нулем
SELECT COALESCE(nickname, email, '—')  FROM users;    -- перший заповнений варіант

    Це стандартна функція, працює однаково в усіх основних СУБД. Для QA вона важлива подвійно. По-перше, нею користуєшся сам: у звірках, де NULL треба привести до значення за замовчуванням. По-друге, її використовує (або забуває використати) бекенд: якщо UI показує знижку «0», а в базі лежить NULL — десь між базою і екраном стоїть COALESCE або його аналог у коді. Тоді питання до розробника не «чому дані різні», а «де саме NULL перетворюється на нуль і чи всюди однаково».

    NULLIF(a, b) — дзеркальна операція: повертає NULL, якщо аргументи рівні, і перший аргумент, якщо ні. Головне практичне застосування — захист від ділення на нуль:

    SELECT amount / NULLIF(quantity, 0) FROM order_items;
-- quantity = 0 → ділимо на NULL → результат NULL, а не помилка

    А в парі вони дають типовий шаблон «безпечне ділення з дефолтом»:

    SELECT COALESCE(amount / NULLIF(quantity, 0), 0) FROM order_items;

    CASE WHEN

    CASE — це вираз (не окремий оператор!), який повертає значення за умовами. Його можна вжити всюди, де очікується значення: у SELECT, WHERE, ORDER BY. Основна, «пошукова» (searched) форма:

    SELECT id,
       CASE
         WHEN total >= 1000 THEN 'vip'
         WHEN total >= 100  THEN 'regular'
         ELSE 'new'
       END AS segment
FROM customers;

    Правила прості, але кожне — джерело помилок:

    • умови перевіряються зверху вниз, перемагає перша TRUE; рядок з total = 5000 отримає 'vip', до другої гілки справа не дійде — тому порядок гілок значущий;
    • ELSE необов'язковий, але без нього всі «непокриті» рядки отримають NULL — і цей NULL далі поводитиметься за всіма правилами вище;
    • гілка з UNKNOWN не спрацьовує — як і у WHERE.

    Є ще «проста» форма — CASE колонка WHEN значення THEN .... Вона порівнює через рівність, і саме тут закопана пастка:

    CASE status
  WHEN 'active' THEN 'Активний'
  WHEN NULL     THEN 'Невідомо'   -- НІКОЛИ не спрацює: status = NULL → UNKNOWN
  ELSE 'Інше'
END

    Рядки з NULL-статусом підуть у гілку ELSE, а не в «Невідомо». Ловити NULL можна лише пошуковою формою з IS NULL:

    CASE
  WHEN status = 'active' THEN 'Активний'
  WHEN status IS NULL    THEN 'Невідомо'
  ELSE 'Інше'
END

    У щоденній роботі QA CASE найчастіше зустрічається у звірках: привести технічні коди до людських назв, як в UI, або порахувати кілька категорій одним запитом через SUM(CASE WHEN ... THEN 1 ELSE 0 END) — цей прийом розберемо в главі про агрегацію.

    Рядкові функції

    Базовий набір скрізь схожий, відрізняються деталі. Найуживаніше для QA:

    ФункціяЩо робитьПастка
    UPPER / LOWERзмінює регістрстандартний спосіб звірки без урахування регістру
    TRIMзрізає пробіли з обох країв«однакові» рядки не рівні через хвостовий пробіл
    LENGTH / CHAR_LENGTHдовжина рядкау MySQL LENGTH рахує байти, CHAR_LENGTH — символи
    SUBSTRINGфрагмент рядканумерація позицій з 1, не з 0
    CONCAT / оператор конкатенаціїсклеювання рядківNULL «з'їдає» весь результат (оператор — у PostgreSQL, CONCAT — у MySQL)
    REPLACEзаміна підрядка—
    POSITIONпозиція підрядкаповертає 0, якщо не знайдено

    Три деталі, які реально стріляють у тестуванні.

    NULL у конкатенації. У PostgreSQL оператор || з NULL дає NULL: first_name || ' ' || last_name для користувача без прізвища поверне NULL цілком — і в звіті зникне все ім'я. У MySQL так само поводиться CONCAT (будь-який NULL-аргумент — результат NULL), а от CONCAT_WS NULL-аргументи пропускає — тому в одному звіті імена є, а в іншому зникли, хоча дані ті самі. До речі, || у MySQL за замовчуванням — логічне АБО, а не конкатенація: ще один прояв діалектів, про які йшлося в першій главі розділу.

    Байти проти символів. У MySQL LENGTH('Київ') в utf8mb4 поверне 8 (байти), а CHAR_LENGTH('Київ') — 4 (символи). Коли перевіряєш обмеження довжини поля на кирилиці, різниця між цими двома функціями — це різниця між «влазить» і «не влазить».

    Регістр у порівняннях і LIKE. У PostgreSQL LIKE чутливий до регістру (для нечутливого є ILIKE), а в MySQL чутливість визначає collation колонки, і дефолтні — нечутливі. Тому один і той самий WHERE city LIKE 'kyiv%' в одній СУБД знайде 'Kyiv', а в іншій — ні. Якщо тест-звірка «не бачить» рядок, який точно є, — спершу перевір регістр і хвостові пробіли, а вже потім бий тривогу.

    Дати, час і пастки таймзон

    Типів дат кілька: DATE (лише дата), TIME, TIMESTAMP (дата+час без таймзони) і TIMESTAMP WITH TIME ZONE (момент часу; у PostgreSQL — timestamptz). Деталі типів — у главі про DDL і типи даних, тут — те, що ламає перевірки.

    timestamptz у PostgreSQL зберігає момент часу в UTC, а при виведенні конвертує в таймзону сесії. timestamp без таймзони — це «настінний годинник»: числа без прив'язки до зони, конвертацій немає. У MySQL схоже розділення: TIMESTAMP конвертується в UTC при записі й назад при читанні, DATETIME зберігається як є. Типова архітектура: база зберігає UTC, UI показує локальний час користувача. Тому «в базі 21:30, а на екрані 00:30» — не баг, а конвертація UTC у київський час (влітку UTC+3). Багом це стає, коли зсув не відповідає таймзоні: подвійна конвертація, зсув на добу, час «стрибає» після переходу на літній/зимовий час.

    Друга пастка — межі доби при фільтрації timestamp-колонки:

    -- created_at — timestamp. Так «сьогоднішні» замовлення загубляться:
WHERE created_at = '2026-07-17'
-- рядок '2026-07-17' стане '2026-07-17 00:00:00' — збіг лише рівно опівніч

-- BETWEEN захопить зайве:
WHERE created_at BETWEEN '2026-07-17' AND '2026-07-18'
-- включає '2026-07-18 00:00:00' — замовлення рівно опівночі порахується двічі в сусідніх звітах

-- Надійний шаблон — піврозкритий інтервал:
WHERE created_at >= '2026-07-17' AND created_at < '2026-07-18'

    А тепер складемо обидві пастки разом — і отримаємо класичний «баг», який QA приносить розробнику, а той закриває як invalid:

    БД зберігає UTCЗастосунокКористувач у КиєвіБД зберігає UTCЗастосунокКористувач у Києвізвіт WHERE created_at >= '2026-07-17' цей рядок не бачитьв UI замовлення «сьогоднішнє», у звіті за 17.07 його немаєстворює замовлення 17.07 о 00:30INSERT created_at = '2026-07-16 21:30' UTCБД зберігає UTCЗастосунокКористувач у КиєвіБД зберігає UTCЗастосунокКористувач у Києвізвіт WHERE created_at >= '2026-07-17' цей рядок не бачитьв UI замовлення «сьогоднішнє», у звіті за 17.07 його немаєстворює замовлення 17.07 о 00:30INSERT created_at = '2026-07-16 21:30' UTC

    Замовлення, створене вночі за Києвом, у UTC належить попередній добі. Якщо звіт ріже добу по UTC, а користувач думає локальним часом — записи біля півночі «мігрують» між днями. Це не баг бази і не баг звіту окремо — це питання «в якій таймзоні визначаємо добу», і його треба з'ясувати до того, як заводити дефект.

    Суміжна дрібниця: NOW(), CURRENT_DATE, CURRENT_TIMESTAMP обчислюються за таймзоною сесії/сервера БД, а не твоєю. «Сьогодні» для CI-сервера в іншій зоні може бути ще «вчора» для тебе — улюблена причина тестів, що падають лише вночі. І штрих для глибини: у PostgreSQL NOW() повертає час початку транзакції, тож усередині однієї транзакції він не змінюється між викликами.

    В автотестах найнадійніша дисципліна — порівнювати моменти часу в UTC та ISO 8601, а не локальні рядки з UI:

    // Playwright: нормалізуємо час з API до UTC перед порівнянням
const created = new Date(order.createdAt);
expect(created.toISOString()).toBe('2026-07-16T21:30:00.000Z');

    CAST і неявні перетворення

    Явне перетворення типів — CAST(вираз AS тип); у PostgreSQL є короткий синтаксис вираз::тип:

    SELECT CAST('123' AS INTEGER);   -- стандартно
SELECT '123'::INTEGER;           -- PostgreSQL
SELECT CAST(2.7 AS INTEGER);     -- 3: PostgreSQL округлює, а не відрізає дріб

    Небезпека — у неявних перетвореннях, коли ти порівнюєш значення різних типів і СУБД конвертує сама. PostgreSQL суворий: 'abc'::INTEGER впаде з помилкою invalid input syntax for type integer. MySQL — ні: при порівнянні рядка з числом він мовчки конвертує рядок у число, і 'abc' стає 0, а '123abc' — 123:

    -- MySQL: order_number — VARCHAR
SELECT * FROM orders WHERE order_number = 123;
-- знайде і '123', і '123abc', і '0123' — рядки конвертуються в число

    Запит «працює», повертає рядки — і саме тому баг живе довго: помилки немає, є тихо неправильна вибірка. Правило для QA: порівнюй рядкову колонку з рядком (= '123'), числову — з числом, і насторожуйся щоразу, коли типи в порівнянні різні.

    Ще дві пастки з цієї ж родини:

    • Цілочисельне ділення. У PostgreSQL 5 / 2 дає 2 — обидва операнди цілі, дріб відкидається. У MySQL 5 / 2 дає 2.5000. Той самий вираз — різні результати; у PostgreSQL рятує явний CAST одного з операндів у numeric.
    • Каст на колонці вимикає індекс. Коли для порівняння СУБД мусить конвертувати саму колонку (як у прикладі з order_number = 123), індекс по цій колонці може не використовуватись, і запит на великій таблиці «раптово» повільний. Механіку розберемо в главі про індекси і швидкодію запитів.

    І нагадування з розряду «чому суми не сходяться на копійки»: гроші зберігають у DECIMAL, а не у FLOAT — двійковий float не представляє десяткові дроби точно. Деталі — у главі про типи даних.

    Типові помилки

    • Виглядає як «WHERE status != 'active' поверне все, крім active», а насправді рядки з NULL теж відкинуто: порівняння з NULL дає UNKNOWN, а WHERE пропускає лише TRUE.
    • Виглядає як «= NULL — те саме, що IS NULL», а насправді = NULL завжди UNKNOWN, і вибірка порожня — навіть якщо NULL-рядків повна таблиця.
    • Виглядає як «CASE status WHEN NULL THEN ... спіймає невідомі статуси», а насправді проста форма CASE порівнює через рівність, і гілка не спрацює ніколи; NULL ловиться лише через WHEN status IS NULL.
    • Виглядає як «BETWEEN по датах бере рівно одну добу», а насправді для timestamp він включає опівніч наступного дня; безпечний шаблон — піврозкритий інтервал >= початок AND менше кінець.
    • Виглядає як «звіт за сьогодні порожній — баг бекенда», а насправді created_at = '2026-07-17' порівнює з рівно опівніччю, а нічні записи ще й «мігрували» в сусідню добу через UTC.
    • Виглядає як «UI показує не той час, що в базі, — дефект», а насправді база зберігає UTC, а UI конвертує в локальний час; дефект лише тоді, коли зсув не збігається з таймзоною користувача.
    • Виглядає як «CONCAT пропустить порожні значення», а насправді один NULL-аргумент робить NULL увесь результат (і || у PostgreSQL, і CONCAT у MySQL); пропускає NULL тільки CONCAT_WS.
    • Виглядає як «запит з order_number = 123 по varchar-колонці — просто нестрогий стиль», а насправді MySQL мовчки конвертує рядки в числа: у вибірку потрапить і '123abc', а індекс може не спрацювати.

    Підсумок

    1. NULL — це «невідомо», а не нуль і не порожній рядок: будь-яке порівняння з ним дає UNKNOWN, а WHERE лишає тільки рядки з TRUE.
    2. З NULL працюють лише спеціальні конструкції: IS NULL / IS NOT NULL, COALESCE (перший не-NULL), NULLIF (NULL при рівності); звичайні = і != — ніколи.
    3. CASE — вираз: гілки перевіряються зверху вниз, перемагає перша TRUE, без ELSE результат — NULL; NULL ловиться тільки пошуковою формою з IS NULL.
    4. Доба в timestamp-колонці фільтрується піврозкритим інтервалом; база зазвичай зберігає UTC, UI показує локальний час — записи біля півночі «мігрують» між добами.
    5. Неявні перетворення типів — джерело тихих багів: MySQL конвертує рядки в числа без помилки, цілочисельне ділення відкидає дріб, а каст на колонці вимикає індекс.

    Що питають на співбесіді

    • «Що поверне NULL = NULL і чому?» — перевіряють, чи знаєш тризначну логіку, а не завчене «використовуй IS NULL». Сильна відповідь пояснює: NULL — невідомо, порівняння двох невідомих — UNKNOWN, WHERE його відкидає.
    • «У таблиці є рядки з NULL у статусі. Що поверне WHERE status != 'active'?» — класична перевірка на практичне розуміння: чи бачиш ти, що NULL-рядки випадуть, і чи знаєш, як дописати умову.
    • «Чим COALESCE відрізняється від NULLIF? Наведи приклад для кожної» — очікують не означення, а живий кейс: дефолт для NULL-знижки і захист від ділення на нуль.
    • «Як вибрати всі замовлення за конкретну дату, якщо колонка — timestamp?» — інтерв'юер дивиться, чи запропонуєш піврозкритий інтервал і чи згадаєш про таймзону; «WHERE created_at = дата» — миттєвий мінус.
    • «Чому час у базі відрізняється від часу в UI на 2–3 години? Це баг?» — перевірка зрілості: чи розрізняєш зберігання в UTC і відображення в локальній зоні, і як з'ясуєш, у якій зоні ріжеться доба звіту.
    • «Що поверне SELECT 5 / 2?» — питання з підступом на знання діалектів і цілочисельного ділення; чесна відповідь — «залежить від СУБД» з поясненням.

    Інтерв'юер на цій темі відрізняє кандидата, який пише запити «поки не запрацює», від того, хто передбачає поведінку до запуску. Найсильніше враження справляє не перелік функцій, а вміння пояснити «чому»: чому WHERE відкидає UNKNOWN, чому CASE не ловить NULL рівністю, чому нічні замовлення випали зі звіту.

    Джерела