Обсуждение: Error in Query Builder: multi-line sql
Using latest 1.15 dev, opensuse 12.1 64 bit. This is a tough one to describe, no backtrace to offer, no segfault or other command line message. 1. Open pgadmin, select correct db, open query builder, all ok. 2. cut and paste from Kwrite editor Python code as follows: select distinct on (mname, nname) mname, nname from grammar \ inner join majors using (mid) \ inner join minorsusing (nid) \ inner join jxgp using (gid) \ where jxgp.pid = 9 3. try to run this, it fails since line continuation is not valid in this context. No problemo. 4. go through and edit out the \ chars, but leave as multi-line query. Try to run, runs ok. 5. attempt to edit line 1, e.g. try to remove distinct clause, all ok until you hit one of the parentheses to activate the paren matching routine, QB freezes silently and no longer responds. Line 1 may remain visible, but the remaining lines are replaced by a light blue rectangle which fills the edit port. (I realize this will be OS dependent, I am just giving what I experience.) 5a. slide horizontal slider to the right and back and text reappears in port. Further editing brings back the blue patch. 6. close QB (window closes correctly and pgadmin seems to be unaware of the error that happened), reopen QB. 7. now paste sql from line 2 above into the port and this time edit out the \ and newlines and put everything on line 1. Edit and runs now proceed normally. 8. break the sql into multiple lines, edits now work ok. I wondered if I am inserting unprinted characters from Kwrite so I tried with CLI editor Joe. Same effect. -- --- Colin Beckingham 613-454-5369 http://www.it4gh.com
On Fri, 2012-03-09 at 05:09 -0500, Colin Beckingham wrote: > [...] > select distinct on (mname, nname) mname, nname from grammar \ > inner join majors using (mid) \ > inner join minors using (nid) \ > inner join jxgp using (gid) \ > where jxgp.pid = 9 > It seems hard to reproduce. Can you send me the tables's definition? (grammar, majors, minors, jxpg) It'll be easier and quicker for me than trying to figure out their definitions. Thanks. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On 03/10/2012 08:38 AM, Guillaume Lelarge wrote: > On Fri, 2012-03-09 at 05:09 -0500, Colin Beckingham wrote: >> [...] >> select distinct on (mname, nname) mname, nname from grammar \ >> inner join majors using (mid) \ >> inner join minors using (nid) \ >> inner join jxgp using (gid) \ >> where jxgp.pid = 9 >> > > It seems hard to reproduce. Can you send me the tables's definition? > (grammar, majors, minors, jxpg) It'll be easier and quicker for me than > trying to figure out their definitions. Thanks. > OK here is some more detail: In order to eliminate the possibility it was the definitions of my tables that are the issue, I tried this: CREATE TABLE one ( col1 integer, col2 text ) WITH ( OIDS=FALSE ); now create table two identical to one above. I think the oids are irrelevant in this case. Neither table contains any data. Use Kwrite (I also tried with text cut and paste from Libreoffice writer) to create text as follows: sql = "select distinct on (one.col2) one.col2 \from one \inner join two \ using (col1)" Now open QB and paste in the text from Kwrite, remove the \ chars and other non-sql in the text and try to run the query, which runs fine, it just does not have any rows, ok. You can probably just paste in the sql, but I wanted to reproduce the Python format. Now we will crash the sql editor: with mouse highlight the opening parenthesis of "(one.col2" and try to delete it with a backspace key. QB editor becomes unusable. I find this is completely consistent on my system. -- --- Colin Beckingham 613-454-5369 http://www.it4gh.com
On 03/10/2012 09:19 AM, Colin Beckingham wrote:
>
>
> On 03/10/2012 08:38 AM, Guillaume Lelarge wrote:
>> On Fri, 2012-03-09 at 05:09 -0500, Colin Beckingham wrote:
>>> [...]
>>> select distinct on (mname, nname) mname, nname from grammar \
>>> inner join majors using (mid) \
>>> inner join minors using (nid) \
>>> inner join jxgp using (gid) \
>>> where jxgp.pid = 9
>>>
>>
>> It seems hard to reproduce. Can you send me the tables's definition?
>> (grammar, majors, minors, jxpg) It'll be easier and quicker for me than
>> trying to figure out their definitions. Thanks.
>>
>
> OK here is some more detail:
> In order to eliminate the possibility it was the definitions of my
> tables that are the issue, I tried this:
>
> CREATE TABLE one
> (
> col1 integer,
> col2 text
> )
> WITH (
> OIDS=FALSE
> );
>
> now create table two identical to one above. I think the oids are
> irrelevant in this case. Neither table contains any data.
>
> Use Kwrite (I also tried with text cut and paste from Libreoffice
> writer) to create text as follows:
>
> sql = "select distinct on (one.col2) one.col2 \
> from one \
> inner join two \
> using (col1)"
>
> Now open QB and paste in the text from Kwrite, remove the \ chars and
> other non-sql in the text and try to run the query, which runs fine, it
> just does not have any rows, ok. You can probably just paste in the sql,
> but I wanted to reproduce the Python format.
>
> Now we will crash the sql editor: with mouse highlight the opening
> parenthesis of "(one.col2" and try to delete it with a backspace key. QB
> editor becomes unusable. I find this is completely consistent on my system.
>
This seems to be related to the number of lines in the text.
With the following arbitrary statements (not related to any real db at
all) the first two, when cut and pasted into the window by themselves,
edit correctly. The second two fail when evaluating pairs of parentheses.
When deleting, say, the '(' from say line 2, line 2 remains visible
while the other lines disappear into the blue cloud.
However, when pasting all the lines from below into the window and
removing a '(', the text remains visible but the window is not usable
until you slide the horizontal bar right and left, which forces a
refresh of the window contents.
==============
select distinct on (junk) from (junk) inner join junk using (junk)
select distinct on (junk) from (junk) inner join junk using (junk) inner join junk using (junk)
select distinct on (junk) from (junk) inner join junk using (junk) inner join junk using (junk)
innerjoin junk using (junk)
select distinct on (junk) from (junk) inner join junk using (junk) inner join junk using (junk)
innerjoin junk using (junk) inner join junk using (junk)
--
---
Colin Beckingham
613-454-5369
http://www.it4gh.com
On Sat, 2012-03-10 at 09:59 -0500, Colin Beckingham wrote:
>
> On 03/10/2012 09:19 AM, Colin Beckingham wrote:
> >
> >
> > On 03/10/2012 08:38 AM, Guillaume Lelarge wrote:
> >> On Fri, 2012-03-09 at 05:09 -0500, Colin Beckingham wrote:
> >>> [...]
> >>> select distinct on (mname, nname) mname, nname from grammar \
> >>> inner join majors using (mid) \
> >>> inner join minors using (nid) \
> >>> inner join jxgp using (gid) \
> >>> where jxgp.pid = 9
> >>>
> >>
> >> It seems hard to reproduce. Can you send me the tables's definition?
> >> (grammar, majors, minors, jxpg) It'll be easier and quicker for me than
> >> trying to figure out their definitions. Thanks.
> >>
> >
> > OK here is some more detail:
> > In order to eliminate the possibility it was the definitions of my
> > tables that are the issue, I tried this:
> >
> > CREATE TABLE one
> > (
> > col1 integer,
> > col2 text
> > )
> > WITH (
> > OIDS=FALSE
> > );
> >
> > now create table two identical to one above. I think the oids are
> > irrelevant in this case. Neither table contains any data.
> >
> > Use Kwrite (I also tried with text cut and paste from Libreoffice
> > writer) to create text as follows:
> >
> > sql = "select distinct on (one.col2) one.col2 \
> > from one \
> > inner join two \
> > using (col1)"
> >
> > Now open QB and paste in the text from Kwrite, remove the \ chars and
> > other non-sql in the text and try to run the query, which runs fine, it
> > just does not have any rows, ok. You can probably just paste in the sql,
> > but I wanted to reproduce the Python format.
> >
> > Now we will crash the sql editor: with mouse highlight the opening
> > parenthesis of "(one.col2" and try to delete it with a backspace key. QB
> > editor becomes unusable. I find this is completely consistent on my system.
> >
>
> This seems to be related to the number of lines in the text.
> With the following arbitrary statements (not related to any real db at
> all) the first two, when cut and pasted into the window by themselves,
> edit correctly. The second two fail when evaluating pairs of parentheses.
>
> When deleting, say, the '(' from say line 2, line 2 remains visible
> while the other lines disappear into the blue cloud.
>
> However, when pasting all the lines from below into the window and
> removing a '(', the text remains visible but the window is not usable
> until you slide the horizontal bar right and left, which forces a
> refresh of the window contents.
>
> ==============
>
> select distinct on (junk)
> from (junk)
> inner join junk using (junk)
>
> select distinct on (junk)
> from (junk)
> inner join junk using (junk)
> inner join junk using (junk)
>
> select distinct on (junk)
> from (junk)
> inner join junk using (junk)
> inner join junk using (junk)
> inner join junk using (junk)
>
> select distinct on (junk)
> from (junk)
> inner join junk using (junk)
> inner join junk using (junk)
> inner join junk using (junk)
> inner join junk using (junk)
>
I don't reproduce your issue at all. The only difference with you is
that I'm using Fedora. Don't see how the OS difference could be an issue
for you.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com