incorrect query result

[zhaoyuan2022]

I have a pg12 running on ubuntu, for mysql_fdw select install check scenarios, these queries returns incorrect result:

1. SELECT test_param_where(); only 5 tuples returned.
2. Nested query with select constant：SELECT c1, c2 FROM f_test_tbl1 WHERE c8 = (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20)) ORDER BY c1; no tuple returned.

If I ingest same data into a local table, I would get a correct result.

[zhaoyuan2022]

Here is the plan of the two queries, we can see the result of select 10 is not set to the where clause. :

contrib_regression=# explain verbose select * from f_test_tbl2 where c1 = (select 10);
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl2  (cost=10.01..1010.01 rows=1000 width=94)
   Output: f_test_tbl2.c1, f_test_tbl2.c2, f_test_tbl2.c3
   Local server startup cost: 10
   Remote query: SELECT `c1`, `c2`, `c3` FROM `mysql_fdw_regress`.`test_tbl2` WHERE ((`c1` = ?))
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4)
           Output: 10
(7 rows)

contrib_regression=# explain verbose select * from f_test_tbl2 where c1 in  (select 10);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_test_tbl2  (cost=10.00..1010.00 rows=1000 width=94)
   Output: f_test_tbl2.c1, f_test_tbl2.c2, f_test_tbl2.c3
   Local server startup cost: 10
   Remote query: SELECT `c1`, `c2`, `c3` FROM `mysql_fdw_regress`.`test_tbl2` WHERE ((`c1` = 10))
(4 rows)

I have a similar scenarios with postgres_fdw, I can get the correct result, and the plan like：

contrib_regression=# explain verbose select * from testa where id = (select 10);
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Foreign Scan on public.testa  (cost=100.01..121.22 rows=4 width=68)
   Output: testa.id, testa.title, testa.body
   Remote SQL: SELECT id, title, body FROM public.testl WHERE ((id = $1::integer))
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4)
           Output: 10
(6 rows)

contrib_regression=# explain verbose select * from testa where id in (select 10);
                                QUERY PLAN
--------------------------------------------------------------------------
 Foreign Scan on public.testa  (cost=100.00..121.21 rows=4 width=68)
   Output: testa.id, testa.title, testa.body
   Remote SQL: SELECT id, title, body FROM public.testl WHERE ((id = 10))
(3 rows)

[zhaoyuan2022]

The root cause is in this function:

static void
mysql_print_remote_param(int paramindex, Oid paramtype, int32 paramtypmod,
                         deparse_expr_cxt *context)
{
    StringInfo  buf = context->buf;
    appendStringInfo(buf, "?");
}

The params return by subquery is deparsed into ? directly. I was trying to deparse it into params $1, but it seems that $1 is not recognized by mysql:

static void
mysql_print_remote_param(int paramindex, Oid paramtype, int32 paramtypmod,
                 deparse_expr_cxt *context)
{
    StringInfo  buf = context->buf;
    appendStringInfo(buf, "$%d, paramindex);
}

The plan like :

contrib_regression=# explain verbose select * from f_test_tbl2 where c1 = (select 10);
ERROR:  failed to prepare the MySQL query:
Unknown column '$1' in 'where clause'

@jeevanchalke could you please help to take a look at this issue, or how can you guys make installcheck passed? The mysql version is 8.0.30-0ubuntu0.20.04.2, do I need another version that can work properly?

[surajkharage19]

Thanks, @zhaoyuan2022 for reporting this issue.

I am not able to reproduce this issue on Linux/CentOS platform and it seems this is only reproducible on ubuntu. On a quick look, it doesn't look like MySQL version issue, however we are investigating this issue further and will update you.

[zhaoyuan2022]

@surajkharage19 Thanks for your reply, I will also do more effort on this issue.

[1165125080]

I also reproduced this problem. My version is pg13.9 in centos8 and mysql_fdw 2.9.0. When executing the test case for mysql_fdw, it failed several times, including the questioner's problem executing SELECT test_param_where();. This issue occurs only when postgres uses the CFLAGS="-fstack-protector-strong" compilation option. I think it's a stack overflow somewhere in the mysql_fdw code, but I can't locate it.

The following is part of the error in executing the use case

diff -U3 /home/pg13.9/mysql_fdw/mysql_fdw-REL-2_9_0/expected/select.out /home/pg13.9/mysql_fdw/mysql_fdw-REL-2_9_0/results/select.out
--- /home/pg13.9/mysql_fdw/mysql_fdw-REL-2_9_0/expected/select.out      2022-12-14 15:09:45.000000000 +0800
+++ /home/pg13.9/mysql_fdw/mysql_fdw-REL-2_9_0/results/select.out       2023-01-03 14:31:25.561528483 +0800
@@ -546,10 +546,10 @@
 NOTICE:  Found number Three
 NOTICE:  Found number Four
 NOTICE:  Found number Five
-NOTICE:  Found number Six
-NOTICE:  Found number Seven
-NOTICE:  Found number Eight
-NOTICE:  Found number Nine
+NOTICE:  Found number <NULL>
+NOTICE:  Found number <NULL>
+NOTICE:  Found number <NULL>
+NOTICE:  Found number <NULL>
  test_param_where
 ------------------

@@ -561,7 +561,7 @@
 SELECT test_param_where2(1, 'One');
  test_param_where2
 -------------------
-                 1
+                  
 (1 row)

 -- Foreign-Foreign table joins
@@ -922,7 +922,7 @@
   SELECT t2.a, t1.a AS t1_a FROM f_mysql_test t2) t3 ON t1.a = t3.a ORDER BY 1;
  a | b | a | t1_a
 ---+---+---+------
- 1 | 1 | 1 |    1
+ 1 | 1 |   |     
 (1 row)

 SELECT t1.c1, t3.c1, t3.t1_c8 FROM f_test_tbl1 t1 INNER JOIN LATERAL (
@@ -1219,30 +1219,30 @@
 SELECT c1, c2 FROM f_test_tbl1
   WHERE c8 = (SELECT c1 FROM f_test_tbl2 WHERE c1 = (SELECT 20))
   ORDER BY c1;
-  c1  |  c2   
-------+-------
-  100 | EMP1
-  400 | EMP4
-  800 | EMP8
- 1100 | EMP11
- 1300 | EMP13
-(5 rows)
+ c1 | c2 
+----+----
+(0 rows)

[surajkharage19]

Thanks for the update on this.

I am able to reproduce this issue at my end after adding CFLAGS="-fstack-protector-strong" compilation flag. Will further check on this.