Port Bug Report: Datetime type comparision error. - Mailing list pgsql-ports

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


Your name               : Artur Wroblewski
Your email address      : wrobell@posexperts.com.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:
---------


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