Issue 69243

Summary: SQL direct not usable for QiQ query definition OOD680_m1
Product: Base Reporter: drewjensen.inbox
Component: codeAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: frank.schoenheit, issues
Version: OOo 2.0.3   
Target Milestone: ---   
Hardware: All   
OS: Windows XP   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
database with query none

Description drewjensen.inbox 2006-09-04 17:00:51 UTC
With the new QiQ feature it is not possible to turn escape processing off if the
query definiton uses another query definition. 

See the attached database.

Open query2 in edit mode. Turn the dsigner window off, and the RUN SQL direct
button on. 

The query generates an error from the database engine, as the query name passed
as a table name is meanlingless to the engine.

A suggestion, it would be a solution and I think helpful to users if when the
SQL direct toolbutton is selected if the tex of the query defintion would
reflect the actual select statment, after the replacement of query names with
table names, be displayed. This change would be useful also for advanced users
that may be experiencing unexpected results or errors when applying one of these
QiQ defintions.
Comment 1 drewjensen.inbox 2006-09-04 17:05:33 UTC
Created attachment 38943 [details]
database with query
Comment 2 Frank Schönheit 2006-09-05 07:43:46 UTC
I don't grasp your suggestions, sorry :-\
When executing such a query, the "More" button in the error message should lead
you to a "Information" entry telling you the statement which was executed. Not
sure if this goes into the direction you mean.

Other than this, the behavior is, well, by design. So allowing QiQ for native
queries would be an ENHANCEMENT. Chaning the error messages to better help the
users would be easier, but I'm not sure about a good way to do this.
Comment 3 drewjensen.inbox 2006-09-05 12:43:03 UTC
"So allowing QiQ for native queries would be an ENHANCEMENT."

That is not my point at all. What I am saying is that when the user selects 'Run
SQl Direct' that the QiQ query definition be converted into the actual 'native'
SQL statment that would have been sent to the engine if the 'Run SQL Direct'
button where not engaged.

So, in my example of Query1 :

SELECT "abc".*, "level 1".* FROM "level 1", "abc" WHERE ( "level 1"."ID" =
"abc"."ID" )

would become

SELECT "Level 1".*, "ABC"* FROM "abc", ( SELECT * FROM "abc" ) AS "Level 1"
WHERE ( "level 1"."ID" = "abc"."ID" )

There is already code to handle the transformation, it would just be a matter of
runnig the query definition through this section and replacing the text of the
query definiton with the out put.

I realize that this would most likely need to be a one way trip so perhaps the
user should be prompted before doing it. Or perhaps it could offer to do this
'into' a new query definition.

There are two cases where it seems to me this would be a valuable feature.
1] The user wants to add some construct or funciton available with a specific
database engine but not recognized by the OOo query parser, to an existing QiQ
query definition. 

2] Troubleshooting. For an example looking at Issue #69227, if one replaces the
QiQ query definition Level 3, with the actual SQL statment that would be created
by simple substitution and then pass this directly to the engine you get a
completely different error then the one reported. [ Column already exists in
Statement.... ] This error message actually gives someone enough information to
let them fix the problem. 

Comment 4 Frank Schönheit 2006-09-05 13:56:29 UTC
thanks for the explanation, now I get you ...

Though, not sure about this. "Run SQL Direct" means: "don't touch what I did".
Replacing sub queries is not the most consistent thing then.
On the other hand, some help for the user to resolve the problem would probably
be a Good Thing (TM). Personally, I'd probably prefer a more explicit option -
"Substitute sub queries" or so - over a silent analysis/magic "Hi, here's your
personal Base pet! It seems you're trying to ... Should I ...?" - you know, this
kind of annoying "assistant" :)
Comment 5 drewjensen.inbox 2006-09-05 17:01:18 UTC
"I'd probably prefer a more explicit option"

Thinking further about using the 'SQL Direct' toolbutton here, and reading your
responses, perhaps this is not be a good idea because: it makes one control do
two completely different functions, depending on context. At the moment this
effects the 'mode' or more precisely, if I understand what is happening, it is
just the GUI manifestation for the 'Escape Processing' property on the result set. 

Well, there are two 'views' available now from the menu and toolbar. Designer
view and SQL View. OOo already uses context sensitive menus and toolbars
extensively, so introducing another should not throw the users off. What about a
new menu and toolbutton that would change the 'view' again. Not sure about a
name, SQL Native or SQL Raw or SQL Direct. I don't know.

Basically it would be promoting, if you will, the function of the 'SQL Direct'
toolbutton to managing a 'view' of the query definition. A menu item could be
added then to the 'View' menu also. This new 'view' being mutually exclusive to
the others.

Taking this type of approach then it also negates the possibility of creating a
QiQ definition, then selecting 'SQL direct' mode, as is possible now, which can
only result in an error.

Finally, I wonder if this then would merit a new task item in the Base query
window, 'Create query in SQL direct view...'?

Comment 6 Frank Schönheit 2006-09-05 20:00:25 UTC
To confirm I understand your properly:

We would have 3 views then
- "Design Mode"
- "SQL Mode"
- "SQL Direct Mode"
where the latter would supersede the current "Run SQL Direct" setting.

The third mode would be disabled (or raise an error when selected) if the query
is based on another query.
The third mode would show the substituted statement (i.e. the statement with
queries replaced with their constituting statements).

If the latter, what would happen if the user would change the statement in the
"SQL Direct Mode"? There would hardly be a way back to the other modes then.

Also, wouldn't this be confusing for novice users, which are confronted with an
"SQL Mode" and an "SQL Direct Mode" which seem to be the same (as long as they
didn't add a query)? (Well, at least more confusing than today's "Run SQL
Direct" button.)

Doesn't sound too convincing to me, but perhaps I just didn't yet get the
complete picture.
Comment 7 drewjensen.inbox 2006-09-06 00:14:26 UTC
Well, when I read your re-statement of the idea. ( and yes I think you got what
I meant ) The first word that comes to my mind is, muddled.

Let me just put it this way then:

There should be some way for the user to see the actual SQL statement that would
be issued to the engine, for QiQ query definitions.

If this "way", what ever that is, also allowed for this statement to be copied
and pasted into a new query definition so that case 1 above could be handled,
all the better. Case 2 - troubleshooting is however the more important, IMO.

Comment 8 christoph.lukasiak 2006-09-11 15:29:18 UTC
sounds heavily like a feature enhancement than like a bug -> so i send this
issues as a 'feature anhancement' to the 'requirements' team - correct me if i
am wrong