-- Test ILIKE and POSIX regex operator pushdown
SET datestyle = 'ISO';
CREATE SERVER ilike_regex_bin_svr FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'ilike_regex_test', driver 'binary');
CREATE USER MAPPING FOR CURRENT_USER SERVER ilike_regex_bin_svr;
CREATE SERVER ilike_regex_http_svr FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'ilike_regex_test', driver 'http');
CREATE USER MAPPING FOR CURRENT_USER SERVER ilike_regex_http_svr;
SELECT clickhouse_raw_query('DROP DATABASE IF EXISTS ilike_regex_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('CREATE DATABASE ilike_regex_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query($$
    CREATE TABLE ilike_regex_test.events (
        id     Int32,
        name   String,
        path   String
    ) ENGINE = MergeTree ORDER BY id
$$);
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query($$
    INSERT INTO ilike_regex_test.events VALUES
        (1, 'page_view',   '/users/profile'),
        (2, 'Page_View',   '/users/settings'),
        (3, 'PAGE_VIEW',   '/admin/dashboard'),
        (4, 'add_to_cart', '/products/shoes'),
        (5, 'Add_To_Cart', '/products/hats'),
        (6, 'purchase',    '/checkout'),
        (7, 'PURCHASE',    '/checkout/confirm'),
        (8, 'share',       '/social/twitter'),
        (9, 'logout',      '/auth/logout'),
        (10, 'signup',     '/auth/signup')
$$);
 clickhouse_raw_query 
----------------------
 
(1 row)

CREATE SCHEMA ilike_regex_bin;
CREATE SCHEMA ilike_regex_http;
IMPORT FOREIGN SCHEMA "ilike_regex_test" FROM SERVER ilike_regex_bin_svr INTO ilike_regex_bin;
IMPORT FOREIGN SCHEMA "ilike_regex_test" FROM SERVER ilike_regex_http_svr INTO ilike_regex_http;
-- =======================================================
-- ILIKE: case-insensitive LIKE pushdown (binary driver)
-- =======================================================
-- ILIKE should match regardless of case
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name ILIKE '%view%' ORDER BY id;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name ILIKE '%view%')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name ILIKE '%view%' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
  2 | Page_View
  3 | PAGE_VIEW
(3 rows)

-- NOT ILIKE
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name NOT ILIKE '%view%' ORDER BY id;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name NOT ILIKE '%view%')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name NOT ILIKE '%view%' ORDER BY id;
 id |    name     
----+-------------
  4 | add_to_cart
  5 | Add_To_Cart
  6 | purchase
  7 | PURCHASE
  8 | share
  9 | logout
 10 | signup
(7 rows)

-- ILIKE in aggregate context
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*) FROM ilike_regex_bin.events WHERE name ILIKE 'page%';
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Foreign Scan
   Output: (count(*))
   Relations: Aggregate on (events)
   Remote SQL: SELECT count(*) FROM ilike_regex_test.events WHERE ((name ILIKE 'page%'))
(4 rows)

SELECT count(*) FROM ilike_regex_bin.events WHERE name ILIKE 'page%';
 count 
-------
     3
(1 row)

-- ILIKE with underscore wildcard (matches page_view, Page_View, PAGE_VIEW)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name ILIKE 'page_____' ORDER BY id;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name ILIKE 'page_____')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name ILIKE 'page_____' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
  2 | Page_View
  3 | PAGE_VIEW
(3 rows)

-- LIKE (case-sensitive) should still work as before
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name LIKE '%view%' ORDER BY id;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name LIKE '%view%')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name LIKE '%view%' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
(1 row)

-- NOT LIKE (case-sensitive)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*) FROM ilike_regex_bin.events WHERE name NOT LIKE '%view%';
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan
   Output: (count(*))
   Relations: Aggregate on (events)
   Remote SQL: SELECT count(*) FROM ilike_regex_test.events WHERE ((name NOT LIKE '%view%'))
(4 rows)

SELECT count(*) FROM ilike_regex_bin.events WHERE name NOT LIKE '%view%';
 count 
-------
     9
(1 row)

-- =======================================================
-- ILIKE: case-insensitive LIKE pushdown (http driver)
-- =======================================================
-- ILIKE should match regardless of case
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_http.events WHERE name ILIKE '%view%' ORDER BY id;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_http.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name ILIKE '%view%')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_http.events WHERE name ILIKE '%view%' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
  2 | Page_View
  3 | PAGE_VIEW
(3 rows)

-- NOT ILIKE
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_http.events WHERE name NOT ILIKE '%view%' ORDER BY id;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_http.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name NOT ILIKE '%view%')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_http.events WHERE name NOT ILIKE '%view%' ORDER BY id;
 id |    name     
----+-------------
  4 | add_to_cart
  5 | Add_To_Cart
  6 | purchase
  7 | PURCHASE
  8 | share
  9 | logout
 10 | signup
(7 rows)

-- ILIKE with underscore wildcard (matches page_view, Page_View, PAGE_VIEW)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_http.events WHERE name ILIKE 'page_____' ORDER BY id;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_http.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((name ILIKE 'page_____')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_http.events WHERE name ILIKE 'page_____' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
  2 | Page_View
  3 | PAGE_VIEW
(3 rows)

-- =======================================================
-- POSIX regex: ~ mapped to match(), !~ to NOT match()
-- =======================================================
-- ~ (case-sensitive regex match)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name ~ '^page' ORDER BY id;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE (match(name, '^page')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name ~ '^page' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
(1 row)

-- !~ (case-sensitive regex no match)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name !~ '^page' ORDER BY id;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((NOT match(name, '^page'))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name !~ '^page' ORDER BY id;
 id |    name     
----+-------------
  2 | Page_View
  3 | PAGE_VIEW
  4 | add_to_cart
  5 | Add_To_Cart
  6 | purchase
  7 | PURCHASE
  8 | share
  9 | logout
 10 | signup
(9 rows)

-- ~ with alternation pattern
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name ~ '^(purchase|share)$' ORDER BY id;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE (match(name, '^(purchase|share)$')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name ~ '^(purchase|share)$' ORDER BY id;
 id |   name   
----+----------
  6 | purchase
  8 | share
(2 rows)

-- ~ in aggregate context
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*) FROM ilike_regex_bin.events WHERE name ~ '_view$';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Foreign Scan
   Output: (count(*))
   Relations: Aggregate on (events)
   Remote SQL: SELECT count(*) FROM ilike_regex_test.events WHERE (match(name, '_view$'))
(4 rows)

SELECT count(*) FROM ilike_regex_bin.events WHERE name ~ '_view$';
 count 
-------
     1
(1 row)

-- ~ with path column (test regex with slashes)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, path FROM ilike_regex_bin.events WHERE path ~ '^/auth/' ORDER BY id;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, path
   Remote SQL: SELECT id, path FROM ilike_regex_test.events WHERE (match(path, '^/auth/')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, path FROM ilike_regex_bin.events WHERE path ~ '^/auth/' ORDER BY id;
 id |     path     
----+--------------
  9 | /auth/logout
 10 | /auth/signup
(2 rows)

-- POSIX regex with http driver
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_http.events WHERE name ~ '^page' ORDER BY id;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_http.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE (match(name, '^page')) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_http.events WHERE name ~ '^page' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_http.events WHERE name !~ '^page' ORDER BY id;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_http.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((NOT match(name, '^page'))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_http.events WHERE name !~ '^page' ORDER BY id;
 id |    name     
----+-------------
  2 | Page_View
  3 | PAGE_VIEW
  4 | add_to_cart
  5 | Add_To_Cart
  6 | purchase
  7 | PURCHASE
  8 | share
  9 | logout
 10 | signup
(9 rows)

-- =======================================================
-- ~* and !~* pushed down via match(col, concat('(?i)', pattern))
-- =======================================================
-- ~* (case-insensitive regex) should be pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name ~* '^page' ORDER BY id;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE (match(name, concat('(?i)', '^page'))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name ~* '^page' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
  2 | Page_View
  3 | PAGE_VIEW
(3 rows)

-- !~* (case-insensitive regex negation) should be pushed down
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_bin.events WHERE name !~* '^page' ORDER BY id;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_bin.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE ((NOT match(name, concat('(?i)', '^page')))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_bin.events WHERE name !~* '^page' ORDER BY id;
 id |    name     
----+-------------
  4 | add_to_cart
  5 | Add_To_Cart
  6 | purchase
  7 | PURCHASE
  8 | share
  9 | logout
 10 | signup
(7 rows)

-- ~* with http driver
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, name FROM ilike_regex_http.events WHERE name ~* '^page' ORDER BY id;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ilike_regex_http.events
   Output: id, name
   Remote SQL: SELECT id, name FROM ilike_regex_test.events WHERE (match(name, concat('(?i)', '^page'))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, name FROM ilike_regex_http.events WHERE name ~* '^page' ORDER BY id;
 id |   name    
----+-----------
  1 | page_view
  2 | Page_View
  3 | PAGE_VIEW
(3 rows)

-- Cleanup
SELECT clickhouse_raw_query('DROP DATABASE ilike_regex_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

DROP USER MAPPING FOR CURRENT_USER SERVER ilike_regex_bin_svr;
DROP USER MAPPING FOR CURRENT_USER SERVER ilike_regex_http_svr;
DROP SERVER ilike_regex_bin_svr CASCADE;
NOTICE:  drop cascades to foreign table ilike_regex_bin.events
DROP SERVER ilike_regex_http_svr CASCADE;
NOTICE:  drop cascades to foreign table ilike_regex_http.events