Re: mysql_fdw trouble - Mailing list pgsql-general
| From | Adrian Klaver |
|---|---|
| Subject | Re: mysql_fdw trouble |
| Date | |
| Msg-id | 5633A2A3.7060605@aklaver.com Whole thread Raw |
| In response to | Re: mysql_fdw trouble (Dane Foster <studdugie@gmail.com>) |
| Responses |
Re: mysql_fdw trouble
|
| List | pgsql-general |
On 10/30/2015 09:55 AM, Dane Foster wrote: > On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com
pgsql-general by date: