On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/30/2015 09:36 AM, Dane Foster wrote: > >> On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto: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> >> <mailto: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>> >> <mailto: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>> >> <mailto: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> >> <mailto: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 <mailto: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. >> > > Forgot about that. Where I was going with this is that duration comes from: > > ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || ']')::TSTZRANGE > AS duration > > and MySQL and Postgres have different ideas about timestamps. While I > thinking about what that meant in the context of the query I realized I was > stepping over the obvious: > > SELECT label FROM _locations WHERE loc=location > > So what does the below show: > > SELECT label FROM _locations, _series WHERE loc=location; > > > >> Dane >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > It returns all the locations. Dane
