On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/30/2015 07:21 AM, Dane Foster wrote: > >> >> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 10/29/2015 05:38 PM, Dane Foster wrote: >> >> Hello, >> >> I think I've tripped over another mysq_fdw bug. I've filed a bug >> report >> on github already but just in case the problem is w/ my query I >> figured >> I would post it here in case someone sees something obvious. >> >> The error message I get is: null value in column "location" >> violates >> not-null constraint. >> >> The DDL is here: >> https://github.com/EnterpriseDB/mysql_fdw/issues/71 >> >> For the record I know top posting is a crime against god and >> humanity >> but I feel justified because this post is not directly related >> to the >> original. So there! Granted it's in the same milieu; and yes this >> current sentence exists for the sole purpose of me being able to >> use the >> word milieu because the opportunity to use it is so few and far >> between. >> >> INSERT INTO series (cid, day, title, description, location, >> duration, >> can_join) >> SELECT >> cid, >> row_number() OVER (PARTITION BY cid ORDER BY >> lower(duration)), >> title, >> description, >> location, >> duration, >> can_join >> FROM ( >> SELECT >> cid, >> title, >> description, >> can_join::BOOLEAN, >> (SELECT label FROM _locations WHERE loc=location) AS >> location, >> ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || >> ']')::TSZ_PERIOD AS duration >> FROM >> _series >> ) AS v >> >> Regards, >> >> >> So what do you get when you do?: >> >> SELECT >> cid, >> title, >> description, >> can_join::BOOLEAN, >> (SELECT label FROM _locations WHERE loc=location) AS location, >> ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || >> ']')::TSTZRANGE AS duration >> FROM >> _series >> ); >> >> >> Dane >> >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> I get rows of data, location and all. >> > > And when you do?: > > SELECT > cid, > row_number() OVER (PARTITION BY cid ORDER BY lower(duration)), > title, > description, > location, > duration, > can_join > FROM ( > SELECT > cid, > title, > description, > can_join::BOOLEAN, > (SELECT label FROM _locations WHERE loc=location) AS location, > ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || > ']')::TSTZRANGE AS duration > FROM > _series > ) AS v > > > >> Dane >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > Before I answer your second query question I need to revise my response to the first. Yes the first query runs w/o an error message but the bit about "rows and all" was not entirely correct. Out of 313 rows only the first row had a location. The other 312 rows have NULL in the location column which is not supposed to happen. To verify this I changed the table names and removed the PostgreSQL transformations (i.e., use of || and :: for casting) and ran the query against the MySQL database; it returned 313 rows of data, location and all. Now that I've cleared that up. Your second query also runs w/o any error messages but like the first only the first row has a non NULL value in the location column. Dane
