Error: mergejoin input data is out of order

[lzbht]

version:

1. PostgreSQL 16.4 (Debian 16.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2. mysql_fdw: 1.2
3. two different mysql. 5.7.36-txsql-log and 5.7.14

sql:
select * from t1 left join t2 on t1.user_name = t2.account

column info:
user_name: text
account: varchar(100)

Somebody also meet the same error: https://www.postgresql.org/message-id/flat/BL0PR05MB66282D22D96AB745FBBD521FF3CF9%40BL0PR05MB6628.namprd05.prod.outlook.com#2a653156ddf60d428015996145786fd2

[surajkharage19]

Hi @lzbht,

I tried to reproduce the issue at my end using mysql_fdw but no luck. Tried below test case -

postgres@47884=#\d f_t1
                   Foreign table "public.f_t1"
  Column   | Type | Collation | Nullable | Default | FDW options 
-----------+------+-----------+----------+---------+-------------
 user_name | text |           |          |         | 
Server: mysql_server
FDW options: (dbname 'suraj', table_name 't1')

postgres@47884=#\d f_t2
                           Foreign table "public.f_t2"
 Column  |          Type          | Collation | Nullable | Default | FDW options 
---------+------------------------+-----------+----------+---------+-------------
 account | character varying(100) |           |          |         | 
Server: mysql_server
FDW options: (dbname 'suraj', table_name 't2')

postgres@47884=#select * from f_t1 left join f_t2 on f_t1.user_name  = f_t2.account;
 user_name | account 
-----------+---------
 a         | 
 b         | b
(2 rows)

postgres@47884=#select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

Can you please share the exact reproducible test case for the same so that we can reproduce the same at our end?

[lzbht]

@surajkharage19
emm, I try a lot of times, not sure the reason why it will cause error.
Maybe the difference between utf8mb4_general_ci and utf8mb4_bin?
And if I wirte sql like this SELECT * FROM t1 LEFT JOIN t2 ON t1.user_name COLLATE "en_US.utf8" = t2.account COLLATE "en_US.utf8"; , it will success

[surajkharage19]

Hi @lzbht,

Thanks for sharing the details, but I am still not able to reproduce the issue. Can you please the exact steps for the same? Are you able to reproduce the issue consistently or did it occur only once?

The collations you are talking about from MySQL side? Did you try the same scenario against MySQL 8? Please share some more details on the same.

[lzbht]

Hi @lzbht,

Thanks for sharing the details, but I am still not able to reproduce the issue. Can you please the exact steps for the same? Are you able to reproduce the issue consistently or did it occur only once?

The collations you are talking about from MySQL side? Did you try the same scenario against MySQL 8? Please share some more details on the same.

@surajkharage19
Consistently.
Yea, from MySQL side and mysql_fdw can't manually set collate rules.
I didn't try with MySQL 8. I try with two different Mysql (version are 5.7.14 and 5.7.32-log), but they are successed
I found the collation_database was different between t1 and t2. One is utf8_general_ci, the other one is utf8mb4_bin

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME IN ('yyy');

show variables like 'collation_server'

So, I built two new databases with the same collation_database. But still succeeded.

[surajkharage19]

Thanks @lzbht for sharing the details.

Can you please try the same test case with MySQL 8 and let us know the result that helps us to narrow down the issue?