Base: fix incorrect field removal in criterion inside query design view

[Olivier Ploton]

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 at 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.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: numeric.odb
Type: application/vnd.sun.xml.base
Size: 4059 bytes
Desc: not available
URL: <http://lists.freedesktop.org/archives/libreoffice/attachments/20130123/7b03681e/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: fieldalias.odb
Type: application/vnd.sun.xml.base
Size: 3533 bytes
Desc: not available
URL: <http://lists.freedesktop.org/archives/libreoffice/attachments/20130123/7b03681e/attachment-0001.bin>