Dear Lionel, Thanks for your previous posts. I didn't anwser them precisely, and I'm getting quite confused too, so I shall sum up the discussion about criterion simplification:
On Mon, 21 Jan 2013 10:21:46 +0100
Lionel Elie Mamane <lio...@mamane.lu> wrote:
> > However it fails on some (odd) examples : you cannot express an
> > equation, see attached example. (production libo also fails with
> > this example).
>
> I'm confused. The two queries in the attached example work for me with
> production LibO. What exactly does not work?
>
> --
> Lionel
Here is my production LibO: Version 3.6.2.2 (Build ID: 360m1(Build:2)) on 32
bit lubuntu 12.10, plus french localization.
Here is the experiment in which a problem appears:
* Open numeric.odb (I re-attached the same file)
* Edit query named Equation (inside query view)
* There is a criterion : "<= [number] / 2 + 10 / 2"
* Pretend the criterion is modified (e.g. insert a space and validate)
* The criterion rewrites into "<= / 2 + 10 / 2"
* Run the modified query or swich to SQL view or even try to save the query:
you get some message like "SQL syntax error".
IMO There are 2 distinct questions about removing a name:
Question (A) : is it the right place ? (a place where we may remove a name)
Question (B) : is it the right name ? (alone or qualified ? what about aliases
? etc.)
*** About question (A) : what is a good place to remove a name :
inside OSQLParseNode::impl_parseNodeToString_throw, file
connectivity/source/parse/sqlnode.cxx, there is a test around line 467, in
which we discuss about adding a CONDITION:
- if (rParam.xField.is() && SQL_ISRULE(pSubTree,column_ref))
+ if (CONDITION && rParam.xField.is() && SQL_ISRULE(pSubTree,column_ref))
I first proposed to test if we are at the beginning, so CONDITION <=>
(rString.getLength() == 0). I do not consider my proposal as a *solution* but
as a *workaround*. Clearly, it forgets many simplifications, which yields ugly
criterion text, but it manages at least trivial ones ("3" instead of "[field] =
3", "<= 5", "LIKE '*foo*'", etc.) and it seems quite robust (no need to anwser
question (B), works fine with the mere field name), so it changes a severe bug
into a cosmetic one. Worth implementing while waiting for a real solution ? I
think so, because I'm afraid things are getting harder and harder as time goes
(see below).
You proposed CONDITION <=> (i == m_aChildren.begin()). I interpret (am I right
?) this condition as "is it the left part (1st child) of its parent ?". Your
condition looks smart, but may remove names deeply inside expressions: I built
the "Equation" query specially to get [number] as left child of divide operator
inside "[number] / 2".
I had a look at MS Access (XP), and it seems they simplify only direct left
children of boolean "atoms" (I don't now the exact term so I explain what I
mean): a criterion is a combination of these "atoms" using AND, OR, NOT; each
"atom" is a primitive condition build with operators such as = <> < > <= >=,
[NOT] LIKE, [NOT] IN, [NOT] BETWEEN, IS [NOT] EMPTY (is this list complete ?
are the op names localized ?)
So an enhancement of your CONDITION could be ((i == m_aChildren.begin()) &&
((this) corresponds to an operator of the previous list)). I did'nt try to
implement it. Do you think it is worth trying ?
*** About Question (B) : does a name refer to the right column :
* As you stated in one of your previous posts, we have to take into account
table names (which are possibly table aliases) : for a criterion about field
[name] in table [Table], [Table].[name] may be removed but [OtherTable].[name]
may not.
* We also have to determine whether [name] alone refers to [Table].[name] and
may be removed or not.
* What about a criterion inside computed columns, such as UPPER([name]) LIKE
'*FOO*' ?
I tried some (obfuscated) examples to figure out to which [Table] an
unqualified [name] refers. I had a bad surprise about field aliases (I mean:
*field* aliases, not *table* aliases). Namely : writing "Table"."name" is not a
safe way to access field "name" of table "Table", it can be captured by alias
"name" (see attached example fieldalias.odb, built with production LibO using
only SQL view):
Consider a table named "Table" with (at least) 2 fields: a, b (say: integers).
This simple query ("Simple") works fine:
SELECT "a", "b" FROM "Table" WHERE "a" <= 3
Now rename field "b" into "a" using an alias. Protecting "a" by writing
"Table"."a" has no effect ! and both columns are named "a" (query "Problem"):
SELECT "a", "b" AS "a" FROM "Table" WHERE "Table"."a" <= 3
Here is the only workaround I found; 2nd column gets renamed into "a1" (query
"Workaround"):
SELECT "a", "b" AS "a" FROM (SELECT * FROM "Table" WHERE "Table"."a" <= 3)
Same kind of problem seems to arise when swapping names (SELECT "a" AS "b", "b"
AS "a").
So many new questions arise:
* do you agree with this example, or did I miss something ?
* is there really no way to access the "hidden" field ?
* does this behaviour conform to some norm ? (I don't know SQL dialects well
enough to anwser ... I don't want to consider MS Access as a reference, but as
a comparison Table.a works fine with it)
* is this behaviour common or is it proper to the embedded HSQL database ?
* should we accept aliases capturing field names ? or generate an error ? or
work around ?
Olivier.
numeric.odb
Description: application/vnd.sun.xml.base
fieldalias.odb
Description: application/vnd.sun.xml.base
_______________________________________________ LibreOffice mailing list LibreOffice@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice
