On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/30/2015 08:13 AM, Dane Foster wrote: > >> >> On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto: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> >> <mailto: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> >> <mailto: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 <mailto: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. >> > > You would think that would also cause an issue with the first row that is > returned correctly. My suspicion is with this: > > row_number() OVER (PARTITION BY cid ORDER BY lower(duration)) > > What happens if you run the full SELECT without it? > > > > >> 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 >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > Your first query didn't use it and as discussed rows come back but only the first row has a non NULL location column. Dane
