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 <lionel@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.
Attachment:
numeric.odb
Description: application/vnd.sun.xml.base
Attachment:
fieldalias.odb
Description: application/vnd.sun.xml.base