MySQL negative double returns 0

Hello,

Since Xojo 2022R1 (same thing with R11) I get very strange result on negative numbers from MySQL Database. See below example

var db as MySQLCommunityServer = new MySQLCommunityServer
db.userName=“root”
db.Password=“xxxx”
db.databaseName=“testDB”
db.host = “127.0.0.1”
db.Connect
db.ExecuteSQL “DROP TABLE IF EXISTS test”
db.ExecuteSQL “CREATE TABLE test (id int, myValue double)”
db.ExecuteSQL “INSERT INTO test (id,myValue) VALUES (1,-5)”
db.ExecuteSQL “INSERT INTO test (id,myValue) VALUES (2,4)”
var data as RowSet = db.SelectSQL( “SELECT myValue FROM test”)
If data <> Nil and data.RowCount>0 Then
For Each row As DatabaseRow In data
var a as double = row.column(“myValue”).DoubleValue
var b as string = row.column(“myValue”).stringValue
var c as double = val(row.column(“myValue”).stringValue)
var d as string = str(val(b))
var e as Double=-50
msgbox str(a)+" “+b +” “+str(c)+” “+d+” "+str(e)
next
end if

Row 1 returns 0 −5 0 0 -50
Row 2 correctly returns 4 4 4 4 -50

How is this possible?

Running the same project with Xojo 2021R31 correctly gives me -5 -5 -5 -5 -50

What is the type of the myValue column?

I think is double

Did you copy this verbatim? Is that really “-5” or “–5”?

If it’s the latter with the hyphen rather than the minus character and the column is text, then your results make sense because it’s just another character.

Hello,

Datatype of myValue is double (Value confirmed to DOUBLE and -5 in sql database)

I did copy the result. I didn’t notice it’s the hypen character rather than minus character…
Could that be the reason why val(–5) return 0?
Then the big question is why database return –5 and not -5

If I change to

For i As Integer = 0 To data.RowCount-1
var a as double = data.column(“myValue”).DoubleValue
data.MoveToNextRow
next

correct value is stored in a (minuscharacter 5)

Value is correct in RowSet but incorrect in DatabaseRow just happened recently with a different data type. Perhaps related.

We recently found a bug in such iterable construction, that ONE was said fixed in 2022r1.1
Check if your problem persists with that version.

The workaround was avoiding the for each for databases.

Yes, the problem is the same in 2022r1.1
A workaround is to take the value from Rowset and not use DatabaseRow…

1 Like

Please report your new findings in the Feedback app. Report the number back here for people follow it up and add comments.

1 Like

Well it certainly would be the reason that Xojo’s Val statement gets it wrong. Any string that starts with a non-numeric or +/- will evaluate to zero.

Thank you all for your help.

<https://xojo.com/issue/68592>