Simple JOIN not working

jefft commented 7 years ago

Hi,

I have two MySQL tables, tickets and ticketcomments, visible in psql. I can select from them individually:

testdb=# select * from tickets;
+-------+
|  id   |
+-------+
| 58060 |
| 58061 |
+-------+
(2 rows)

testdb=# select * from ticketcomments;
+---------+----------+
|   id    | ticketid |
+---------+----------+
| 2287173 |    58059 |
| 2287174 |    58059 |
| 2287175 |    58059 |
| 2287176 |    58059 |
| 2287177 |    58059 |
| 2287178 |    58059 |
| 2287183 |    58060 |
| 2287184 |    58060 |
| 2287185 |    58060 |
| 2287187 |    43819 |
| 2287188 |    43819 |
| 2287189 |    58038 |
| 2287190 |    58038 |
| 2287191 |    58038 |
| 2287196 |    58061 |
| 2287197 |    58061 |
| 2287198 |    58061 |
| 2287200 |    58061 |
+---------+----------+
(18 rows)

But a JOIN gives incorrect results:

testdb=# select * From tickets JOIN ticketcomments ON tickets.id=ticketcomments.ticketid;
+-------+---------+----------+
|  id   |   id    | ticketid |
+-------+---------+----------+
| 58060 | 2287183 |    58060 |
| 58060 | 2287184 |    58060 |
| 58060 | 2287185 |    58060 |
+-------+---------+----------+
(3 rows)

(notice that tickets.id = 58061 should join but doesn't)

Bizarrely, if I switch the table order (which shouldn't make a difference) I get even fewer results:

testdb=# select * From ticketcomments JOIN tickets ON tickets.id=ticketcomments.ticketid;
+----+----------+----+
| id | ticketid | id |
+----+----------+----+
+----+----------+----+
(0 rows)

Here is my MySQL ddl:

DROP TABLE IF EXISTS `tickets`;
CREATE TABLE `tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

INSERT INTO `tickets` VALUES (58060);
INSERT INTO `tickets` VALUES (58061);

DROP TABLE IF EXISTS `ticketcomments`;
CREATE TABLE `ticketcomments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticketid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `ticketcomments` VALUES (2287187,43819);
INSERT INTO `ticketcomments` VALUES (2287188,43819);
INSERT INTO `ticketcomments` VALUES (2287189,58038);
INSERT INTO `ticketcomments` VALUES (2287190,58038);
INSERT INTO `ticketcomments` VALUES (2287191,58038);
INSERT INTO `ticketcomments` VALUES (2287173,58059);
INSERT INTO `ticketcomments` VALUES (2287174,58059);
INSERT INTO `ticketcomments` VALUES (2287175,58059);
INSERT INTO `ticketcomments` VALUES (2287176,58059);
INSERT INTO `ticketcomments` VALUES (2287177,58059);
INSERT INTO `ticketcomments` VALUES (2287178,58059);
INSERT INTO `ticketcomments` VALUES (2287183,58060);
INSERT INTO `ticketcomments` VALUES (2287184,58060);
INSERT INTO `ticketcomments` VALUES (2287185,58060);
INSERT INTO `ticketcomments` VALUES (2287196,58061);
INSERT INTO `ticketcomments` VALUES (2287197,58061);
INSERT INTO `ticketcomments` VALUES (2287198,58061);
INSERT INTO `ticketcomments` VALUES (2287200,58061);

and Postgres:

CREATE DATABASE testdb;
\c testdb;
CREATE EXTENSION mysql_fdw;
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306', use_remote_estimate 'true');
CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'root', password 'secret');
IMPORT FOREIGN SCHEMA testdb FROM SERVER mysql_server INTO public;

I am using Postgres 9.5.4 and MySQL 5.7.15.

jefft commented 7 years ago

I wonder if this is the same as https://github.com/EnterpriseDB/mysql_fdw/issues/73, and perhaps https://github.com/EnterpriseDB/mysql_fdw/issues/102. I tweaked the source to print something in mysqlReScanForeignScan, I can see it is indeed being called, but is unimplemented.

jmealo commented 7 years ago

:+1: I encountered this as well. It seems like the function signatures are updated to match the latest FDW API to fix compilation but no effort is made to actually implement the API changes.

gabbasb commented 7 years ago

@jefft I was assigned to fix this issue, however it does not reproduce for me by following the steps you have mentioned. If you can help me reproduce this issue that would be great.

Here are the steps I performed: On MySql:


mysql> CREATE TABLE `tickets` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO `tickets` VALUES (58060);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `tickets` VALUES (58061);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> DROP TABLE IF EXISTS `ticketcomments`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `ticketcomments` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `ticketid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287187,43819);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287188,43819);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287189,58038);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287190,58038);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287191,58038);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287173,58059);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287174,58059);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287175,58059);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287176,58059);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287177,58059);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287178,58059);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287183,58060);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287184,58060);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287185,58060);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287196,58061);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287197,58061);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287198,58061);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `ticketcomments` VALUES (2287200,58061);
Query OK, 1 row affected (0.01 sec)

mysql> select * From tickets JOIN ticketcomments ON tickets.id=ticketcomments.ticketid;
+-------+---------+----------+
| id    | id      | ticketid |
+-------+---------+----------+
| 58060 | 2287183 |    58060 |
| 58060 | 2287184 |    58060 |
| 58060 | 2287185 |    58060 |
| 58061 | 2287196 |    58061 |
| 58061 | 2287197 |    58061 |
| 58061 | 2287198 |    58061 |
| 58061 | 2287200 |    58061 |
+-------+---------+----------+
7 rows in set (0.01 sec)

test=# IMPORT FOREIGN SCHEMA msql_test_db FROM SERVER mysql_server INTO ts;
IMPORT FOREIGN SCHEMA
test=# 
test=# select * from ts.tickets;
INFO:  Successfully connected to MySQL database msql_test_db at server 127.0.0.1 via TCP/IP with cipher <none> (server version: 5.7.17, protocol version: 10) 
  id   
-------
 58060
 58061
(2 rows)

test=# \d ts.tickets;
         Foreign table "ts.tickets"
 Column |  Type   | Modifiers | FDW Options 
--------+---------+-----------+-------------
 id     | integer | not null  | 
Server: mysql_server
FDW Options: (dbname 'msql_test_db', table_name 'tickets')

test=# select * From ts.tickets JOIN ts.ticketcomments ON ts.tickets.id=ts.ticketcomments.ticketid;
  id   |   id    | ticketid 
-------+---------+----------
 58060 | 2287185 |    58060
 58060 | 2287184 |    58060
 58060 | 2287183 |    58060
 58061 | 2287200 |    58061
 58061 | 2287198 |    58061
 58061 | 2287197 |    58061
 58061 | 2287196 |    58061
(7 rows)

Regards Abbas

jefft commented 7 years ago

@gabbasb, thanks for checking. I can no longer replicate the issue with pg 9.5.6 and mysql_fwd 2.2.0 or even with the version from git at 30 Sept 2016. Perhaps the weird results were from an invalid binary. As I discovered on #132, one must run make USE_PGXS=1 clean install, not just make USE_PGXS=1 install.

EnterpriseDB / mysql_fdw

Simple JOIN not working #119