db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Simon Rousseau (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (TORQUE-202) Postgres Timestamp not interpreted the same way on two different environnements with the same local settings
Date Wed, 09 May 2012 18:45:49 GMT

     [ https://issues.apache.org/jira/browse/TORQUE-202?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Simon Rousseau updated TORQUE-202:
----------------------------------

    Description: 
** I don't know if I'm writing at the appropriate place... so correct me if I doesn't...

The problem is in the way Torque post the query to PostgresDB with timestamp comparaison onto
different environnement with apparently the same local settings...

We have a production environnement(1 master server with 1 virtual server) and a developpement
environnement(1 master server with 1 virtual server).
Both use the same local settings for the master and the virtual server (I've got the administrator
confimation, but maybe he've forget something).
Both virtual servers (Dev and Prod) are identicals (The dev server is a Prod nightly backup
restore)

1- Original environnement query result


Prod                                                              
 
  Master Prod                                                   

   Virtual Server(Prod)=Incorrect query result   


Developpement

  Maste Dev

    Virtual Server(Dev) = Correct query result



2- Here the query result if we copy the Virtual Server(Dev) onto the Master Prod

Prod                                                              

  Master Prod                                                   

    Virtual Server(Prod)=Incorrect query result     
    Virtual Server(Dev)=Incorrect query result


Developpement

  Maste Dev

    Virtual Server(Dev)=Correct query result



3- Here the result if we copy the Virtual Server(Prod) onto the Master Dev

Prod                                                              

  Master Prod                                                   

   Virtual Server(Prod)=Incorrect query result  
                                                                     

Developpement

  Maste Dev

    Virtual Server(Dev)=Correct query result
    Virtual Server(Prod)=Correct query result


Here is the actual query generated by torque createQueryString fonction (Same in Prod and
Dev environnement with remote debuging confirmation) :

(On the Prod environnement, this query return 1 row when none was suppose to be returned.
On Dev, all is working like it was suppose to ...)

SELECT 
FROM   Ag_Event_Date,
             Ag_Agenda_Event
WHERE  Ag_Event_Date.START_DATE = {ts '2012-05-10 00:00:00.0'}
       AND ( 
			( 
				( 
					( 
						( 
							Ag_Event_Date.START_TIME >= {ts '1970-01-01 17:00:00.0'}
							AND
							Ag_Event_Date.START_TIME < {ts '1970-01-01 18:00:00.0'} 
						)
						OR 
						( 
							Ag_Event_Date.END_TIME > {ts '1970-01-01 17:00:00.0'}
							AND
							Ag_Event_Date.END_TIME < {ts '1970-01-01 18:00:00.0'} 
						) 
					)
					OR 
					( 
						Ag_Event_Date.START_TIME < {ts '1970-01-01 17:00:00.0'}
						AND
						Ag_Event_Date.END_TIME > {ts '1970-01-01 18:00:00.0'} 
					) 
				)
                OR 
				Ag_Event_Date.END_TIME = {ts '1970-01-01 18:00:00.0'} 
			)
            OR 
			Ag_Event_Date.START_TIME IS NULL 
		   )
           AND Ag_Agenda_Event.AGENDA_ID = 84160
           AND Ag_Agenda_Event.EVENT_ID = Ag_Event_Date.EVENT_ID


So I'm suspecting the way {ts '1970-01-01 18:00:00.0'}  is interpreted...

Could you give me some advice on the {ts 'date'} statement ? 
As {ts 'date'} is not a postgres function, where do I need to look to find out how this work?
Do you know a master environnement setting that could affect the date interpretation ?

Or maybe it's a bug ?

...

  was:
** I don't know if I'm writing at the appropriate place... so correct me if I doesn't...

The problem is in the way Torque post the query to PostgresDB with timestamp comparaison onto
different environnement with apparently the same local settings...

We have a production environnement(1 master server with 1 virtual server) and a developpement
environnement(1 master server with 1 virtual server).
Both use the same local settings for the master and the virtual server (I've got the administrator
confimation, but maybe he've forget something).
Both virtual servers (Dev and Prod) are identicals (The dev server is a Prod nightly backup
restore)

1- Original environnement query result

Prod                                                              Developpement

Master Prod                                                   Maste Dev

   Virtual Server(Prod)=Incorrect query result          Virtual Server(Dev) = Correct query
result



2- Here the query result if we copy the Virtual Server(Dev) onto the Master Prod

Prod                                                              Developpement

Master Prod                                                   Maste Dev

   Virtual Server(Prod)=Incorrect query result     Virtual Server(Dev)=Correct query result
   Virtual Server(Dev)=Incorrect query result

3- Here the result if we copy the Virtual Server(Prod) onto the Master Dev

Prod                                                              Developpement

Master Prod                                                   Maste Dev

   Virtual Server(Prod)=Incorrect query result     Virtual Server(Dev)=Correct query result
                                                                     Virtual Server(Prod)=Correct
query result

Here is the actual query generated by torque createQueryString fonction (Same in Prod and
Dev environnement with remote debuging confirmation) :

(On the Prod environnement, this query return 1 row when none was suppose to be returned.
On Dev, all is working like it was suppose to ...)

SELECT 
FROM   Ag_Event_Date,
             Ag_Agenda_Event
WHERE  Ag_Event_Date.START_DATE = {ts '2012-05-10 00:00:00.0'}
       AND ( 
			( 
				( 
					( 
						( 
							Ag_Event_Date.START_TIME >= {ts '1970-01-01 17:00:00.0'}
							AND
							Ag_Event_Date.START_TIME < {ts '1970-01-01 18:00:00.0'} 
						)
						OR 
						( 
							Ag_Event_Date.END_TIME > {ts '1970-01-01 17:00:00.0'}
							AND
							Ag_Event_Date.END_TIME < {ts '1970-01-01 18:00:00.0'} 
						) 
					)
					OR 
					( 
						Ag_Event_Date.START_TIME < {ts '1970-01-01 17:00:00.0'}
						AND
						Ag_Event_Date.END_TIME > {ts '1970-01-01 18:00:00.0'} 
					) 
				)
                OR 
				Ag_Event_Date.END_TIME = {ts '1970-01-01 18:00:00.0'} 
			)
            OR 
			Ag_Event_Date.START_TIME IS NULL 
		   )
           AND Ag_Agenda_Event.AGENDA_ID = 84160
           AND Ag_Agenda_Event.EVENT_ID = Ag_Event_Date.EVENT_ID


So I'm suspecting the way {ts '1970-01-01 18:00:00.0'}  is interpreted...

Could you give me some advice on the {ts 'date'} statement ? 
As {ts 'date'} is not a postgres function, where do I need to look to find out how this work?
Do you know a master environnement setting that could affect the date interpretation ?

Or maybe it's a bug ?

...

    
> Postgres Timestamp not interpreted the same way on two different environnements with
the same local settings
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: TORQUE-202
>                 URL: https://issues.apache.org/jira/browse/TORQUE-202
>             Project: Torque
>          Issue Type: Bug
>          Components: Runtime
>    Affects Versions: 3.1
>         Environment: Centos 5 (Virtual server)
> Tomcat 6 also tested with Tomcat 5
> Java 1.5.x alse tested with Java 1.6.x
> PostgresSQL 8.1
>            Reporter: Simon Rousseau
>              Labels: newbie
>
> ** I don't know if I'm writing at the appropriate place... so correct me if I doesn't...
> The problem is in the way Torque post the query to PostgresDB with timestamp comparaison
onto different environnement with apparently the same local settings...
> We have a production environnement(1 master server with 1 virtual server) and a developpement
environnement(1 master server with 1 virtual server).
> Both use the same local settings for the master and the virtual server (I've got the
administrator confimation, but maybe he've forget something).
> Both virtual servers (Dev and Prod) are identicals (The dev server is a Prod nightly
backup restore)
> 1- Original environnement query result
> Prod                                                              
>  
>   Master Prod                                                   
>    Virtual Server(Prod)=Incorrect query result   
> Developpement
>   Maste Dev
>     Virtual Server(Dev) = Correct query result
> 2- Here the query result if we copy the Virtual Server(Dev) onto the Master Prod
> Prod                                                              
>   Master Prod                                                   
>     Virtual Server(Prod)=Incorrect query result     
>     Virtual Server(Dev)=Incorrect query result
> Developpement
>   Maste Dev
>     Virtual Server(Dev)=Correct query result
> 3- Here the result if we copy the Virtual Server(Prod) onto the Master Dev
> Prod                                                              
>   Master Prod                                                   
>    Virtual Server(Prod)=Incorrect query result  
>                                                                      
> Developpement
>   Maste Dev
>     Virtual Server(Dev)=Correct query result
>     Virtual Server(Prod)=Correct query result
> Here is the actual query generated by torque createQueryString fonction (Same in Prod
and Dev environnement with remote debuging confirmation) :
> (On the Prod environnement, this query return 1 row when none was suppose to be returned.
On Dev, all is working like it was suppose to ...)
> SELECT 
> FROM   Ag_Event_Date,
>              Ag_Agenda_Event
> WHERE  Ag_Event_Date.START_DATE = {ts '2012-05-10 00:00:00.0'}
>        AND ( 
> 			( 
> 				( 
> 					( 
> 						( 
> 							Ag_Event_Date.START_TIME >= {ts '1970-01-01 17:00:00.0'}
> 							AND
> 							Ag_Event_Date.START_TIME < {ts '1970-01-01 18:00:00.0'} 
> 						)
> 						OR 
> 						( 
> 							Ag_Event_Date.END_TIME > {ts '1970-01-01 17:00:00.0'}
> 							AND
> 							Ag_Event_Date.END_TIME < {ts '1970-01-01 18:00:00.0'} 
> 						) 
> 					)
> 					OR 
> 					( 
> 						Ag_Event_Date.START_TIME < {ts '1970-01-01 17:00:00.0'}
> 						AND
> 						Ag_Event_Date.END_TIME > {ts '1970-01-01 18:00:00.0'} 
> 					) 
> 				)
>                 OR 
> 				Ag_Event_Date.END_TIME = {ts '1970-01-01 18:00:00.0'} 
> 			)
>             OR 
> 			Ag_Event_Date.START_TIME IS NULL 
> 		   )
>            AND Ag_Agenda_Event.AGENDA_ID = 84160
>            AND Ag_Agenda_Event.EVENT_ID = Ag_Event_Date.EVENT_ID
> So I'm suspecting the way {ts '1970-01-01 18:00:00.0'}  is interpreted...
> Could you give me some advice on the {ts 'date'} statement ? 
> As {ts 'date'} is not a postgres function, where do I need to look to find out how this
work?
> Do you know a master environnement setting that could affect the date interpretation
?
> Or maybe it's a bug ?
> ...

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message