-- String to number: leading digits extracted, rest discarded
SELECT '42abc' + 0;      -- 42  (string becomes 42)
SELECT '42abc' = 42;     -- 1   (TRUE — string converts to 42)
SELECT '0abc'  = 0;      -- 1   (TRUE — '0abc' converts to 0)

-- This silently matches unintended rows!
SELECT * FROM users WHERE id = '1 OR 1=1';
-- MySQL converts '1 OR 1=1' to 1 (stops at non-digit)
-- Finds only user id=1 — NOT an injection issue here, but a logic bug

-- Type mismatch prevents index use
-- Bad: index on id (INT) cannot be used with string literal
EXPLAIN SELECT * FROM users WHERE id = '42'G
-- type: ref or ALL depending on version (implicit cast)

-- Good: matches column type exactly, index used efficiently
EXPLAIN SELECT * FROM users WHERE id = 42G

-- Declaring NOT NULL enables the optimizer to skip null checks
CREATE TABLE events (
  id         BIGINT   NOT NULL AUTO_INCREMENT,
  user_id    INT      NOT NULL,   -- no null check needed in joins
  event_type VARCHAR(50) NOT NULL,
  payload    JSON,                -- nullable: only allocate when present
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- This is wrong — FLOAT has rounding errors
CREATE TABLE prices_wrong (price FLOAT);
INSERT INTO prices_wrong VALUES (9.99);
SELECT price * 100 FROM prices_wrong;
-- Result: 999.0000019073486 -- NOT 999.00!

-- This is correct
CREATE TABLE prices_correct (price DECIMAL(10, 2));
INSERT INTO prices_correct VALUES (9.99);
SELECT price * 100 FROM prices_correct;
-- Result: 999.00 -- exact!

-- CAST(value AS type) -- SQL standard
SELECT CAST('2024-01-15'   AS DATE);
SELECT CAST(42.7           AS UNSIGNED);    -- 42 (truncates, not rounds)
SELECT CAST('123abc'       AS UNSIGNED);    -- 123 (stops at non-digit)
SELECT CAST(NOW()          AS DATE);        -- strips time portion
SELECT CAST('{"a":1}'     AS JSON);        -- validates and parses

-- All CAST target types:
-- SIGNED              INT with sign
-- UNSIGNED            INT without sign
-- DECIMAL(p,s)        Exact decimal
-- FLOAT               4-byte float
-- DOUBLE              8-byte float
-- DATE                Date only
-- DATETIME            Date and time
-- TIME                Time only
-- YEAR                Year only
-- CHAR[(n)]           Character string
-- BINARY[(n)]         Binary string
-- JSON                JSON document (8.0+)

-- CONVERT(value, type) -- MySQL extension, functionally same as CAST
SELECT CONVERT('42',    UNSIGNED);
SELECT CONVERT(NOW(),   DATE);

-- Two-argument CONVERT: character set conversion
SELECT CONVERT('hello' USING utf8mb4);
SELECT CONVERT(latin1_col USING utf8mb4) FROM legacy_table;

-- VIRTUAL column: computed on SELECT, not stored
CREATE TABLE order_items (
  id         INT            NOT NULL AUTO_INCREMENT,
  qty        INT            NOT NULL,
  unit_price DECIMAL(10,2)  NOT NULL,
  -- VIRTUAL: no storage used, computed each time it is read
  line_total DECIMAL(12,2)  AS (qty * unit_price) VIRTUAL,
  PRIMARY KEY (id)
);

-- STORED column: computed on INSERT/UPDATE, stored on disk
CREATE TABLE articles (
  id       INT       NOT NULL AUTO_INCREMENT,
  title    VARCHAR(500) NOT NULL,
  body     MEDIUMTEXT,
  -- STORED: indexable, takes disk space
  word_count INT UNSIGNED AS (
    IF(body IS NULL, 0,
      CHAR_LENGTH(body) - CHAR_LENGTH(REPLACE(body, ' ', '')) + 1)
  ) STORED,
  PRIMARY KEY (id),
  INDEX idx_word_count (word_count)  -- can index STORED generated columns
);

-- Query using the generated column
SELECT title, word_count FROM articles WHERE word_count > 1000;

-- JSON column: validated, path-accessible
CREATE TABLE user_preferences (
  user_id   INT PRIMARY KEY,
  settings  JSON NOT NULL
);

INSERT INTO user_preferences VALUES
  (1, '{"theme":"dark","lang":"en","notifications":true}');

-- Extract with -> (returns JSON value, strings are quoted)
SELECT settings->'$.theme' FROM user_preferences WHERE user_id = 1;
-- Result: "dark"

-- Extract with ->> (returns unquoted string)
SELECT settings->>'$.theme' FROM user_preferences WHERE user_id = 1;
-- Result: dark

-- Index a specific JSON path via generated column
ALTER TABLE user_preferences
  ADD COLUMN theme_virtual VARCHAR(20)
    AS (settings->>'$.theme') VIRTUAL,
  ADD INDEX idx_theme (theme_virtual);

SELECT user_id FROM user_preferences WHERE theme_virtual = 'dark';