search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
535 stars 162 forks source link

Erratic behavior due to mysqlReScanForeignScan not being implemented #73

Closed jmealo closed 7 years ago

jmealo commented 8 years ago

mysqlReScanForeignScan is not optional. Some queries fail entirely or partially without rescans working. I wanted to draw attention to this and see what progress has been made implementing it?

https://github.com/EnterpriseDB/mysql_fdw/blob/d9836a345d820ce419063bf46c0ed744e5e3ba87/mysql_fdw.c#L682

Thanks, Jeff

ibrarahmad commented 8 years ago

Yes, i am working on this.

On Mon, Nov 9, 2015 at 8:22 PM, Jeffrey notifications@github.com wrote:

mysqlReScanForeignScan is not optional. Some queries fail entirely or partially without rescans working. I wanted to draw attention to this and see what progress has been made implementing it?

Thanks, Jeff

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/73.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

jmealo commented 8 years ago

@ibrarahmad Thank you. It struck me as quite a surprise that the problems I encountered were due to mysql_fdw (rather than my query). Besides this issue mysql_fdw has done a fine job of pushing down what it's able to. Kudos on the great work.

ahsanhadi commented 8 years ago

On Tue, Nov 10, 2015 at 12:48 AM, Jeffrey notifications@github.com wrote:

@ibrarahmad https://github.com/ibrarahmad Thank you. It struck me as quite a surprise that the problems I encountered were due to mysql_fdw (rather than my query). Besides this issue mysql_fdw has done a fine job of pushing down what it's able to. Kudos on the great work.

Thanks... We have the following outstanding issues to look at for mysql_fdw. We will try and get to these issues addressed in the next two weeks.

- https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen Erratic behavior due to mysqlReScanForeignScan not being implemented https://github.com/EnterpriseDB/mysql_fdw/issues/73

73 opened 13 hours ago by jmealo

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3Ajmealo

2 <https://github.com/EnterpriseDB/mysql_fdw/issues/73>

- https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen PostgresSQL join with 2 mysql tables by foreign data wrapper https://github.com/EnterpriseDB/mysql_fdw/issues/72

72 opened 5 days ago by Borkache

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3ABorkache

0 <https://github.com/EnterpriseDB/mysql_fdw/issues/72>

- https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen NULL constraint violation https://github.com/EnterpriseDB/mysql_fdw/issues/71

71 opened 11 days ago by studdugie

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3Astuddugie

0 <https://github.com/EnterpriseDB/mysql_fdw/issues/71>

- https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen btrim does not exist https://github.com/EnterpriseDB/mysql_fdw/issues/70

70 opened 11 days ago by studdugie

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3Astuddugie

—

Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mysql_fdw/issues/73#issuecomment-155170813 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

ibrarahmad commented 8 years ago

Jeffrey: Can you please share sample queries which are not working?

On Tue, Nov 10, 2015 at 9:05 AM, Ahsan Hadi notifications@github.com wrote:

On Tue, Nov 10, 2015 at 12:48 AM, Jeffrey notifications@github.com wrote:

@ibrarahmad https://github.com/ibrarahmad Thank you. It struck me as quite a surprise that the problems I encountered were due to mysql_fdw (rather than my query). Besides this issue mysql_fdw has done a fine job of pushing down what it's able to. Kudos on the great work.

Thanks... We have the following outstanding issues to look at for mysql_fdw. We will try and get to these issues addressed in the next two weeks.

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen Erratic behavior due to mysqlReScanForeignScan not being implemented https://github.com/EnterpriseDB/mysql_fdw/issues/73

73 opened 13 hours ago by jmealo

< https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3Ajmealo

2 https://github.com/EnterpriseDB/mysql_fdw/issues/73

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen PostgresSQL join with 2 mysql tables by foreign data wrapper https://github.com/EnterpriseDB/mysql_fdw/issues/72

72 opened 5 days ago by Borkache

< https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3ABorkache

0 https://github.com/EnterpriseDB/mysql_fdw/issues/72

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen NULL constraint violation https://github.com/EnterpriseDB/mysql_fdw/issues/71

71 opened 11 days ago by studdugie

< https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3Astuddugie

0 https://github.com/EnterpriseDB/mysql_fdw/issues/71

https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen btrim does not exist https://github.com/EnterpriseDB/mysql_fdw/issues/70

70 opened 11 days ago by studdugie

< https://github.com/EnterpriseDB/mysql_fdw/issues?q=is%3Aissue+is%3Aopen+author%3Astuddugie

—

Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mysql_fdw/issues/73#issuecomment-155170813> .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

— Reply to this email directly or view it on GitHub.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

jmealo commented 8 years ago

The query we're using in production right now is:

   SELECT "teacher_feedback".*,
          ("local_people"."FirstName" || ' ' || "local_people"."LastName") AS author_name
     FROM "teacher_feedback"
LEFT JOIN "local_people" ON "teacher_feedback"."author_id" = "local_people"."ID"
    WHERE "student_id" = ?
      AND "sparkpoint_id" = ?

The local_people table was created by doing:

CREATE TABLE local_people AS SELECT * FROM people;

Where people was a foreign table using mysql_fdw. When the query was using people and mysql_fdw:

   SELECT "teacher_feedback".*,
          ("people"."FirstName" || ' ' || "people"."LastName") AS author_name
     FROM "teacher_feedback"
LEFT JOIN "people" ON "teacher_feedback"."author_id" = "people"."ID"
    WHERE "student_id" = ?
      AND "sparkpoint_id" = ?

The query would work as expected except only the first matched row got an author_name due to rescan not being enabled.

The same query works fine when querying against a local/native table.

ahsanhadi commented 8 years ago

Can you send the complete test case along with table DDL and sample data so we are able to reproduce the issue.

jmealo commented 8 years ago

MySQL

CREATE TABLE `people` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Class` enum('Emergence\\People\\Person','Emergence\\People\\User','Slate\\People\\Student') NOT NULL,
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatorID` int(11) DEFAULT NULL,
  `FirstName` varchar(255) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `MiddleName` varchar(255) DEFAULT NULL,
  `Gender` enum('Male','Female') DEFAULT NULL,
  `BirthDate` date DEFAULT NULL,
  `Location` varchar(255) DEFAULT NULL,
  `About` text,
  `PrimaryPhotoID` int(10) unsigned DEFAULT NULL,
  `PrimaryEmailID` int(10) unsigned DEFAULT NULL,
  `PrimaryPhoneID` int(10) unsigned DEFAULT NULL,
  `PrimaryPostalID` int(10) unsigned DEFAULT NULL,
  `Username` varchar(255) DEFAULT NULL,
  `Password` varchar(255) DEFAULT NULL,
  `AccountLevel` enum('Disabled','Contact','User','Student','Staff','Teacher','Administrator','Developer') DEFAULT 'User',
  `StudentNumber` varchar(255) DEFAULT NULL,
  `AdvisorID` int(10) unsigned DEFAULT NULL,
  `GraduationYear` year(4) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Username` (`Username`),
  UNIQUE KEY `StudentNumber` (`StudentNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (1,'Emergence\\People\\User','2015-08-12 01:23:04',1,'Chris','Doe',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Developer',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (2,'Emergence\\People\\User','2015-08-12 00:19:08',2,'Christian','Doe',NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,'Developer',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (3,'Emergence\\People\\User','2015-08-12 09:06:37',3,'Nithi','Doe',NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (4,'Emergence\\People\\User','2015-08-12 16:29:45',4,'Amy','Doe',NULL,NULL,NULL,NULL,NULL,1,3,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (5,'Slate\\People\\Student','2015-08-12 18:07:56',2,'Jon','Doe',NULL,NULL,NULL,NULL,NULL,NULL,4,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (6,'Slate\\People\\Student','2015-08-12 18:07:56',2,'Sherrill','Doe',NULL,NULL,NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (7,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Clarisa','Doe',NULL,NULL,NULL,NULL,NULL,NULL,6,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (8,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Shon','Doe',NULL,NULL,NULL,NULL,NULL,NULL,7,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (9,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Edmund','Doe',NULL,NULL,NULL,NULL,NULL,NULL,8,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (10,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Jenise','Doe',NULL,NULL,NULL,NULL,NULL,NULL,9,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (11,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Tiffany','Doe',NULL,NULL,NULL,NULL,NULL,NULL,10,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (12,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Leonard','Doe',NULL,NULL,NULL,NULL,NULL,NULL,11,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (13,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Nydia','Doe',NULL,NULL,NULL,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (14,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Alysha','Doe',NULL,NULL,NULL,NULL,NULL,NULL,13,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (15,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Alfonso','Doe',NULL,NULL,NULL,NULL,NULL,NULL,14,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (16,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Laree','Doe',NULL,NULL,NULL,NULL,NULL,NULL,15,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2018);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (17,'Slate\\People\\Student','2015-08-12 18:07:57',2,'Bev','Doe',NULL,NULL,NULL,NULL,NULL,NULL,16,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (18,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Sherie','Doe',NULL,NULL,NULL,NULL,NULL,NULL,17,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (19,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Mollie','Doe',NULL,NULL,NULL,NULL,NULL,NULL,18,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (20,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Antoine','Doe',NULL,NULL,NULL,NULL,NULL,NULL,19,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (21,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Madalene','Doe',NULL,NULL,NULL,NULL,NULL,NULL,20,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (22,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Marg','Doe',NULL,NULL,NULL,NULL,NULL,NULL,21,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (23,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Maribel','Doe',NULL,NULL,NULL,NULL,NULL,NULL,22,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (24,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Omer','Doe',NULL,NULL,NULL,NULL,NULL,NULL,23,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (25,'Slate\\People\\Student','2015-08-12 18:07:58',2,'Sammy','Doe',NULL,NULL,NULL,NULL,NULL,NULL,24,NULL,NULL,NULL,NULL,'Student',NULL,NULL,2017);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (52,'Emergence\\People\\User','2015-10-23 19:49:09',52,'Alison','Doe',NULL,NULL,NULL,NULL,NULL,NULL,51,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (51,'Emergence\\People\\User','2015-10-23 19:45:08',51,'Robert','Doe',NULL,NULL,NULL,NULL,NULL,NULL,50,NULL,NULL,NULL,NULL,'User',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (26,'Emergence\\People\\User','2015-08-12 23:36:31',26,'John','Doe',NULL,NULL,NULL,NULL,NULL,NULL,25,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (27,'Emergence\\People\\User','2015-08-12 23:55:44',27,'Al','Doe',NULL,NULL,NULL,NULL,NULL,3,26,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (28,'Emergence\\People\\User','2015-08-12 23:55:51',2,'Sajan','Doe',NULL,NULL,NULL,NULL,NULL,NULL,27,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (29,'Emergence\\People\\User','2015-08-24 16:41:29',29,'Mike','Doe',NULL,NULL,NULL,NULL,NULL,NULL,28,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (30,'Emergence\\People\\User','2015-08-25 16:45:01',1,'Ryon','Doe',NULL,NULL,NULL,NULL,NULL,NULL,29,NULL,NULL,NULL,NULL,'Developer',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (31,'Slate\\People\\Student','2015-08-26 11:07:43',31,'Chris','Doe',NULL,NULL,NULL,NULL,NULL,NULL,30,NULL,NULL,NULL,NULL,'Student',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (32,'Emergence\\People\\User','2015-09-21 10:56:27',1,'Jeff','Doe',NULL,NULL,NULL,NULL,NULL,NULL,31,NULL,NULL,NULL,NULL,'Developer',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (33,'Emergence\\People\\User','2015-09-29 21:25:54',33,'Bill','Doe',NULL,NULL,NULL,NULL,NULL,NULL,32,NULL,NULL,NULL,NULL,'Developer',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (34,'Emergence\\People\\User','2015-10-14 10:42:42',34,'Ali','Doe',NULL,NULL,NULL,NULL,NULL,NULL,33,NULL,NULL,NULL,NULL,'User',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (35,'Emergence\\People\\User','2015-10-15 07:30:36',35,'Tiffany','Doe',NULL,NULL,NULL,NULL,NULL,NULL,34,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (36,'Emergence\\People\\User','2015-10-15 10:57:51',36,'Laurance','Doe',NULL,NULL,NULL,NULL,NULL,4,35,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (37,'Emergence\\People\\User','2015-10-16 07:35:39',37,'Donald','Doe',NULL,NULL,NULL,NULL,NULL,NULL,36,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (38,'Emergence\\People\\User','2015-10-16 10:55:28',38,'Tommy','Doe',NULL,NULL,NULL,NULL,NULL,5,37,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (39,'Emergence\\People\\User','2015-10-16 11:04:43',39,'Rashad','Doe',NULL,NULL,NULL,NULL,NULL,NULL,38,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (40,'Emergence\\People\\User','2015-10-19 10:28:24',40,'Ron','Doe',NULL,NULL,NULL,NULL,NULL,NULL,39,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (41,'Emergence\\People\\User','2015-10-19 10:51:31',41,'Autumn','Doe',NULL,NULL,NULL,NULL,NULL,NULL,40,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (42,'Emergence\\People\\User','2015-10-19 12:34:05',42,'Kristin','Doe',NULL,NULL,NULL,NULL,NULL,NULL,41,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (43,'Emergence\\People\\User','2015-10-19 14:48:13',43,'sasha','Doe',NULL,NULL,NULL,NULL,NULL,NULL,42,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (44,'Emergence\\People\\User','2015-10-19 14:49:19',44,'Krystal','Doe',NULL,NULL,NULL,NULL,NULL,NULL,43,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (45,'Emergence\\People\\User','2015-10-19 15:27:28',45,'Samuel','Doe',NULL,NULL,NULL,NULL,NULL,NULL,44,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (46,'Emergence\\People\\User','2015-10-20 10:44:10',46,'Margina','Doe',NULL,NULL,NULL,NULL,NULL,6,45,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (47,'Emergence\\People\\User','2015-10-20 13:58:06',47,'Gregory','Doe',NULL,NULL,NULL,NULL,NULL,NULL,46,NULL,NULL,NULL,NULL,'Developer',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (48,'Emergence\\People\\User','2015-10-20 17:22:00',48,'Charmaine','Doe',NULL,NULL,NULL,NULL,NULL,NULL,47,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (49,'Emergence\\People\\User','2015-10-21 12:24:03',49,'Douglas','Doe',NULL,NULL,NULL,NULL,NULL,NULL,48,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (50,'Emergence\\People\\User','2015-10-22 10:52:08',50,'Thomas','Doe',NULL,NULL,NULL,NULL,NULL,NULL,49,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (53,'Emergence\\People\\User','2015-10-24 09:56:27',53,'crystal','Doe',NULL,NULL,NULL,NULL,NULL,NULL,52,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (54,'Emergence\\People\\User','2015-10-25 00:05:01',54,'Ashley','Doe',NULL,NULL,NULL,NULL,NULL,NULL,53,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (55,'Emergence\\People\\User','2015-10-26 12:45:31',55,'Chris','Doe',NULL,NULL,NULL,NULL,NULL,NULL,54,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (56,'Emergence\\People\\User','2015-10-27 21:21:14',56,'Kristen','Doe',NULL,NULL,NULL,NULL,NULL,NULL,55,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (57,'Emergence\\People\\User','2015-10-28 05:38:48',3,'Tommy','Doe',NULL,NULL,NULL,NULL,NULL,NULL,56,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (58,'Emergence\\People\\User','2015-10-29 07:13:52',58,'Leon','Doe',NULL,NULL,NULL,NULL,NULL,NULL,57,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (59,'Emergence\\People\\User','2015-10-29 12:55:36',59,'Katya','Doe',NULL,NULL,NULL,NULL,NULL,NULL,58,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (60,'Emergence\\People\\User','2015-10-30 15:51:50',60,'Terence','Doe',NULL,NULL,NULL,NULL,NULL,NULL,59,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (61,'Emergence\\People\\User','2015-10-30 15:54:44',3,'Nicole','Doe',NULL,NULL,NULL,NULL,NULL,NULL,60,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (62,'Emergence\\People\\User','2015-10-30 21:56:56',62,'Cheryl','Doe',NULL,NULL,NULL,NULL,NULL,8,61,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (63,'Emergence\\People\\User','2015-10-31 16:48:59',63,'Ames','Doe',NULL,NULL,NULL,NULL,NULL,NULL,62,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (64,'Emergence\\People\\User','2015-11-02 11:20:57',64,'Susan','Doe',NULL,NULL,NULL,NULL,NULL,NULL,63,NULL,NULL,NULL,NULL,'Administrator',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (65,'Emergence\\People\\User','2015-11-03 11:11:40',65,'Angela','Doe',NULL,NULL,NULL,NULL,NULL,NULL,64,NULL,NULL,NULL,NULL,'User',NULL,NULL,NULL);
INSERT INTO `people` (`ID`,`Class`,`Created`,`CreatorID`,`FirstName`,`LastName`,`MiddleName`,`Gender`,`BirthDate`,`Location`,`About`,`PrimaryPhotoID`,`PrimaryEmailID`,`PrimaryPhoneID`,`PrimaryPostalID`,`Username`,`Password`,`AccountLevel`,`StudentNumber`,`AdvisorID`,`GraduationYear`) VALUES (66,'Emergence\\People\\User','2015-11-03 11:16:48',66,'Julianne','Doe',NULL,NULL,NULL,NULL,NULL,NULL,65,NULL,NULL,NULL,NULL,'User',NULL,NULL,NULL);

PostgreSQL

CREATE FOREIGN TABLE people (
    ID integer,
    Class text DEFAULT 'Emergence\\People\\Person',
    Created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CreatorID integer DEFAULT NULL,
    FirstName text NOT NULL,
    LastName text NOT NULL,
    MiddleName text DEFAULT NULL,
    Gender text DEFAULT NULL,
    BirthDate date DEFAULT NULL,
    Location text DEFAULT NULL,
    About text,
    PrimaryPhotoID integer DEFAULT NULL,
    PrimaryEmailID integer DEFAULT NULL,
    PrimaryPhoneID integer DEFAULT NULL,
    PrimaryPostalID integer DEFAULT NULL,
    Username text DEFAULT NULL,
    Password text DEFAULT NULL,
    AccountLevel text DEFAULT 'User',
    StudentNumber text DEFAULT NULL,
    AdvisorID integer DEFAULT NULL,
    GraduationYear char(4) DEFAULT NULL
  ) SERVER server_name
    OPTIONS (dbname '<<DATABASE_NAME>>', table_name 'people');
-- Running the failing query against local_people rather than the foreign people will work
CREATE TABLE local_people AS SELECT * FROM people;
create TABLE teacher_feedback (
    id integer not null default nextval('teacher_feedback_id_seq'::regclass),
    student_id integer,
    author_id integer,
    sparkpoint_id char(8) not null,
    phase "sandbox-school".gps_phase not null,
    message text not null,
    created_time timestamp not null default now(),
    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX teacher_feedback_pkey ON teacher_feedback (id);
CREATE INDEX teacher_feedback_student_id_sparkpoint_id_idx ON teacher_feedback (student_id);
CREATE INDEX teacher_feedback_author_id_idx ON teacher_feedback (author_id);
CREATE INDEX teacher_feedback_sparkpoint_id_phase_sparkpoint_id_idx ON teacher_feedback (sparkpoint_id, phase);
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (1, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:27:14.085295');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (2, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:27:15.251326');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (3, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:27:16.223212');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (4, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:27:17.104922');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (5, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:32:38.220784');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (6, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:32:39.364092');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (7, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 18:32:40.219691');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (8, 15, 1, 'M1000103', 'apply', 'apply feedback #1', '2015-11-08 18:33:05.391527');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (9, 15, 1, 'M1000103', 'apply', 'Apply feedbakc #2', '2015-11-08 18:33:11.513977');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (10, 15, 1, 'M1000103', 'apply', 'Apply feedback #3', '2015-11-08 18:33:20.064137');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (11, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 19:36:42.199389');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (12, 15, 1, 'M1000102', 'apply', 'Good work albert!', '2015-11-08 20:07:41.553058');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (13, 15, 1, 'M1000102', 'apply', 'teacher feedback #2', '2015-11-08 20:17:46.664832');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (14, 15, 1, 'M1000102', 'apply', 'teacher feedbakc #3', '2015-11-08 20:20:06.475180');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (15, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#4', '2015-11-08 20:20:36.684525');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (16, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#5', '2015-11-08 20:21:35.774585');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (17, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#6', '2015-11-08 20:21:47.231456');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (18, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#7', '2015-11-08 20:21:57.720954');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (19, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#8', '2015-11-08 20:24:35.765066');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (20, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#9', '2015-11-08 20:28:09.079530');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (21, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#10', '2015-11-08 20:28:45.946900');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (22, 15, 1, 'M1000102', 'apply', 'Teacher
Feedback
#11', '2015-11-08 20:29:55.276689');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (23, 15, 1, 'M1000102', 'apply', '<h1>Teacher</h1>
<h2>Feedback</h2>
<p>#12</p>', '2015-11-08 20:30:46.348125');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (24, 9, 1, 'M1000102', 'learn', 'Teacher feedback', '2015-11-08 20:32:49.856904');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (25, 15, 1, 'M1000102', 'learn', 'Teacher
Feedback', '2015-11-08 20:33:04.774047');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (26, 5, 1, 'M1000852', 'learn', 'Learn feedback #1 for Jon Snow', '2015-11-08 20:39:42.702660');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (27, 5, 1, 'M1000852', 'learn', 'Learn feedback #2 for Jon Snow', '2015-11-08 20:39:45.957053');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (28, 15, 1, 'M1000102', 'conference', 'Conference feedback #1 for Alfonso', '2015-11-08 20:42:27.500835');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (29, 15, 1, 'M1000102', 'conference', 'Conference feedback #2 for Alfonso', '2015-11-08 20:43:10.148076');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (30, 15, 1, 'M1000102', 'conference', 'Conference feedback #3 for Alfonso', '2015-11-08 20:44:10.251712');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (31, 15, 1, 'M1000102', 'learn', 'Learn feedback #2 for Alfonso', '2015-11-08 20:45:09.746302');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (32, 15, 1, 'M1000102', 'learn', 'Learn feedback #3 for Alfonso', '2015-11-08 20:46:01.347822');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (33, 15, 1, 'M1000102', 'conference', 'Conference Feedback #4 for Alfonso', '2015-11-08 21:23:48.819648');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (34, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:32:36.134558');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (35, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:32:40.474324');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (36, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:41:18.558690');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (37, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:41:19.654355');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (38, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:41:20.722185');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (39, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:41:22.283593');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (40, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 22:41:23.414498');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (41, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 17:42:54.175087');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (42, 15, 7, 'M1000103', 'learn', 'Hello feedback!', '2015-11-08 17:43:56.628020');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (43, 15, 7, 'M1000103', 'learn', 'Learn feedback', '2015-11-08 17:45:18.002621');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (44, 15, 7, 'M1000103', 'conference', 'Conference feedback', '2015-11-08 17:45:18.002621');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (45, 15, 1, 'M100008B', 'learn', 'Feedback for albert in learn phase', '2015-11-09 09:45:53.649318');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (46, 15, 1, 'M100008B', 'conference', 'Feedback for albert in conference phase', '2015-11-09 09:49:06.343158');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (47, 15, 1, 'M100008B', 'apply', 'Feedback for albert in apply phase', '2015-11-09 09:54:54.498429');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (48, 8, 1, 'M10001A2', 'conference', 'feedback', '2015-11-09 11:28:00.916846');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (49, 8, 1, 'M10001A2', 'apply', 'feedback!', '2015-11-09 11:28:50.767152');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (50, 7, 32, 'M10000FC', 'apply', 'Wow! This apply was awesome. I love how you explain it all!', '2015-11-09 12:46:55.864956');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (51, 15, 7, 'M100092F', 'learn', 'Hello feedback!', '2015-11-10 16:25:50.263223');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (52, 15, 7, 'M100092F', 'learn', 'Hello feedback!', '2015-11-10 16:27:27.231843');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (53, 15, 7, 'M100092F', 'learn', 'Hello feedback!', '2015-11-10 16:27:30.141022');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (54, 15, 1, 'M10001BE', 'learn', 'aoeu', '2015-11-10 22:41:48.841853');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (55, 15, 1, 'M1000011', 'conference', 'feedback', '2015-11-11 04:43:12.639286');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (56, 15, 27, 'M1000102', 'conference', 'Keep looking at the fraction formula in the space provided.', '2015-11-11 04:51:39.551520');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (57, 15, 27, 'M1000102', 'conference', 'Feedback #1.', '2015-11-11 04:52:13.675581');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (58, 15, 27, 'M1000102', 'learn', 'Feedback #1 for Al', '2015-11-11 04:52:35.952889');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (59, 15, 27, 'M1000102', 'apply', 'Albert is the best name!!!', '2015-11-11 04:53:57.436945');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (60, 7, 32, 'M1000102', 'conference', 'hello', '2015-11-12 01:47:11.986809');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (61, 7, 32, 'M1000102', 'apply', 'ffdfdsfs', '2015-11-12 01:48:34.568698');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (62, 15, 1, 'M1000098', 'learn', 'hello albert!', '2015-11-12 05:28:34.497994');
INSERT INTO teacher_feedback (id, student_id, author_id, sparkpoint_id, phase, message, created_time) VALUES (63, 15, 1, 'M1000098', 'conference', 'feeback in conf!', '2015-11-12 05:45:27.491163');

Failing query

-- If the planner decides to do a RESCAN on the foreign table, only the first join
-- will work (only one row will have an author_name set)
   SELECT "teacher_feedback".*,
          ("people"."FirstName" || ' ' || "people"."LastName") AS author_name
     FROM "teacher_feedback"
LEFT JOIN "people" ON "teacher_feedback"."author_id" = "people"."ID"
    WHERE "student_id" = ?
      AND "sparkpoint_id" = ?

Working query

   -- Running the same query against a local table works fine.
   SELECT "teacher_feedback".*,
          ("local_people"."FirstName" || ' ' || "local_people"."LastName") AS author_name
     FROM "teacher_feedback"
LEFT JOIN "local_people" ON "teacher_feedback"."author_id" = "local_people"."ID"
    WHERE "student_id" = ?
      AND "sparkpoint_id" = ?

This should allow you to reproduce the issue, however, it seems that just implementing rescan will solve the problem.

jmealo commented 8 years ago

For reference:

postgresql_fdw

/*
 * postgresReScanForeignScan
 *      Restart the scan.
 */
static void
postgresReScanForeignScan(ForeignScanState *node)
{
    PgFdwScanState *fsstate = (PgFdwScanState *) node->fdw_state;
    char        sql[64];
    PGresult   *res;

    /* If we haven't created the cursor yet, nothing to do. */
    if (!fsstate->cursor_exists)
        return;

    /*
     * If any internal parameters affecting this node have changed, we'd
     * better destroy and recreate the cursor.  Otherwise, rewinding it should
     * be good enough.  If we've only fetched zero or one batch, we needn't
     * even rewind the cursor, just rescan what we have.
     */
    if (node->ss.ps.chgParam != NULL)
    {
        fsstate->cursor_exists = false;
        snprintf(sql, sizeof(sql), "CLOSE c%u",
                 fsstate->cursor_number);
    }
    else if (fsstate->fetch_ct_2 > 1)
    {
        snprintf(sql, sizeof(sql), "MOVE BACKWARD ALL IN c%u",
                 fsstate->cursor_number);
    }
    else
    {
        /* Easy: just rescan what we already have in memory, if anything */
        fsstate->next_tuple = 0;
        return;
    }

    /*
     * We don't use a PG_TRY block here, so be careful not to throw error
     * without releasing the PGresult.
     */
    res = PQexec(fsstate->conn, sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
        pgfdw_report_error(ERROR, res, fsstate->conn, true, sql);
    PQclear(res);

    /* Now force a fresh FETCH. */
    fsstate->tuples = NULL;
    fsstate->num_tuples = 0;
    fsstate->next_tuple = 0;
    fsstate->fetch_ct_2 = 0;
    fsstate->eof_reached = false;
}

oracle_fdw

https://github.com/laurenz/oracle_fdw/blob/master/oracle_fdw.c#L1098

ahsanhadi commented 7 years ago

Hi Jeffery,

We have not been able to work on this issue due to other priorities but will make a point to fix the this issue with joins asap. We also have plan to add to join push down to mysql_fdw in the next release which will make the queries where joins can be pushed down to foreign server run faster.

-- Ahsan

ibrarahmad commented 7 years ago

I am working on implementing the "mysqlReScanForeignScan" which was a TODO. But without that test case is working fine. Do you really expect the bellow results?

postgres=# SELECT "teacher_feedback".*,
          ("people".firstname || ' ' || "people".lastname) AS author_name
     FROM "teacher_feedback"
LEFT JOIN people ON "teacher_feedback"."author_id" = people.ID
    WHERE "student_id" = 15
      AND "sparkpoint_id" = 'M1000103';
 id | student_id | author_id | sparkpoint_id |   phase    |       message       |        created_time        | author_name 
----+------------+-----------+---------------+------------+---------------------+----------------------------+-------------
  1 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:14.085295 | Clarisa Doe
  2 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:15.251326 | Clarisa Doe
  3 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:16.223212 | Clarisa Doe
  4 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:17.104922 | Clarisa Doe
  5 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:32:38.220784 | Clarisa Doe
  6 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:32:39.364092 | Clarisa Doe
  7 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:32:40.219691 | Clarisa Doe
  8 |         15 |         1 | M1000103      | apply      | apply feedback #1   | 2015-11-08 18:33:05.391527 | Chris Doe
  9 |         15 |         1 | M1000103      | apply      | Apply feedbakc #2   | 2015-11-08 18:33:11.513977 | Chris Doe
 10 |         15 |         1 | M1000103      | apply      | Apply feedback #3   | 2015-11-08 18:33:20.064137 | Chris Doe
 11 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 19:36:42.199389 | Clarisa Doe
 34 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:32:36.134558 | Clarisa Doe
 35 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:32:40.474324 | Clarisa Doe
 36 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:18.55869  | Clarisa Doe
 37 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:19.654355 | Clarisa Doe
 38 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:20.722185 | Clarisa Doe
 39 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:22.283593 | Clarisa Doe
 40 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:23.414498 | Clarisa Doe
 41 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 17:42:54.175087 | Clarisa Doe
 42 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 17:43:56.62802  | Clarisa Doe
 43 |         15 |         7 | M1000103      | learn      | Learn feedback      | 2015-11-08 17:45:18.002621 | Clarisa Doe
 44 |         15 |         7 | M1000103      | conference | Conference feedback | 2015-11-08 17:45:18.002621 | Clarisa Doe
(22 rows)

postgres=# SELECT "teacher_feedback".*,
          ("local_people".firstname || ' ' || "local_people".lastname) AS author_name
     FROM "teacher_feedback"
LEFT JOIN local_people ON "teacher_feedback"."author_id" = "local_people".id
    WHERE "student_id" = 15
      AND "sparkpoint_id" = 'M1000103';
 id | student_id | author_id | sparkpoint_id |   phase    |       message       |        created_time        | author_name 
----+------------+-----------+---------------+------------+---------------------+----------------------------+-------------
  1 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:14.085295 | Clarisa Doe
  2 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:15.251326 | Clarisa Doe
  3 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:16.223212 | Clarisa Doe
  4 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:27:17.104922 | Clarisa Doe
  5 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:32:38.220784 | Clarisa Doe
  6 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:32:39.364092 | Clarisa Doe
  7 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 18:32:40.219691 | Clarisa Doe
  8 |         15 |         1 | M1000103      | apply      | apply feedback #1   | 2015-11-08 18:33:05.391527 | Chris Doe
  9 |         15 |         1 | M1000103      | apply      | Apply feedbakc #2   | 2015-11-08 18:33:11.513977 | Chris Doe
 10 |         15 |         1 | M1000103      | apply      | Apply feedback #3   | 2015-11-08 18:33:20.064137 | Chris Doe
 11 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 19:36:42.199389 | Clarisa Doe
 34 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:32:36.134558 | Clarisa Doe
 35 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:32:40.474324 | Clarisa Doe
 36 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:18.55869  | Clarisa Doe
 37 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:19.654355 | Clarisa Doe
 38 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:20.722185 | Clarisa Doe
 39 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:22.283593 | Clarisa Doe
 40 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 22:41:23.414498 | Clarisa Doe
 41 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 17:42:54.175087 | Clarisa Doe
 42 |         15 |         7 | M1000103      | learn      | Hello feedback!     | 2015-11-08 17:43:56.62802  | Clarisa Doe
 43 |         15 |         7 | M1000103      | learn      | Learn feedback      | 2015-11-08 17:45:18.002621 | Clarisa Doe
 44 |         15 |         7 | M1000103      | conference | Conference feedback | 2015-11-08 17:45:18.002621 | Clarisa Doe
(22 rows)
jmealo commented 7 years ago

@ibrarahmad: Thank you for working on this. Previously, only the first JOIN worked when RESCANing a foreign table. The issue encountered was only one row had an author_name set. Since author_name is set for all rows using both the local and foreign table, I believe that you may have corrected the issue reported in this ticket.

Background: I worked through this at length in the #postgresql IRC channel and some maintainers helped me track my issue down to mysqlReScanForeignScan not being implemented. The consensus was that it is not optional. The planner expects it to be implemented. It's not safe to add stubs for functionality that's not actually implemented just to get it to compile against a new version of PostgreSQL.

ibrarahmad commented 7 years ago

I pushed a fix for that, please verify and close the ticket.

jmealo commented 7 years ago

@ibrarahmad: I'm not using mysql_fdw in production anymore, I've been pushing my results into PostgreSQL (laregly due to the issues with join). Your changes look good from an error handling perspective, I'm unsure if unrelated fixes since then have fixed the issue.

I'm not familiar with the codebase, but, what in the provided function is resetting the scan state? It seems dissimilar to the postgresql-fdw example I provided.

If the provided test case passes, feel free to close.

ahsanhadi commented 7 years ago

Hi Jeffery,

The test case passes so we are going to close this case. If you still see other issues with joins or mysql_fdw in general, please post here and we will take a look at it asap.

-- Ahsan