Unexpected Data

[audiopro]

I have a table which is a combination of two sales tables created within two retail outlets. Tot number of records at present stands at just over 96000 records. The date fields of the original tables are not in a format useable in MySQL so I insert an epoch value into the DATED field based on the date field of the original tables.
First Example.

Range 1/1/2000 - 31/1/2006
SELECT * FROM `sales` where DATED>=946771200 and DATED<1138752000 
total - 69215

Second Example

Range 1/1/1999 - 31/1/2006
SELECT * FROM `sales` where DATED>=915235200 and DATED<1138752000 
total - 4294967295

I should mention that the data within the table ranges from 20/9/1998 - 25/3/2006 so bothe quries fall within the data range.
Why do I get a very large row count from the second query?

Keith

http://www.studiosoft.co.uk

 

[TonyGroves]

That value is 2^32-1, so it's probably an error code. Where are you getting the value? If you do SELECT COUNT(*) instead, does the result look right?

 

[audiopro]

I thought I was getting to grips with MySQL but I am beginning to wonder.
This just gets more and more confusing.
Assuming I am using the correct syntax, I have tried a few combinations but what is happning now?
Same range yet different results.

SELECT COUNT(*) FROM sales where DATED>=1136160000 and DATED<1136246400
total - 1

SELECT code FROM sales where DATED>=1136160000 and DATED<1136246400 LIMIT 1, 100
total - 61

Keith

http://www.studiosoft.co.uk

 

[TonyGroves]

Where is your "total" coming from? There would only be one record in the result set from the first query, but the count you're looking for would be stored as the value of the 'COUNT(*)' field in that record.

 

[r937]

what is the datatype of DATED?

r937.com | rudy.ca

 

[audiopro]

Extract from the calling PERL program.

$get_sales_range_sth->execute();
my $TopTotal=$get_sales_range_sth->rows;
print "total - $TopTotal<br>";

DATED data type is BIGINT(20) with an index although I have tested it without the index and get thesame result.

Keith

http://www.studiosoft.co.uk

 

[r937]

try this query (which will return only one row), and show us the three numbers it produces --

SELECT COUNT(*) as total_rows
     , sum(case when DATED >=  915235200 
                 and DATED  < 1138752000
                then 1 else 0 end ) as count_1
     , sum(case when DATED >= 1136160000 
                 and DATED  < 1136246400
                then 1 else 0 end ) as count_2
  FROM sales

r937.com | rudy.ca

 

[audiopro]

Sounds like this has got you intrigued too.

my $get_sales_range_sql="SELECT COUNT(*) as total_rows, sum(case when DATED >= 915235200 and DATED < 1138752000 then 1 else 0 end ) as count_1, sum(case when DATED >= 1136160000 and DATED < 1136246400then 1 else 0 end ) as count_2 FROM sales";
print "$get_sales_range_sql<br>";
my $get_sales_range_sth=$dbh->prepare($get_sales_range_sql);
$get_sales_range_sth->execute();
my @get_res=$get_sales_range_sth->fetchrow_array();
print "$get_res[0] - $get_res[1] - $get_res[2]<br>";

Produces query

SELECT COUNT(*) as total_rows, sum(case when DATED >= 915235200 and DATED < 1138752000 then 1 else 0 end ) as count_1, sum(case when DATED >= 1136160000 and DATED < 1136246400then 1 else 0 end ) as count_2 FROM sales

But produces no output.
Unless I have messed up the syntax.
I get a problem with all attepts at select count(*)

Keith

http://www.studiosoft.co.uk

 

[TonyGroves]

It wouldn't produce output because it contains a syntax error - ("1136246400then"). This is not apparent because you seem to be just using Perl to test your queries. You would be better off using a generic MySQL interface (such as the bundled command-line client, or a GUI program) to test your queries, then when you have them working, put them into your Perl code.

Also, it would be better in the long run if you were to use standard DATE fields; it would make queries much simpler. If the source table uses an incompatible format, it should be possible to convert the format during the data-loading process.

 

[audiopro]

Thanks, I didn't spot the error but correcting it made no difference. It appears that the server has what the ISP describes as a 'minor problem'. I have been using this script for a number of months without a hitch but since I made a few recent changes I am experiencing some erratic behaviour.
I am hoping it is a coincidence but I will not find out until tomorrow.
I tried the query on the ISP's own PHPAdmin script and got the same strange results with no error message, so I can only guess what is going on.
With regard to using date fields, I am procesing form input within the PERL script and as I already have the conversion functions written it is simple to pass the epoch values from the function straight into the Query as I already do in a number of different scripts. I like this method as I am only dealing with positive integers. This usually works a treat with a minimum of coding and only has the erratic behaviour in this particular script.

Keith

http://www.studiosoft.co.uk

 

[audiopro]

The queries now work as expected - it was all caused by an error on a server.
Sorry to have wasted your time.

Keith

http://www.studiosoft.co.uk