Port Bug Report: Datetime type comparision error.

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Artur Wroblewski
Your email address : wrobell(at)posexperts(dot)com(dot)pl

Category : runtime: back-end: SQL
Severity : serious

Summary: Datetime type comparision error.

System Configuration
--------------------
Operating System : Linux 2.2.7 ELF, RedHat 6.0

PostgreSQL version : 6.4.2

Compiler used : Look below

Hardware:
---------
uname -a: Linux dk.posexperts.com.pl 2.2.7 #4 SMP Tue May 4 18:24:39 EDT 1999 i586 unknown
Pentium MMX 166MHz, 80MB RAM

Versions of other tools:
------------------------
I don't know at all. I installed precompiled
postgresql from rpm package which has been shipped
with RedHat 6.0. Package version: 6.4.2-3.

--------------------------------------------------------------------------

Problem Description:
--------------------
I am trying to execute SQL query via psql. If the query
contains datetime comparision I am getting error:
ERROR: _finalize_primnode: can't handle node 108

If I remove the comparision the query is executed without
query.

--------------------------------------------------------------------------

Test Case:
----------
Create database bug01.
----------- Database ----------------
CREATE TABLE "logins" (
"id_user" int4 NOT NULL,
"login_number" int4,
"login_time" datetime,
"login_ip" character varying(20),
"logout_time" datetime,
"logout_ip" character varying(20));
COPY "logins" FROM stdin;
8 1 Wed Apr 14 09:30:00 1999 EDT xx.xx.xx.xx Wed Apr 14 18:00:56 1999 EDT xx.xx.xx.xx
5 19 Mon Apr 26 08:34:03 1999 EDT xx.xx.xx.xx Mon Apr 26 15:34:39 1999 EDT xx.xx.xx.xx
12 1 Thu Apr 15 09:21:02 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
100 1 Fri Apr 23 08:30:00 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
8 2 Thu Apr 15 10:01:50 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
100 2 Sat Apr 24 13:57:00 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
5 20 Tue Apr 27 10:08:48 1999 EDT xx.xx.xx.xx Tue Apr 27 17:19:15 1999 EDT xx.xx.xx.xx
100 5 Sun Apr 25 17:04:00 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
5 5 Thu Apr 15 08:34:06 1999 EDT xx.xx.xx.xx Thu Apr 15 12:18:57 1999 EDT xx.xx.xx.xx
1 1 Wed Apr 14 11:43:00 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
5 4 Wed Apr 14 15:56:04 1999 EDT xx.xx.xx.xx Wed Apr 14 16:19:05 1999 EDT xx.xx.xx.xx
5 30 Thu May 06 08:17:35 1999 EDT xx.xx.xx.xx Thu May 06 15:37:01 1999 EDT xx.xx.xx.xx
5 31 Fri May 07 08:17:52 1999 EDT xx.xx.xx.xx Fri May 07 15:44:26 1999 EDT xx.xx.xx.xx
5 6 Fri Apr 16 08:36:56 1999 EDT xx.xx.xx.xx Fri Apr 16 11:29:46 1999 EDT xx.xx.xx.xx
5 32 Fri May 07 15:44:33 1999 EDT xx.xx.xx.xx \N xx.xx.xx.xx
5 7 Fri Apr 16 14:24:51 1999 EDT xx.xx.xx.xx Fri Apr 16 18:42:35 1999 EDT xx.xx.xx.xx
5 1 Wed Apr 14 08:07:00 1999 EDT xx.xx.xx.xx Wed Apr 14 15:28:30 1999 EDT xx.xx.xx.xx
5 2 Wed Apr 14 15:29:16 1999 EDT xx.xx.xx.xx Wed Apr 14 15:49:00 1999 EDT xx.xx.xx.xx
5 3 Wed Apr 14 15:49:49 1999 EDT xx.xx.xx.xx Wed Apr 14 15:55:00 1999 EDT xx.xx.xx.xx
5 8 Sat Apr 17 10:31:57 1999 EDT xx.xx.xx.xx Sat Apr 17 13:26:37 1999 EDT xx.xx.xx.xx
5 9 Sat Apr 17 13:41:24 1999 EDT xx.xx.xx.xx Sat Apr 17 14:51:41 1999 EDT xx.xx.xx.xx
5 22 Wed Apr 28 16:12:03 1999 EDT xx.xx.xx.xx Wed Apr 28 18:01:00 1999 EDT xx.xx.xx.xx
5 10 Sat Apr 17 14:51:54 1999 EDT xx.xx.xx.xx Sat Apr 17 15:41:53 1999 EDT xx.xx.xx.xx
5 21 Wed Apr 28 08:23:25 1999 EDT xx.xx.xx.xx Wed Apr 28 14:52:38 1999 EDT xx.xx.xx.xx
5 11 Mon Apr 19 08:00:06 1999 EDT xx.xx.xx.xx Mon Apr 19 15:09:22 1999 EDT xx.xx.xx.xx
5 23 Thu Apr 29 10:59:38 1999 EDT xx.xx.xx.xx Thu Apr 29 15:12:28 1999 EDT xx.xx.xx.xx
5 12 Mon Apr 19 15:09:45 1999 EDT xx.xx.xx.xx Mon Apr 19 15:24:34 1999 EDT xx.xx.xx.xx
5 13 Mon Apr 19 15:25:08 1999 EDT xx.xx.xx.xx Mon Apr 19 15:29:45 1999 EDT xx.xx.xx.xx
5 14 Mon Apr 19 15:30:55 1999 EDT xx.xx.xx.xx Mon Apr 19 16:06:29 1999 EDT xx.xx.xx.xx
5 24 Fri Apr 30 08:19:00 1999 EDT xx.xx.xx.xx Fri Apr 30 17:34:10 1999 EDT xx.xx.xx.xx
11 1 Fri Apr 16 11:07:19 1999 EDT xx.xx.xx.xx Tue Apr 20 09:47:05 1999 EDT xx.xx.xx.xx
5 25 Tue May 04 08:17:21 1999 EDT xx.xx.xx.xx Tue May 04 18:47:10 1999 EDT xx.xx.xx.xx
5 26 Wed May 05 07:26:36 1999 EDT xx.xx.xx.xx Wed May 05 18:05:41 1999 EDT xx.xx.xx.xx
5 15 Tue Apr 20 08:06:26 1999 EDT xx.xx.xx.xx Tue Apr 20 16:11:00 1999 EDT xx.xx.xx.xx
11 2 Tue Apr 20 09:47:36 1999 EDT xx.xx.xx.xx Wed Apr 21 09:28:12 1999 EDT xx.xx.xx.xx
5 16 Wed Apr 21 08:14:08 1999 EDT xx.xx.xx.xx Wed Apr 21 17:30:00 1999 EDT xx.xx.xx.xx
100 3 Sun Apr 25 08:10:00 1999 EDT xx.xx.xx.xx Sun Apr 25 11:20:00 1999 EDT xx.xx.xx.xx
5 17 Thu Apr 22 08:18:24 1999 EDT xx.xx.xx.xx Thu Apr 22 15:36:27 1999 EDT xx.xx.xx.xx
11 3 Wed Apr 21 09:28:33 1999 EDT xx.xx.xx.xx Thu Apr 22 17:39:44 1999 EDT xx.xx.xx.xx
100 4 Sun Apr 25 12:04:00 1999 EDT xx.xx.xx.xx Sun Apr 25 16:20:00 1999 EDT xx.xx.xx.xx
11 4 Thu Apr 22 17:40:12 1999 EDT xx.xx.xx.xx Fri Apr 23 10:40:24 1999 EDT xx.xx.xx.xx
5 27 Wed May 05 18:05:46 1999 EDT xx.xx.xx.xx Wed May 05 18:36:36 1999 EDT xx.xx.xx.xx
5 18 Fri Apr 23 08:22:37 1999 EDT xx.xx.xx.xx Fri Apr 23 18:07:11 1999 EDT xx.xx.xx.xx
11 5 Fri Apr 23 10:40:50 1999 EDT xx.xx.xx.xx Mon Apr 26 13:02:50 1999 EDT xx.xx.xx.xx
5 28 Wed May 05 18:36:43 1999 EDT xx.xx.xx.xx Wed May 05 18:37:00 1999 EDT xx.xx.xx.xx
5 29 Wed May 05 18:37:11 1999 EDT xx.xx.xx.xx Wed May 05 18:52:26 1999 EDT xx.xx.xx.xx
10 1 Thu May 06 12:53:01 1999 EDT xx.xx.xx.xx Thu May 06 12:56:59 1999 EDT xx.xx.xx.xx
100 100 Mon Apr 26 14:04:00 1999 EDT xx.xx.xx.xx Mon Apr 26 17:04:00 1999 EDT xx.xx.xx.xx
100 100 Mon Apr 26 17:14:00 1999 EDT xx.xx.xx.xx Mon Apr 26 18:04:00 1999 EDT xx.xx.xx.xx
\.
-----------End of DB-----------------

psql bug01 <- connect to database

Exec following queries with \i query_in_file
-------- SQL Queries -------------------
begin;
set datestyle to 'iso';
create view lzview as select id_user, date(login_time),
min(login_time)+'06:00:00' as first, max(login_time) as last from logins louts
where date(login_time) in
(select date(login_time) from logins
where id_user=louts.id_user and logout_time is null)
group by id_user, date(login_time);

select logins.id_user, logins.login_time,
lzview.first as logout_time
from logins, lzview where logins.logout_time is null and
logins.id_user=lzview.id_user and date(logins.login_time)=lzview.date
and lzview.first<lzview.last;
-- and datetime(date(lzview.first), '11:00:00'::time)>lzview.first;
-- and datetime(date(lzview.first), '11:00:00')>=lzview.first;

--select * from lzview;
drop view lzview;
rollback;
----------------------------------------

--------------------------------------------------------------------------

Solution:
---------

--------------------------------------------------------------------------