<commit_before># Test the LIKE function
---
- Include: all-types-schema.yaml
---
- Statement: SELECT varchar_field LIKE FROM all_types
- error: !select-engine { all: [42000], sys-mysql: [1064] }
---
- Statement: SELECT LIKE varchar_field FROM all_types
- error: !select-engine {all: [42000], sys-mysql: [1064] }
---
- Statement: INSERT INTO all_types (varchar_field) VALUES (null)
---
- Statement: INSERT INTO all_types (varchar_field) VALUES
    (''), ('a'), ('b'), ('ab'), ('ac'), ('cb'), ('cc'),
    ('abc'), ('bbc'), ('cbb'),
    ('abcd'), ('xbcd')
---
- Statement: SELECT varchar_field FROM all_types
    WHERE varchar_field LIKE ''
- output: [['']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE 'a'
- output: [['a']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%'
- row_count: 12
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%%'
- row_count: 12
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '_'
- output: [['a'], ['b']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '__'
- output: [['ab'], ['ac'], ['cb'], ['cc']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '_%'
- output: [['a'], ['b'], ['ab'], ['ac'], ['cb'], ['cc'],
           ['abc'], ['bbc'], ['cbb'],
           ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE 'a%'
- output: [['a'], ['ab'], ['ac'], ['abc'], ['abcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%b'
- output: [['b'], ['ab'], ['cb'], ['cbb']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%b%c%'
- output: [['abc'], ['bbc'], ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%_'
- output: [['a'], ['b'], ['ab'], ['ac'], ['cb'], ['cc'],
           ['abc'], ['bbc'], ['cbb'],
           ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%b%'
- output: [['b'], ['ab'], ['cb'], ['abc'], ['bbc'], ['cbb'], ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE 'a_c%'
- output: [['abc'], ['abcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE 'abc' LIKE varchar_field
- output: [['abc']]

---
- Statement: SELECT varchar_field, varchar_field LIKE 'ab%' FROM all_types
- output: [[null, null],
           ['', false],
           [a, false],
           [b, false],
           [ab, true],
           [ac, false],
           [cb, false],
           [cc, false],
           [abc, true],
           [bbc, false],
           [cbb, false],
           [abcd, true],
           [xbcd, false]]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE ?
- params: [['b%']]
- output: [['b'], ['bbc']]
# COLLATE fails
# # Case sensitivity
# ---
# - CreateTable: t (cs varchar(32) CHARSET latin1 COLLATE latin1_bin)
# ---
# - Statement: INSERT INTO t VALUES ('abc'), ('Abc'), ('ABC')
# ---
# - Statement: SELECT cs WHERE cs LIKE 'Abc'
# - output: [[Abc]]
# ---
# - Statement: SELECT cs WHERE cs LIKE 'abc'
# - output: [[abc]]
# ---
# - Statement: SELECT cs WHERE cs LIKE 'ABC'
# - output: [[ABC]]
---
- Statement: DELETE FROM all_types
---
- Statement: INSERT INTO all_types (varchar_field) VALUES ('abc'), ('Abc'), ('ABC')
---
- Statement: SELECT varchar_field FROM all_types WHERE BLIKE(varchar_field, 'Abc')
- output: [[Abc]]
---
- Statement: SELECT varchar_field FROM all_types WHERE ILIKE(varchar_field, 'Abc')
- output: [[abc], [Abc], [ABC]]
---
- Statement: SELECT varchar_field FROM all_types WHERE BLIKE(varchar_field, 'abc')
- output: [[abc]]
---
- Statement: SELECT varchar_field FROM all_types WHERE ILIKE(varchar_field, 'abc')
- output: [[abc], [Abc], [ABC]]
---
- Statement: SELECT varchar_field FROM all_types WHERE BLIKE(varchar_field, 'ABC')
- output: [[ABC]]
---
- Statement: SELECT varchar_field FROM all_types WHERE ILIKE(varchar_field, 'ABC')
- output: [[abc], [Abc], [ABC]]

# Test escapes
---
- Statement: DELETE FROM all_types
---
- Statement: INSERT INTO all_types (varchar_field)
    VALUES ('a'), ('ax'), ('a%'), ('a_'), ('a\'), ('a%c'), ('a%cd'), ('a_c'), ('a_cd'),
    ('ax%'), ('ax_'), ('axx')<commit_msg>Modulo typos, there was already a test that would have shown the problem. Uncomment it.<commit_after># Test the LIKE function
---
- Include: all-types-schema.yaml
---
- Statement: SELECT varchar_field LIKE FROM all_types
- error: !select-engine { all: [42000], sys-mysql: [1064] }
---
- Statement: SELECT LIKE varchar_field FROM all_types
- error: !select-engine {all: [42000], sys-mysql: [1064] }
---
- Statement: INSERT INTO all_types (varchar_field) VALUES (null)
---
- Statement: INSERT INTO all_types (varchar_field) VALUES
    (''), ('a'), ('b'), ('ab'), ('ac'), ('cb'), ('cc'),
    ('abc'), ('bbc'), ('cbb'),
    ('abcd'), ('xbcd')
---
- Statement: SELECT varchar_field FROM all_types
    WHERE varchar_field LIKE ''
- output: [['']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE 'a'
- output: [['a']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%'
- row_count: 12
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%%'
- row_count: 12
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '_'
- output: [['a'], ['b']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '__'
- output: [['ab'], ['ac'], ['cb'], ['cc']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '_%'
- output: [['a'], ['b'], ['ab'], ['ac'], ['cb'], ['cc'],
           ['abc'], ['bbc'], ['cbb'],
           ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE 'a%'
- output: [['a'], ['ab'], ['ac'], ['abc'], ['abcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%b'
- output: [['b'], ['ab'], ['cb'], ['cbb']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%b%c%'
- output: [['abc'], ['bbc'], ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%_'
- output: [['a'], ['b'], ['ab'], ['ac'], ['cb'], ['cc'],
           ['abc'], ['bbc'], ['cbb'],
           ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE '%b%'
- output: [['b'], ['ab'], ['cb'], ['abc'], ['bbc'], ['cbb'], ['abcd'], ['xbcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE 'a_c%'
- output: [['abc'], ['abcd']]
---
- Statement: SELECT varchar_field FROM all_types WHERE 'abc' LIKE varchar_field
- output: [['abc']]

---
- Statement: SELECT varchar_field, varchar_field LIKE 'ab%' FROM all_types
- output: [[null, null],
           ['', false],
           [a, false],
           [b, false],
           [ab, true],
           [ac, false],
           [cb, false],
           [cc, false],
           [abc, true],
           [bbc, false],
           [cbb, false],
           [abcd, true],
           [xbcd, false]]
---
- Statement: SELECT varchar_field FROM all_types WHERE varchar_field LIKE ?
- params: [['b%']]
- output: [['b'], ['bbc']]
# Case sensitivity
---
- CreateTable: t (cs varchar(32) CHARACTER SET latin1 COLLATE latin1_bin)
---
- Statement: INSERT INTO t VALUES ('abc'), ('Abc'), ('ABC')
---
- Statement: SELECT cs FROM t WHERE cs LIKE 'Abc'
- output: [[Abc]]
---
- Statement: SELECT cs FROM t WHERE cs LIKE 'abc'
- output: [[abc]]
---
- Statement: SELECT cs FROM t WHERE cs LIKE 'ABC'
- output: [[ABC]]
---
- Statement: DELETE FROM all_types
---
- Statement: INSERT INTO all_types (varchar_field) VALUES ('abc'), ('Abc'), ('ABC')
---
- Statement: SELECT varchar_field FROM all_types WHERE BLIKE(varchar_field, 'Abc')
- output: [[Abc]]
---
- Statement: SELECT varchar_field FROM all_types WHERE ILIKE(varchar_field, 'Abc')
- output: [[abc], [Abc], [ABC]]
---
- Statement: SELECT varchar_field FROM all_types WHERE BLIKE(varchar_field, 'abc')
- output: [[abc]]
---
- Statement: SELECT varchar_field FROM all_types WHERE ILIKE(varchar_field, 'abc')
- output: [[abc], [Abc], [ABC]]
---
- Statement: SELECT varchar_field FROM all_types WHERE BLIKE(varchar_field, 'ABC')
- output: [[ABC]]
---
- Statement: SELECT varchar_field FROM all_types WHERE ILIKE(varchar_field, 'ABC')
- output: [[abc], [Abc], [ABC]]

# Test escapes
---
- Statement: DELETE FROM all_types
---
- Statement: INSERT INTO all_types (varchar_field)
    VALUES ('a'), ('ax'), ('a%'), ('a_'), ('a\'), ('a%c'), ('a%cd'), ('a_c'), ('a_cd'),
    ('ax%'), ('ax_'), ('axx')
{'fraction_non_alphanumeric': 0.21395029206276486, 'fraction_numerical': 0.0059557897148093, 'mean_word_length': 3.5693354264782835, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5045985', 'n_tokens_mistral': 3148, 'n_tokens_neox': 2893, 'n_words': 898}