On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/30/2015 09:55 AM, Dane Foster wrote: > >> On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto: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> >> <mailto: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>> >> <mailto: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>>> >> <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 >> <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>>> >> <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 >> <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>> >> <mailto: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> >> <mailto: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 <mailto:adrian.kla...@aklaver.com> >> >> >> It returns all the locations. >> > > You know where we are going: > > SELECT label, starts, ('[' || starts || ', ' || (starts + INTERVAL '4 > HOUR') || ']')::TSTZRANGE AS duration FROM _locations, _series WHERE > loc=location; > > > > >> Dane >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > Converting my query to its JOIN equivalent did indeed do the trick. O the joys of relational algebra and calculus. Well played Sir (or Madam) ! And thanks. Dane P.S. Now if only you can fix the trim/btrim problem then I'll be happy ... until I find something new to complain about.
