Issue 126029 - Base query wizard error due to missing quoting of table and column names
Summary: Base query wizard error due to missing quoting of table and column names
Status: RESOLVED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: 4.0.0
Hardware: PC All
: P3 Major (vote)
Target Milestone: 4.2.0
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-12 15:44 UTC by antonio1
Modified: 2015-11-07 18:30 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.2.0-dev
Developer Difficulty: ---


Attachments
1 (172.71 KB, image/jpeg)
2015-01-12 16:58 UTC, antonio1
no flags Details
2 (171.56 KB, image/jpeg)
2015-01-12 16:59 UTC, antonio1
no flags Details
3 (369.02 KB, image/jpeg)
2015-01-12 16:59 UTC, antonio1
no flags Details
2a (122.96 KB, image/jpeg)
2015-01-12 17:06 UTC, antonio1
no flags Details
here the step by step query (122.96 KB, image/jpeg)
2015-01-12 17:12 UTC, antonio1
no flags Details
sql query (53.72 KB, image/jpeg)
2015-01-12 18:35 UTC, antonio1
no flags Details
syntax errors (22.50 KB, image/jpeg)
2015-01-13 13:26 UTC, antonio1
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description antonio1 2015-01-12 15:44:47 UTC
hi.
when i type query in sql mode and run it, i get an error message.
QUERY purpose:select all children, who was born in 2001 year.
[code]
select * from 589 where yy='2001';
[/code]
*I checked the syntax, it works perfect on demo base on tutorial site.
**when i try make query in design view, it return me just unsorted table, with all records from 1997 till 2008.


Thank you
Comment 1 oooforum (fr) 2015-01-12 16:12:13 UTC
Your bug report is being closed as NOT_AN_ISSUE due to a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information:

a) Provide a step-by-step procedure to reproduce (the simpler will be the better)

b) Provide any test case(s) which will help us confirm the problem (like a sample document)

c) Provide screenshots of the problem if you think it might help

Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue.
Comment 2 antonio1 2015-01-12 16:58:35 UTC
Created attachment 84407 [details]
1
Comment 3 antonio1 2015-01-12 16:59:04 UTC
Created attachment 84408 [details]
2
Comment 4 antonio1 2015-01-12 16:59:38 UTC
Created attachment 84409 [details]
3
Comment 5 antonio1 2015-01-12 17:06:02 UTC
Created attachment 84410 [details]
2a
Comment 6 antonio1 2015-01-12 17:12:31 UTC
Created attachment 84411 [details]
here the step by step query

here is making query by query wisard. 
i set condition yy=2001 but in result this condition not settled on 2a picture. on 3 pic. see - result all records are choose, not 2001 only.
Comment 7 antonio1 2015-01-12 18:35:24 UTC
Created attachment 84412 [details]
sql query

direct sql doesn't accepted conditions
Comment 8 antonio1 2015-01-12 20:43:59 UTC
i just find, that this bug was when only one conditions choosed. 
when 2 conditions choosed, the query created. 
is it bug or special feature?
Comment 9 oooforum (fr) 2015-01-13 08:00:35 UTC
Please read more attentively my comment 1.
Screenshots are fine but an ODB is better to reproduce.
Save our time and provide informations required.
Comment 10 antonio1 2015-01-13 09:01:12 UTC
of coarce,sir, i can send odb, but to your private mail, because it has some pers data,i'm in doubt to expose it public.
Comment 11 oooforum (fr) 2015-01-13 10:00:27 UTC
This is a public bug tracking system, so if you attach a file, it does not contain confidential data.
See this tutorial: https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=8289 to know how do it.
Comment 12 antonio1 2015-01-13 13:26:33 UTC
Created attachment 84413 [details]
syntax errors

base even truncated is 1.3 mb(20 records) 
is it bug or normal
Comment 14 damjan 2015-11-07 07:01:08 UTC
Confirming even with latest SVN.

Open the database in comment 13. Queries -> Use Wizard to Create Query, choose table 589, in step 3 "Search conditions" choose "yy" "is equal to" "2001", click "Next" right up until the end. The query is created without that condition.

When a second condition is entered, both conditions work correctly.

The following exception is logged on the console when "Next" is clicked:

Nov 07, 2015 8:26:30 AM com.sun.star.wizards.ui.FilterComponent getFilterConditions
SEVERE: null
com.sun.star.sdbc.SQLException: Syntax error in SQL expression
java stack trace:
	at com.sun.star.bridges.jni_uno.JNI_proxy.dispatch_call(Native Method)
	at com.sun.star.bridges.jni_uno.JNI_proxy.invoke(JNI_proxy.java:171)
	at com.sun.proxy.$Proxy59.setElementaryQuery(Unknown Source)
	at com.sun.star.wizards.ui.FilterComponent.getFilterConditions(FilterComponent.java:256)
	at com.sun.star.wizards.query.QueryWizard.leaveStep(QueryWizard.java:341)
	at com.sun.star.wizards.ui.WizardDialog.switchToStep(WizardDialog.java:359)
	at com.sun.star.wizards.ui.WizardDialog.gotoNextAvailableStep(WizardDialog.java:677)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.sun.star.wizards.ui.event.MethodInvocation.invoke(MethodInvocation.java:90)
	at com.sun.star.wizards.ui.event.AbstractListener.invoke(AbstractListener.java:86)
	at com.sun.star.wizards.ui.event.CommonListener.actionPerformed(CommonListener.java:45)
	at com.sun.star.bridges.jni_uno.JNI_proxy.dispatch_call(Native Method)
	at com.sun.star.bridges.jni_uno.JNI_proxy.invoke(JNI_proxy.java:171)
	at com.sun.proxy.$Proxy57.execute(Unknown Source)
	at com.sun.star.wizards.ui.UnoDialog.executeDialog(UnoDialog.java:730)
	at com.sun.star.wizards.query.QueryWizard.start(QueryWizard.java:127)
	at com.sun.star.wizards.query.CallQueryWizard$QueryWizardImplementation.trigger(CallQueryWizard.java:92)

	at com.sun.star.bridges.jni_uno.JNI_proxy.dispatch_call(Native Method)
	at com.sun.star.bridges.jni_uno.JNI_proxy.invoke(JNI_proxy.java:171)
	at com.sun.proxy.$Proxy59.setElementaryQuery(Unknown Source)
	at com.sun.star.wizards.ui.FilterComponent.getFilterConditions(FilterComponent.java:256)
	at com.sun.star.wizards.query.QueryWizard.leaveStep(QueryWizard.java:341)
	at com.sun.star.wizards.ui.WizardDialog.switchToStep(WizardDialog.java:359)
	at com.sun.star.wizards.ui.WizardDialog.gotoNextAvailableStep(WizardDialog.java:677)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.sun.star.wizards.ui.event.MethodInvocation.invoke(MethodInvocation.java:90)
	at com.sun.star.wizards.ui.event.AbstractListener.invoke(AbstractListener.java:86)
	at com.sun.star.wizards.ui.event.CommonListener.actionPerformed(CommonListener.java:45)
	at com.sun.star.bridges.jni_uno.JNI_proxy.dispatch_call(Native Method)
	at com.sun.star.bridges.jni_uno.JNI_proxy.invoke(JNI_proxy.java:171)
	at com.sun.proxy.$Proxy57.execute(Unknown Source)
	at com.sun.star.wizards.ui.UnoDialog.executeDialog(UnoDialog.java:730)
	at com.sun.star.wizards.query.QueryWizard.start(QueryWizard.java:127)
	at com.sun.star.wizards.query.CallQueryWizard$QueryWizardImplementation.trigger(CallQueryWizard.java:92)
Comment 15 damjan 2015-11-07 18:27:24 UTC
#i126029# Base query wizard error due to missing quoting of table and column names
During the Base query design wizard,
com.sun.star.wizards.ui.FilterComponent.getFilterConditions()
calls
com.sun.star.wizards.db.SQLQueryComposer.getSelectClause()
before its
getFromClause(). However the getSelectClause() needs composedCommandNames
which is populated only by getFromClause() in order to quote table and column names.
Without this, table and column names that need quoting (spaces, special characters,
reserved words) produce errors.
Comment 16 damjan 2015-11-07 18:30:41 UTC
The syntax error is on the user's side: missing space before "and" and table name not quoted.

As for the serious problem here, where table and column name were not getting quoted, leading to an error creating the query, patch was committed in revision 1713164:

#i126029# Base query wizard error due to missing quoting of table and column names
During the Base query design wizard,
com.sun.star.wizards.ui.FilterComponent.getFilterConditions()
calls
com.sun.star.wizards.db.SQLQueryComposer.getSelectClause()
before its
getFromClause(). However the getSelectClause() needs composedCommandNames
which is populated only by getFromClause() in order to quote table and column names.
Without this, table and column names that need quoting (spaces, special characters,
reserved words) produce errors.

Resolving fixed.