why does my sql table treat 1000 as a middle number?

Question

When I try to sort by a value descending my SQL table does it correctly, but if it sees for example "1000" it always puts it in the middle? for Example:

this even happens when I reference it in spigot (I'm using it for a plugin) it outputs it the same way

this is how I'm calling it in my plugin:

PreparedStatement statement = database.getConnection().prepareStatement("SELECT uuid FROM player_stats ORDER BY blocks_broken DESC");
                        ResultSet rs = statement.executeQuery();
                        while (rs.next()) {
                            String name = rs.getString("uuid");
                            LeaderboardCommand.name = name;
                            String player = String.valueOf(Bukkit.getPlayer(UUID.fromString(name)));
                            p.sendMessage(player);

I know it's not perfect as I'm just learning/experimenting with databases currently, but I'm mainly asking for help on why the SQL is outputted this way & advice on any severe mistakes I'm making is greatly appreciated! Thanks in advance -Occy

public void createPlayerStats(PlayerStats playerStats) throws SQLException {

    PreparedStatement statement = getConnection()
            .prepareStatement("INSERT INTO player_stats(uuid, blocks_broken, last_login, last_logout) VALUES (?, ?, ?, ?)");
    statement.setString(1, playerStats.getPlayerUUID());
    statement.setLong(2, playerStats.getBlocksBroken());
    statement.setDate(3, new Date(playerStats.getLastLogin().getTime()));
    statement.setDate(4, new Date(playerStats.getLastLogout().getTime()));

    statement.executeUpdate();

    statement.close();

I think you might be sorting strings rather than numbers. Check your code. (Or I guess in this case check the type of your db field) — markspace, Jul 05 '22 at 15:50
What is the type of `blocks_broken`? I'm guessing it's VARCHAR2? — David Conrad, Jul 05 '22 at 15:51
`long` isn't a type used by databases. Please include the `CREATE TABLE` statement in your question. — David Conrad, Jul 05 '22 at 15:54
Can you dump the definitions of that table and copy the text into the question? I think you might be missing something. — markspace, Jul 05 '22 at 15:54
And specify the name of the database engine and its version. — Basil Bourque, Jul 05 '22 at 15:54
@DavidConrad when I create it in the code it's a long, but in the database it says the type is "mediumtext" is that the issue? — , Jul 05 '22 at 15:55
Almost certainly. What api are you using to create this table? The translation seems broken. — markspace, Jul 05 '22 at 15:56
Yes, "mediumtext" is the issue. The behavior you are observing is lexicographic sorting. What you want is numeric sorting. — Kaan, Jul 05 '22 at 15:56
In the future, put more effort into drafting your Question. Think about all the details we might need, and omit the details we don’t need. — Basil Bourque, Jul 05 '22 at 15:56
@BasilBourque I'm sorry ''m new to stackoverflow & database related stuff — , Jul 05 '22 at 15:58
Now you have added the INSERT, but you still are not showing how PLAYER_STATS gets created. There must be some code somewhere to create the table. — David Conrad, Jul 05 '22 at 16:05
@DavidConrad `public void initializeDatabase() throws SQLException { Statement statement = getConnection().createStatement(); //Create the player_stats table String sql = "CREATE TABLE IF NOT EXISTS player_stats (uuid varchar(36) primary key, blocks_broken long, last_login DATE, last_logout DATE)"; statement.execute(sql); statement.close();` — , Jul 05 '22 at 16:10
@123909182441598 According to the [MariaDB documentation](https://mariadb.com/kb/en/data-types/), "LONG and LONG VARCHAR are synonyms for MEDIUMTEXT." Solution: read the documentation and choose an appropriate data type for the `blocks_broken` column. — David Conrad, Jul 05 '22 at 16:16
@DavidConrad should I use INT to initialize it then? What type do you suggest I use to refer to "blocks_broken" with? — , Jul 05 '22 at 16:17
The [MariaDB BIGINT](https://mariadb.com/kb/en/bigint/) type has the same range as the Java `long` type. — David Conrad, Jul 05 '22 at 16:17
@DavidConrad In some parts of my code I have to actually reference long (and BIGINT isn't a java term). Do I only have to change how the table is created so the database can use the correct type? Or do I have to change every time I use "long" — , Jul 05 '22 at 16:21

Davide Lorenzo MARINO · Accepted Answer · 2022-07-05T16:04:29.203

1

It happens because block_broken type is a varchar and not a number.

In this case you are ordering lexycographically and not numerically.

You can change your query to handle that as a numeric value with an explicit cast so your query should be:

 SELECT uuid FROM player_stats ORDER BY cast(blocks_broken as numeric) DESC

Update: In MariaDb try to use this (You can try directly in the db client and once it is working update your java code):

 SELECT uuid FROM player_stats ORDER BY CAST(blocks_broken AS INTEGER) DESC

edited Jul 05 '22 at 16:04

answered Jul 05 '22 at 15:55

Davide Lorenzo MARINO

26,420
4
39
56

I get this error "[11:58:28 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'numeric) DESC' at line 1" – Jul 05 '22 at 15:59
2

It would be better to fix the database table so that arbitrary strings would not be permitted as blocks_broken. – David Conrad Jul 05 '22 at 15:59
@DavidConrad how would I do that? – Jul 05 '22 at 16:00
@123909182441598 How do you create the database table? – David Conrad Jul 05 '22 at 16:01
@123909182441598 try to use cast(blocks_broken as INTEGER) instead of numeric – Davide Lorenzo MARINO Jul 05 '22 at 16:02
@DavidConrad edited into original post – Jul 05 '22 at 16:03

why does my sql table treat 1000 as a middle number?

1 Answers1