MySQL Vertical Bar Chart Backwards

[Mindframes]

When using the ImageSelectSQL servlet, X axis labels for the bar charts shows up backwards relative to the data.

I.e. when doing a query ("ResourceID, count(*) as Count from courts.tblreservation GROUP BY ResourceID") that has makes a table like this:
+------------+-------+
| ResourceID | Count |
+------------+-------+
| 1 | 4 |
| 2 | 8 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
| 8 | 1 |
+------------+-------+
7 rows in set (0.05 sec)

The horizontal bar chart would look a little like this:

8: ****
7: ********
6: **
5: *
4: **
2: *
1: *

Note that for ResourceID 2, the data should be 8, but it is only 1, and the data point for ResourceID 7 is 8 instead.
The right graph would look like this:
1: ****
2: ********
4: **
5: *
6: **
7: *
8: *

Here's my driver html form, based off the sample one at http://coyote.aad.gov.au/JFreeServlet/example2.html with only the SQL query changed (and the database linked to MySQL instead of Oracle)

<form action="http://homenet2.hcii.cs.cmu.edu:8080/se ... eSelectSQL" method="post">

<table border="0"><tr>
<td valign="top" >Width</td> <td valign="top">
<input type="text" name="width" size="20"
value="800">
</td><td valign="top" >Height</td> <td valign="top"><input type="text" name="height" size="20"
value="600"></td></tr><tr> <td valign="top">1st Colour</td>
<td valign="top" ><select size="1" name="initColor">
<option value="0" selected>White</option>
<option value="1">Black</option>
<option value="2">Blue</option>
<option value="3">Green</option>
<option value="4">Red</option>
<option value="5">Yellow</option>
<option value="6">Gray</option>
<option value="7">Orange</option>
<option value="8">Cyan</option>
<option value="9">Magenta</option>
<option value="10">Pink</option>
<option value="11">Random</option>
</select></td> <td valign="top">2nd Colour</td>
<td valign="top" >
<select size="1" name="finalColor">
<option value="0">White</option>
<option value="1">Black</option>
<option value="2">Blue</option>
<option value="3">Green</option>
<option value="4">Red</option>
<option value="5">Yellow</option>
<option value="6">Gray</option>
<option value="7">Orange</option>
<option value="8">Cyan</option>
<option value="9">Magenta</option>
<option value="10" selected>Pink</option>
<option value="11">Random</option>
</select></td></tr><tr> <td valign="top">Output</td>
<td valign="top" ><select size="1" name="output">
<option selected value="jpeg">JPEG</option>
<option value="gif">GIF</option>
<option value="svg">SVG</option>
</select></td> <td valign="top">Title</td>
<td valign="top" >
<input type="text" name="title" size="20" value="Reservations by Resource"></td></tr><tr> <td valign="top" >SQL</td> <td valign="top" colspan="3" >

<textarea rows="3" name="sql" cols="50">ResourceID, count(*) as Count from courts.tblreservation GROUP BY ResourceID</textarea></td> <tr> <td valign="top" >Chart
Type</td> <td valign="top" colspan="2" >

<select size="1" name="type">
<option selected value="25">XY Chart</option>
<option value="11">Vertical BarChart 3D</option>
<option value="12">Stacked Vertical Bar Chart</option>
<option value="13">Stacked Vertical Bar Chart3D</option>
<option value="14">Horizontal Bar Chart</option>
<option value="15">Stacked Horizontal Bar Chart</option>
<option value="19">Vertical Bar Chart</option>
<option value="9">Pie Chart</option>
</select>

<input type="text" name="explode" size="3" value="1">
<input type="text" name="explodePercent" size="3" value="100">

</td> <td valign="top">
</td> <tr> <td valign="top" >Inc Zero</td> <td valign="top" colspan="2" >

<input type="radio" value="false" checked name="zero">False<br>
<input type="radio" name="zero" value="true">True</td> <td valign="top">
<input type="submit" value="Submit" name="Plot">

</form>


Any ideas, or is this a bug?

[Mindframes]

I forgot to mention that the "XY Chart" works fine on this data, but "Vertical Bar Chart"-types and "Horizontal Bar Chart" types don't work.

[Bryan]

HI

I will setup the data and see if I can replicate on coyote, on monday. To test and see if I get the same results.

Bryan

[Mindframes]

If it's useful, here's the MySQL dump to create that table:


--
-- Table structure for table 'tblreservation'
--

CREATE TABLE tblreservation (
ReservationID int(11) NOT NULL auto_increment,
ResourceID int(11) NOT NULL default '0',
ReservationDate date default NULL,
StartTime time default NULL,
EndTime time default NULL,
ReservationMessage longtext,
EmailReminder tinyint(1) default '0',
SportID int(11) default NULL,
TimeSignedUp timestamp(14) NOT NULL,
PRIMARY KEY (ReservationID),
KEY PRIMARY_KEY (ReservationID),
KEY ReservationID (ReservationID),
KEY ResourceID (ResourceID),
KEY tblResourcetblReservation (ResourceID)
) TYPE=MyISAM;

--
-- Dumping data for table 'tblreservation'
--


INSERT INTO tblreservation VALUES (7,1,'2002-07-18','16:00:00','17:00:00','Hello',1,1,20020718160209);
INSERT INTO tblreservation VALUES (6,1,'2002-06-29','15:00:00','16:00:00','Hello',1,1,20020629123442);
INSERT INTO tblreservation VALUES (8,1,'2002-07-19','11:00:00','12:00:00','Hello',1,1,20020718160215);
INSERT INTO tblreservation VALUES (9,2,'2002-07-20','11:00:00','12:00:00','Hello',1,1,20020718160218);
INSERT INTO tblreservation VALUES (10,2,'2002-07-21','14:00:00','15:00:00','Hello',1,1,20020718160221);
INSERT INTO tblreservation VALUES (11,2,'2002-07-21','15:00:00','16:00:00','Hello',1,1,20020718160224);
INSERT INTO tblreservation VALUES (12,2,'2002-07-21','16:00:00','17:00:00','Hello',1,1,20020718160227);
INSERT INTO tblreservation VALUES (13,2,'2002-07-22','11:00:00','12:00:00','Hello',1,1,20020718160230);
INSERT INTO tblreservation VALUES (14,2,'2002-07-23','14:00:00','15:00:00','Hello',1,1,20020718160233);
INSERT INTO tblreservation VALUES (15,2,'2002-07-24','13:00:00','14:00:00','Hello',1,1,20020718160238);
INSERT INTO tblreservation VALUES (16,2,'2002-07-25','13:00:00','14:00:00','Hello',1,1,20020718160241);
INSERT INTO tblreservation VALUES (17,4,'2002-07-25','11:00:00','12:00:00','Hello',1,1,20020718160345);
INSERT INTO tblreservation VALUES (18,1,'2002-07-25','07:00:00','08:00:00','Hello',1,1,20020718160352);
INSERT INTO tblreservation VALUES (19,4,'2002-07-18','16:00:00','17:00:00','Hello',1,2,20020718160356);
INSERT INTO tblreservation VALUES (20,5,'2002-07-18','16:00:00','17:00:00','Hello',1,2,20020718160359);
INSERT INTO tblreservation VALUES (21,6,'2002-07-18','16:00:00','17:00:00','Hello',1,4,20020718160412);
INSERT INTO tblreservation VALUES (22,7,'2002-07-18','17:00:00','18:00:00','Hello',1,4,20020718160414);
INSERT INTO tblreservation VALUES (23,8,'2002-07-18','18:00:00','19:00:00','Hello',1,4,20020718160417);
INSERT INTO tblreservation VALUES (24,6,'2002-07-18','19:00:00','20:00:00','Hello',1,4,20020718170347);
INSERT INTO tblreservation VALUES (25,1,'2002-07-19','18:00:00','19:00:00','Hello',1,1,20020719142556);
INSERT INTO tblreservation VALUES (26,2,'2002-07-19','19:00:00','20:00:00','Hello',1,1,20020719150130);
INSERT INTO tblreservation VALUES (27,4,'2002-07-19','15:00:00','16:00:00','Hello',1,2,20020719150302);

[Bryan]

HI

Have loaded the data on our server.

In SQL-PLUS
select ResourceID, count(*) as Count from tblreservation GROUP BY ResourceID ;

RESOURCEID COUNT
---------- ----------
1 5
2 9
4 3
5 1
6 2
7 1
8 1

7 rows selected.

Using http://coyote.aad.gov.au/JFreeServlet/example2.html
and entering sql as follows :
ResourceID, count(*) as Count from marine.tblreservation GROUP BY ResourceID

The generated charts appears to be correct?? Can I get you to check.

thanks
Bryan

[Mindframes]

Right, that's exactly what I saw.

Note that when you do an XY Chart and a Vertical Bar Chart, the graph points are the same visually, but the horizontal x-axis is reversed on the Vertical Bar Chart.

[Bryan]

Hi

It strikes me that there are two options
a) MySQL issue with the code (have had one of these before)
b) Code running on coyote is not the same as you are running. I am sure that I have submitted what is on coyote to CVS.

I am thinking that a is more likely but b is easier to discount. Can I send you directly what is running on coyote to check? Hopefully this will discount b)

Bryan

[Mindframes]

I'm a bit confused by your reply.
(b) doesn't seem like a possibility since both of us are getting the exact same output. Both versions exhibit the bug where the x-axis lables are backwards only on the Vertical Bar Chart.

I guess (a) isn't a possibility either since you're using Oracle and not MySQL.

To clarify:

In a XY Plot the X-Axis looks like this:

1 2 3 4 5 6 7 8

In a Vertical Bar Chart the X-Axis looks like this:

8 7 6 5 4 2 1

The graphing is the same but the X-axis has been reversed.

[Mindframes]

I think I found it.

In JdbcCategoryDataset.executeQuery, it uses a HashMap for storing the categories. As they go in, they come out backwards. Why are we using a HashMap when we want an ordered set?

To see this, just add to JdbcCategoryDataset.executeQuery() one System.out:
while (resultSet.next()) {
/// First column is a category name
String categoryName = resultSet.getString(1);
System.out.println("JdbcCategoryDataset.executeQuery: Putting in: "+categoryName);

And change DefaultCategoryDataset.getCategories()
public List getCategories() {

// the CategoryDataset interface expects a list of categories, but we've stored them in
// an array...
if (categories==null) {
return new java.util.ArrayList();
}
else {
System.out.println("DefaultCategoryDataset.getCategories: categories"+categories);
for (int i=0; i<categories.length; i++)
System.out.println("categories["+i+"]="+categories);
return Collections.unmodifiableList(Arrays.asList(categories));
}

}

JdbcCategoryDataset.executeQuery: Putting in: 70
JdbcCategoryDataset.executeQuery: Putting in: 71
JdbcCategoryDataset.executeQuery: Putting in: 72
DefaultCategoryDataset.getCategories: categories
categories[0]=72
categories[1]=71
categories[2]=70

So I just need to make this temporary categoryNames anything but a HashMap. Will this cause problems in other places?

[Mindframes]

Yeah, an ArrayList solves the problem. The bug fix is to change JdbcCategoryDataset.executeQuery()'s HashMap into an ArrayList, only three lines:

...
java.util.ArrayList categoryNames = new java.util.ArrayList(); // NEW
//HashMap categoryNames = new HashMap();
...
while (resultSet.next()) {
/// First column is a category name
String categoryName = resultSet.getString(1);
System.out.println("JdbcCategoryDataset.executeQuery: Putting in: "+categoryName);
//categoryNames.put(categoryName, "");
categoryNames.add(categoryName); // NEW
...

categories = categoryNames.toArray(); // NEW

categoryNames.clear();
rows.removeAllElements();

Should this be submitted to the bug list on SourceForge or will it be taken care of from here?

[Bryan]

Hi

Sorry, about the late reply. Thanks a lot for doing the debugging, I've been enjoying my holidays. So, yes I will take onboard your code changes and submit to DG this week.
I have added a comment, crediting you.

The problem with my last reply was I was only checking the XY chart.

Again thanks alot.


Bryan

[Ricky B]

Are the fixes referred to by Mindframes already in the JCommon-0.6.4 release of the JDBCCategoryDataset?

[Bryan]

Ricky

Yes, this can be checked by looking at the CVS tree

Bryan