Strange characters after using join with parenthesis

[kgizmo]

I'm using ZeosLib (rev 806) with Delphi 2010 and PostgreSQL 8.4.3. I've noticed strange behaviour during using joins in queries. Fields of "text" type shows strange characters when I use parenthesis with join in query.

Example:

Code: Select all

select SR.*,STA.opis as status_opis, R.nazwa as rodzaj,P.data_od,
PR.nazwisko||coalesce(' '||PR.imiona,'') as pracownik,
POM.nazwa as pomieszczenie
from ((st.srodki_trwale SR left join st.przypisania P on SR.id=P.id_srodka_trwalego)
         left join admin.pracownicy PR on P.id_pracownika=PR.id)
        left join admin.pomieszczenia POM on SR.id_pomieszczenia=POM.id,
   st.statusy STA,st.rodzaje_st R
where SR.status=STA.kod and SR.id_rodzaju=R.id

The result for SR.opis ('text' field) for first record is C㐀㤀㄀⼀㘀㤀　⼀㈀　　㌀.

But the same query without parenthesis:

Code: Select all

select SR.*,STA.opis as status_opis, R.nazwa as rodzaj,P.data_od,
PR.nazwisko||coalesce(' '||PR.imiona,'') as pracownik,
POM.nazwa as pomieszczenie
from st.srodki_trwale SR left join st.przypisania P on SR.id=P.id_srodka_trwalego
         left join admin.pracownicy PR on P.id_pracownika=PR.id
        left join admin.pomieszczenia POM on SR.id_pomieszczenia=POM.id,
   st.statusy STA,st.rodzaje_st R
where SR.status=STA.kod and SR.id_rodzaju=R.id

works fine and shows :
CELERON 1,7GHz
RAM - 256MB
HDD - SAMSUNG SP4002H 40GB
CDROM - ASUS CD-S520/A
for the same record and field.

In PGAdmin both versions of queries work just fine.

I've attached simple project (with sql script).

[mdaems]

Hi,

I can explain you where zeoslib sees a difference between these queries. For the version without parenthesis zeoslib can retrieve the field type from the database metadata as it recognises the tables in the from clause. In case you use paranthesis this becomes too difficult for the parser. Feel free to have a look at the zeoslib sql parser code in ZGenericSqlAnalyser.pas (More specifically TZGenericStatementAnalyser.FillTableRefs). If you find a smart way to ignore the paranthesis, you're welcome!

I think this makes zeoslib choose for an other field type for the SR.opis field. And in case of D2010 I'm not sure what field type that would be. Then it's not impossible unicode string buffers are read as if it were Ansistrings with strange characters as the result.

I'm not a D2010 user, so if you want to solve this issue you'll have to look for the the exact point of failure yourself.
Starting with finding out if there's a different field type choosen may already be a good start.

Mark

[kgizmo]

Hi,

For now I've noticed difference beetwen field types for "SR.opis":
- without parenthesis: TWideMemoField
- with parenthesis: TWideStringField with Size=0

I'm still looking for some solution. Is it good idea to ignore only parenthesis with "select" clause (or all FSectionNames)?

Thanks,
Kamil

[mdaems]

Well, I'm still wondering if it's a good idea to drop parenthesis from the FROM part of the query, because there you can have subselects as well.

So, to solve this problem I'm afraid we need very smart code in FillTableRefs to analyse the code within parenthesis and only extract tablerefs when there's only tables in it which are JOINED together. Maybe something like recursively trying to decode the part within the parenthesis?

Or is there a better default field type instead of TWideStringField in D2009+? What if you make opis a varchar(100) field instead of Text? Would the default field type TWideStringField work correct there?

Mark