So we've come across a bug that seems to happen when using any multicorn foreign data wrapper. I think this is a fairly major issue, as rather than throwing an error the wrong data can be returned. In this example foreigntab is a foreign table with 2 float columns. If we run this:
select avg(float_b)::double precision from foreigntab where float_b > 0;
avg
-------------------
0.540655263477848
(1 row)
We can calculate the average, but if I put that same query as a subquery in the WHERE clause I get this:
select float_a from foreigntab where float_a > (select avg(float_b) from foreigntab where float_b > 0);
float_a
---------
(0 rows)
There should be 30 rows returned here rather than 0. The reason they are not is that the value of the sub-query is coming back as 902.095 rather than 0.5406. If we manually cast the return value to a real the behaviour is as expected:
select float_a from foreigntab where float_a > (select avg(float_b)::real from foreigntab where float_b > 0);
float_a
----------
0.698564
0.675077
...
(30 rows)
So basically multicorn expects the value returned from the sub-query to be a real, and when it comes back as a double precision (due to the use of AVG) it is cast incorrectly and a garbage value comes out (902.095).
I've dug through the C code a little and it seems like the issue relates somehow to the ConversionInfo. As far as I can tell this is set in multicornBeginForeignScan, before any sub-queries are run. Whenever multicornIterateForeignScan is run and the sub-query returns an answer this is sent to python via qualdefToPython, which gets type information from the ConversionInfo. In the case above the type info seems to have been assumed before execution to be a 4 byte float and never updated, so when the answer that is returned is actually an 8 byte float the conversion returns the wrong value.
This issue doesn't seem to happen with the PostgreSQL native FDW, and to be honest I don't know why, I think my understanding of the C API is not deep enough to get to the real source of the issue. Maybe someone here can help out?
So we've come across a bug that seems to happen when using any multicorn foreign data wrapper. I think this is a fairly major issue, as rather than throwing an error the wrong data can be returned. In this example foreigntab is a foreign table with 2 float columns. If we run this:
We can calculate the average, but if I put that same query as a subquery in the WHERE clause I get this:
There should be 30 rows returned here rather than 0. The reason they are not is that the value of the sub-query is coming back as 902.095 rather than 0.5406. If we manually cast the return value to a real the behaviour is as expected:
So basically multicorn expects the value returned from the sub-query to be a real, and when it comes back as a double precision (due to the use of AVG) it is cast incorrectly and a garbage value comes out (902.095).
I've dug through the C code a little and it seems like the issue relates somehow to the ConversionInfo. As far as I can tell this is set in multicornBeginForeignScan, before any sub-queries are run. Whenever multicornIterateForeignScan is run and the sub-query returns an answer this is sent to python via qualdefToPython, which gets type information from the ConversionInfo. In the case above the type info seems to have been assumed before execution to be a 4 byte float and never updated, so when the answer that is returned is actually an 8 byte float the conversion returns the wrong value.
This issue doesn't seem to happen with the PostgreSQL native FDW, and to be honest I don't know why, I think my understanding of the C API is not deep enough to get to the real source of the issue. Maybe someone here can help out?