Postgresql conditional queries don't work

[markus]

Hi,

I will start with test case:

Code: Select all

create table test_tab{
  date_field date,
  int_field integer,
  real_field numeric(18,6)
WITH (
  OIDS=FALSE
);

fill data in any way - it doesn't matter too much

now in application execute query like this:

Code: Select all

select case when coalesce(:date_grp,false) = true then date_field else null end as date_field, sum(int_field) as int_field, sum(real_field) as real_field
from test_tab
group by case when coalesce(:date_grp,false) = true then date_field else null end

where :date_grp is boolean parameter.

When i execute it i get error:

2014-09-22 23:38:01 CEST ERROR: column "test_tab.date_field" must appear in the GROUP BY clause or be used in an aggregate function at character 49
2014-09-22 23:38:01 CEST STATEMENT: select case when coalesce($1,false) = true then date_field else null end as date_field, sum(int_field) as int_field, sum(real_field) as real_field from test_tab group by case when coalesce($2,false) = true then date_field else null end ORDER BY int_field asc LIMIT 100 OFFSET 0

but what is more interesting, queries like:

Code: Select all

select *
  from test_tab tt
  where case when :int1 is not null then int_field > :int1 else true end
  and case when :int2 is not null then real_field < :int2 else true end

executes ok

I've located that this error occured first in revision 2432, when #define ZEOS_TEST_ONLY was introduced.

Best regards,
Marek

[EgonHugeist]

Lorbs,

hmpf looks like the PreparedStmt stuff to me again.

Marek i allready did add a TZDataSet.Properties parameter: 'EMULATE_PREPARES=Boolean';
Purpose is to allways use the emulation we had before. Because of a low-level client lib i didn't know before. This should also make youe "Lazy" queries running again.

So could you test this too?

[markus]

Michael,

i've checked with param You suggested - and it works:)
Now i got a question: is there a way that i can determine if query will work with CAPI prepared statements - so i can turn on 'EMULATE_PREPARES only for them? I mean without executing query and catching exception
I don't fully understand difference between CAPI and emulated statements, but i assume that CAPI was introduced for benefit of us all
That's why i would like to keep CAPI for most of my queries, and turn on emulated_prepares only for the ones that need that.

Regards,
Marek

[EgonHugeist]

A good point, Marek.

Well, i v'e allready been trying to prevent such cases as much as possible. See TZPostgreSQLPreparedStatement.GetCompareFirstKeywordStrings in ZDbcPostgresSqlStatement.pas (MySQL + PG only! all other providers can handle ALL queries ).

Annoying.. Marek could you please prepare a simple testcase with a SQL-Script(create all required objects, populate some testdata), which i can add to our Test-Suites? Best practice would be, to catch all your know issues in one Test-Case (in Pascal, PLEASE )

Than i'll try to find something. In your current report i can't see where we fail -> Call to prepare the stmt or on executing the prepared stmt.
If the prepare fails with some spezial error-codes i can process the issue you're talking about in a different way as i allready did it for indeterminable datatypes before.

To be clear: A valid testcase -> and i'll do my best. Noting to work with ... snooze Sorry work overload

[markus]

Michael,

I don't have testcase for you yet, but...

I've played a little more with my report query of

Code: Select all

select case when coalesce(:date_grp,false) = true then date_field else null end as date_field, sum(int_field) as int_field, sum(real_field) as real_field
from test_tab
group by case when coalesce(:date_grp,false) = true then date_field else null end

such query goes by two stages:

Code: Select all

select case when coalesce(?,false) = true then date_field else null end as date_field, sum(int_field) as int_field, sum(real_field) as real_field
from test_tab
group by case when coalesce(?,false) = true then date_field else null end

and finally (in ZDbcPostgresqlStatement.pas:TZPostgreSQLPreparedStatement.Prepare)

Code: Select all

PREPARE PLAN_NAME(boolean,boolean) as
select case when coalesce($1,false) = true then date_field else null end as date_field, sum(int_field) as int_field, sum(real_field) as real_field
from test_tab
group by case when coalesce($2,false) = true then date_field else null end

such query could be prepared and executed properly if in prepare statement would be only one parameter:

Code: Select all

PREPARE PLAN_NAME(boolean) as
select case when coalesce($1,false) = true then date_field else null end as date_field, sum(int_field) as int_field, sum(real_field) as real_field
from test_tab
group by case when coalesce($1,false) = true then date_field else null end

and such version would be also logically correct wits SQL passed to ZQuery component (first code section)

but params are inserted based on 2'nd step where they are in form of ?.

So my question is, is this 2nd step needed (not only in pgsql, but in all dbms supported), or it only exists because it's long time ZEOS code, and nobody wants to mess with it?

Regards,
Marek

[EgonHugeist]

Marek,

don't feel ignored. I'm out of time. All i can say: all-time code! This may change on 7.3 (:

[markus]

Michael,

i don't feel ignored - don't worry:)
I assumed that you don't have time for my everlasting list of requests and questions. I also assumed that since i didn't prepared a testcase in Pascal - you'll not attend my issue.

Best regards,
Marek