mysql_fdw: Exceeded number of digits

[kimjinju730]

Hi, A select query is performed using mysql_fdw, and you can see that it changes to a full scan when the int range is exceeded. (x >2147483647). Is this a bug? or Is the problem caused by the different type properties of mysql and postgresql?

1)

# explain verbose select * from mylink.test where id = 2147483648;
                                                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ad_info  (cost=25.00..1025.00 rows=1000 width=580)
   Output: id, account_id, profile_id, event_type, update_fieldss, update_from, v , create_at 
   Filter: (test.id = '2147483648'::bigint)
   Remote server startup cost: 25
   Remote querye: SELECT `id`, `account_id`, `profile_id`, `event_type`, `update_fields`, `update_from`, `v`, `created_at` from `test_production`.`test` 
(5rows)

2)

# explain verbose select * from mylink.test where id = 2147483647;
                                                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ad_info  (cost=25.00..1025.00 rows=1000 width=580)
   Output: id, account_id, profile_id, event_type, update_fieldss, update_from, v , create_at 
   Remote server startup cost: 25
   Remote querye: SELECT `id`, `account_id`, `profile_id`, `event_type`, `update_fields`, `update_from`, `v`, `created_at` from `test_production`.`test` WHERE ((`id` = 2147483647))
(4rows)

[giant-ksj]

me too. i tested it.

This is my test case.

MySQL

mysql> create table bulkload (id bigint primary key, name varchar(10));
Query OK, 0 rows affected (0.03 sec)

mysql> show create table bulkload;
+----------+-------------------------------------------------------+
| Table    | Create Table                                          |
+----------+-------------------------------------------------------+
| bulkload | CREATE TABLE `bulkload` (
  `id` bigint NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+----------+-------------------------------------------------------+
1 row in set (0.01 sec)

\mysql> insert into bulkload values(2147483647, 'test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bulkload values(2147483648, 'test');
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from bulkload where id = 2147483647;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | bulkload | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from bulkload where id = 2147483648;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | bulkload | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

PostgreSQL

psql> \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description
-----------+---------+------------+--------------------------------------------------
 mysql_fdw | 1.2     | public     | Foreign data wrapper for querying a MySQL server
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

psql> import foreign schema hammerdb limit to ("bulkload") from server test_fdw into public;
IMPORT FOREIGN SCHEMA

psql> \d+ public.bulkload
                                            Foreign table "public.bulkload"
 Column |         Type          | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+-------------+----------+--------------+-------------
 id     | bigint                |           | not null |         |             | plain    |              |
 name   | character varying(10) |           |          |         |             | extended |              |
Server: test_fdw
FDW options: (dbname 'hammerdb', table_name 'bulkload')

psql> explain verbose select * from public.bulkload where id = 2147483647;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Foreign Scan on public.bulkload  (cost=25.00..27.00 rows=2 width=46)
   Output: id, name
   Remote server startup cost: 25
   Remote query: SELECT `id`, `name` FROM `hammerdb`.`bulkload` WHERE ((`id` = 2147483647))
(4 rows)

psql> explain verbose select * from public.bulkload where id = 2147483648;
                              QUERY PLAN
----------------------------------------------------------------------
 Foreign Scan on public.bulkload  (cost=25.00..29.00 rows=4 width=46)
   Output: id, name
   Filter: (bulkload.id = '2147483648'::bigint)
   Remote server startup cost: 25
   Remote query: SELECT `id`, `name` FROM `hammerdb`.`bulkload`
(5 rows)

[surajkharage19]

Hi @kimjinju730 and @giant-ksj,

I am able to reproduce this issue at my end. I think the issue with operator =(bigint, bigint). If the value is within int range then =(bigint,integer) operator is getting used which is already there in mysql_fdw_pushdown.config file.

Can you please add the below required entry in mysql_fdw_pushdown.config file and check? e.g. OPERATOR pg_catalog.=(bigint,bigint)

Once that is added you can reload the config file using select mysql_fdw_display_pushdown_list(true); command or start a new session so that new entries can take effect. Users can add/modify/delete the entries as per their requirements from mysql_fdw_pushdown.config file.

After adding this entry in mysql_fdw_pushdown.config file, I can see where clause is getting pushdown to remote server.

edb@99543=#explain (analyze , verbose) select * from bigint_test where a = 2147483648;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.bigint_test  (cost=10.00..1010.00 rows=1000 width=16) (actual time=1.080..1.103 rows=1 loops=1)
   Output: a, b
   Local server startup cost: 10
   Remote query: SELECT `a`, `b` FROM `suraj`.`bigint_test` WHERE ((`a` = 2147483648))
 Planning Time: 22.412 ms
 Execution Time: 2.308 ms
(6 rows)

edb@99543=#
edb@99543=#\d bigint_test
               Foreign table "public.bigint_test"
 Column |  Type  | Collation | Nullable | Default | FDW options 
--------+--------+-----------+----------+---------+-------------
 a      | bigint |           |          |         | 
 b      | bigint |           |          |         | 
Server: mysql_server
FDW options: (dbname 'suraj', table_name 'bigint_test')

Hope this helps.

[giant-ksj]

@surajkharage19 Thank you. It works greatly.

$ grep "pg_catalog.=(bigint,bigint)" /usr/pgsql-13/share/extension/mysql_fdw_pushdown.config
OPERATOR pg_catalog.=(bigint,bigint)

psql> explain verbose select * from public.bulkload where id = 2147483648;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Foreign Scan on public.bulkload  (cost=25.00..27.00 rows=2 width=46)
   Output: id, name
   Remote server startup cost: 25
   Remote query: SELECT `id`, `name` FROM `hammerdb`.`bulkload` WHERE ((`id` = 2147483648))
(4 rows)

[surajkharage19]

Thanks for your feedback.