Thread: 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