Odd behavior: SQL Result different between Mysql Browser and SQLEXEC 1

[Jay9988]

Hi All,

I have just experienced this odd behavior:

I have a SELECT statement with several subqueries and join

If I tried manually the statement on the Mysql Browser utility, it generates records exactly what I intended (For example: 5 records). But if I run the same SQL statement from the VFP with sqlexec, it generates slightly different (more records have generated, looks like the filtering from subquery I used, do not works)

I have tried on different environment (different Mysql Server, Different Client), the SQLEXEC is fine (generated 5 records as I intended)

Has anyone experienced this kind of situation ? I have tried to re-install MySQL ODBC connector, but doesn't help

Many thanks

 

[Tore Bleken]

My guess is either different settings, or that you are not querying the same tables.

 

[atlopes]

Jay9988,

What is the SQL statement you use in the MySQL Browser, and what is the VFP code you use to prepare and execute the statement?

 

[Jay9988]

Hi atlopes,

Here is the code in VFP

     msql="select a.no_aktiva,' ' no_seri, a.nama,LEFT(a.lokasi,50) lokasi,a.umur, convert(a.harga,decimal(15)) harga,"+;
            "a.tgl_beli,convert(a.metode,decimal(1)) metode,d.expense, d.jual, p.kd_pos,p.pos, ifnull(s.no,0) no,ifnull(s.expbln,0) expbln "+;
            "from asset a join (select no_aktiva,SUM(expense) expense,SUM(jual) jual from dasset "+;
            "where kddivisi=?xxdivisi and no_aktiva in("+masset+") and tanggal<=?thisform.txttgl.value "+;
            "and concat(kddivisi,no_aktiva) not in(select concat(kddivisi,no_aktiva) from djasset dj join jasset j on(dj.no_trans=j.no_trans) "+;
            "where j.tanggal<=?thisform.txttgl.value) group by no_aktiva) d "+;
            "on(a.no_aktiva=d.no_aktiva) "+;
            "left join (select no_aktiva,no,expense expbln from dasset where kddivisi=?xxdivisi and no_aktiva in("+masset+") and tanggal=?thisform.txttgl.value) s "+;
            "on(a.no_aktiva=s.no_aktiva) join pos p ON(a.kd_asset=p.kd_pos)
SQLEXEC(xcon,msql,"crs2")

I used question mark (?) to pass the value from VFP to Mysql
I have used this technique in many statement, it works fine

thanks

 

[atlopes]

Jay9988,

What are the VARTYPE() and values of [tt]xxdivisi[/tt], [tt]masset[/tt], and [tt]thisform.txttgl.value[/tt]?

 

[Jay9988]

HI atlopes

the type of:
xxdivisi is character
masset is memory variables containing list of values such as ('AB','BC'... etc)
thisform.txttgl.value is date type which the user input it in the textbox of the main form

thanks

 

[Tore Bleken]

Updated!
The last line doesn't end with ". And what is "masset"?

However, instead I strongly suggest that you use this solution to construct msql:

Text to msql textmerge noshow pretext 7
  select a.no_aktiva,' ' no_seri, a.nama,LEFT(a.lokasi,50) lokasi,a.umur, convert(a.harga,decimal(15)) harga,
  a.tgl_beli,convert(a.metode,decimal(1)) metode,d.expense, d.jual, p.kd_pos,p.pos, ifnull(s.no,0) no,ifnull(s.expbln,0) expbln 
  from asset a join (select no_aktiva,SUM(expense) expense,SUM(jual) jual from dasset
  where kddivisi=?xxdivisi and no_aktiva in(<<masset>>) and tanggal<=?thisform.txttgl.value
  and concat(kddivisi,no_aktiva) not in(select concat(kddivisi,no_aktiva) from djasset dj join jasset j on(dj.no_trans=j.no_trans)
  where j.tanggal<=?thisform.txttgl.value) group by no_aktiva) d 
    on(a.no_aktiva=d.no_aktiva)
    left join (select no_aktiva,no,expense expbln from dasset where kddivisi=?xxdivisi and no_aktiva in("+masset+") and tanggal=?thisform.txttgl.value) s
    on(a.no_aktiva=s.no_aktiva) join pos p ON(a.kd_asset=p.kd_pos)  
EndText

 

[atlopes]

Jay9988,

This code puts the SQL statement into your clipboard. It's a close approximation to what is sent to the server via ODBC (assuming you don't have apostrophes in the [tt]xxdivisi[/tt] variable). Can you paste it into the MySQL Browser query window and compare it to the statement you're executing in the Browser? Better still, after running the code, can you paste the result here and the statement you're executing in the Browser for comparison?

_CLIPTEXT = "select a.no_aktiva,' ' no_seri, a.nama,LEFT(a.lokasi,50) lokasi,a.umur, convert(a.harga,decimal(15)) harga,"+;
            "a.tgl_beli,convert(a.metode,decimal(1)) metode,d.expense, d.jual, p.kd_pos,p.pos, ifnull(s.no,0) no,ifnull(s.expbln,0) expbln "+;
            "from asset a join (select no_aktiva,SUM(expense) expense,SUM(jual) jual from dasset "+;
            "where kddivisi='" + xxdivisi + "' and no_aktiva in("+masset+") and tanggal<='" + DTOS(thisform.txttgl.value) +"' "+;
            "and concat(kddivisi,no_aktiva) not in(select concat(kddivisi,no_aktiva) from djasset dj join jasset j on(dj.no_trans=j.no_trans) "+;
            "where j.tanggal<='" + DTOS(thisform.txttgl.value) + "') group by no_aktiva) d "+;
            "on(a.no_aktiva=d.no_aktiva) "+;
            "left join (select no_aktiva,no,expense expbln from dasset where kddivisi='" + xxdivisi + "' and no_aktiva in("+masset+") and tanggal='" + DTOS(thisform.txttgl.value)+ "') s "+;
            "on(a.no_aktiva=s.no_aktiva) join pos p ON(a.kd_asset=p.kd_pos)"

 

[Chris Miller]

Well, to get to the bottom of the differences you could also use the MySQL query log, as described here:

https://dev.mysql.com/doc/refman/8.0/en/query-log.html

That's showing how the queries you send in by SQLEXEC actually end up, after the parameters are put in and is simpler to compare with what you do in the MySQL Browser. I'd also recommend to use MySQL Workbench instead of the Browser.


Chriss

 

[Jay9988]

Hi All,

Thank you all for your input.
continuing @atlopes example, I agree, as far as I know, there are 2 ways of passing the variable value from VFP to MySQL Statement:
1. Use Question mark (?) in front of the variable name (which I usually used)
Example: select * from ... where name=?thisform.txtname.value

2. Use The variable values itself
Example: select * from .... where name='"+thisform.txtname.value+"'"

which one is actually the best way?

@Chriss: thank you for the article, I'll see it.

thanks

 

[Tore Bleken]

Solution 1, question mark in front of the variable is the best only solution for so many reason. One is security, another is the automatic casting.

NB! Have a look at my solution for creating the select statement inside a Text-EndText construct, it's way simpler!

 

[atlopes]

Jay9988,

I was not suggesting that you'd change anything in your code. But, just as a side note, you can't use a fully parametrized statement if you need to include a variable list in the statement (that is, the contents of your [tt]masset[/tt] variable).

I was trying to assert the validity of your initial question: "The same statement produces different results using the MySQL Browser and using VFP". As for now, I still don't know if you're using the same statement in both situations. That's why I asked you to paste them both here.

 

[Jay9988]

Hi atlopes

Yes I agree, I have used a lot of question marks (?) for passing the variable value, and works fine.

For easily comparison, I paste again the VFP Code

CODE -->
msql="select a.no_aktiva,' ' no_seri, a.nama,LEFT(a.lokasi,50) lokasi,a.umur, convert(a.harga,decimal(15)) harga,"+;
a.tgl_beli,convert(a.metode,decimal(1)) metode,d.expense, d.jual, p.kd_pos,p.pos, ifnull(s.no,0) no,ifnull(s.expbln,0) expbln "+;
"from asset a join (select no_aktiva,SUM(expense) expense,SUM(jual) jual from dasset "+;
"where kddivisi=?xxdivisi and no_aktiva in("+masset+") and tanggal<=?thisform.txttgl.value "+;
"and concat(kddivisi,no_aktiva) not in(select concat(kddivisi,no_aktiva) from djasset dj join jasset j on(dj.no_trans=j.no_trans) "+;
"where j.tanggal<=?thisform.txttgl.value) group by no_aktiva) d "+;
"on(a.no_aktiva=d.no_aktiva) "+;
"left join (select no_aktiva,no,expense expbln from dasset where kddivisi=?xxdivisi and no_aktiva in("+masset+") and tanggal=?thisform.txttgl.value) s "+;
"on(a.no_aktiva=s.no_aktiva) join pos p ON(a.kd_asset=p.kd_pos)"

SQLEXEC(xcon,msql,"crs2")

And this is the statement I executed on MySQL Browser

CODE -->
select a.no_aktiva,' ' no_seri, a.nama,LEFT(a.lokasi,50) lokasi,a.umur, convert(a.harga,decimal(15)) harga,
a.tgl_beli,convert(a.metode,decimal(1)) metode,d.expense, d.jual, p.kd_pos,p.pos, ifnull(s.no,0) no,ifnull(s.expbln,0) expbln 
from asset a join (select no_aktiva,SUM(expense) expense,SUM(jual) jual from dasset 
where kddivisi='R' and no_aktiva in('11','12') and tanggal<='2023-06-30'
and concat(kddivisi,no_aktiva) not in(select concat(kddivisi,no_aktiva) from djasset dj join jasset j on(dj.no_trans=j.no_trans) where j.tanggal<='2023-06-30') group by no_aktiva) d 
on(a.no_aktiva=d.no_aktiva) 
left join (select no_aktiva,no,expense expbln from dasset where kddivisi='R' and no_aktiva in('11','12') and tanggal='2023-06-30') s 
on(a.no_aktiva=s.no_aktiva) join pos p ON(a.kd_asset=p.kd_pos)

The difference is only on the parameter (xxdivisi, masset, thisform.txttgl.value)

Many thanks

 

[atlopes]

Jay9988,

Ok, now we have one of the statements. Please, in VFP, run the code I posted above. It will put the SQL statement into the clipboard. Then paste the statement here for comparison.

 

[Chris Miller]

Jay,

which one is actually the best way? (parameter or variable or control values as in ..."+thisform.txtname.value+"

The parameters with ? are to be preferred. For several reasons, the most important one defending against SQL injection. If you don't know about that it's worth researching. Other reasons are, that you then don't have the service of the ODBC driver to translate VFP data types to MYSQL types, when it comes to dates, for example.

As atlopes already said you can't do the part about [tt]and no_aktiva in('11','12')[/tt] with parameter syntax [tt]and no_aktiva in(?masset)[/tt]. Because no matter how you prepre masset, it will never be a list of values, it will always arrive as a single value using the parameter syntax. So for that part it's fine you do use [tt]no_aktiva in("+masset+")[/tt]. But while everything looks good from the perspective of the VFP code, that could still cause the difference leading to different results in the end. And that can only be seen after you ran the VFP code to put together the query and then copy that here.

We can't see from the VFP code how that query turns out to be, as we don't have your variable values nor your form control values, masset is unknown to us, for example. Do as atlopes suggested or as I suggested, to be able to get at the actual query that's executed.

I strongly suggest you follow my advice to turn on the general_log of MySQL you would have a log file of everything that executes. Also connections/authentication, settings being done by the connection or by MySQL Browser, etc., etc.

The general_log will slow down MySQL, so it shouldn't be done in production, but it's a great help for the analysis of problems. It will even show you how the parts with parameters arrive in MySQL server and what it precisely executes. If you take atlopes you still only have [tt]tanggal<=?thisform.txttgl.value[/tt] in the query and we still don't know what thisform.txttgl.value is or was.




Chriss

 

[Chris Miller]

I tried the general_log myself and it's not easily set up. The simplest solution I ended up with is changing the settings file my.ini file that configures MySQL and stop and restart the server.

There will be a line in my.ini or mysql.cnf starting a section [mysqld]. That will surely have many lines you all keep, but add these (highlighted) lines:

[mysqld]
[highlight #FCE94F]general_log=on
general_log_file=C:\whatever\general.log[/highlight]

[highlight #FCE94F]UPDATE:[/highlight] You can also see what is set by

SQLExec(xcon,"SHOW variables like '%general_log%';")

and set it on and set the file with

SQLExec(xcon,"SET global general_log=1;")
SQLExec(xcon,"SET global general_log_file='...filename...';")

Which means you don't need access to the config file.

If you use MySQL hosted somewhere it will still be easiest to ask the hoster to make that setting at least temporary for your tests. Also, the file name then is likely a Linux file name, another reason you will only have limited success in specifying this if not knowing what part of the file system is available for you on the server and also accessible by mysqld.

Otherwise, of course, make the file name whatever suits you. Then whatever you do is recorded and you can look at the end of that log file after you did the query with the Browser or Workbench and with VFP. You'lll easily detect what was VFP. I show you a section of my log that resulted from SQLEXEC(h,'Select * from test where id=?n'):

....                11 Prepare	select * from test where id=?
		    11 Query	set @@sql_select_limit=DEFAULT
		    11 Execute	select * from test where id='1.00000000000000000e+00'
		    11 Reset stmt	
....                11 Close stmt	
		    11 Quit

I set n=1 in VFP and you see that this results in string syntax in MySQL, which surprised me, but works. That's not meaning you have to write numbers in quotes when you don't use parameters. What's important is that you'll find lines with "Execute" in the log that show what SQL actually was executed. And that is what you can compare to what you find when running from the MySQL Browser or WorkBench.

Chriss

PS: running without parameter directly SQLEXEC(h,'Select * from test where id=1'), the log has this:

...                  8 Query	set @@sql_select_limit=DEFAULT
		     8 Query	select * from test where id=1
...                  8 Quit

Then there is no Preparation phase and no Execute, so look for "Query" to find the queries logged. Well, just look at the end of the general.log file, you'll find what was executed and can compare it.

 

[Jay9988]

Hi all

Thanks altopes's.
I have run your suggested command (I paste here from _CLIPTEXT)

select a.no_aktiva,' ' no_seri, a.nama,LEFT(a.lokasi,50) lokasi,a.umur, convert(a.harga,decimal(15)) harga,
a.tgl_beli,convert(a.metode,decimal(1)) metode,d.expense, d.jual, p.kd_pos,p.pos, 
ifnull(s.no,0) no,ifnull(s.expbln,0) expbln from asset a join 
(select no_aktiva,SUM(expense) expense,SUM(jual) jual from dasset where kddivisi='R ' 
and no_aktiva in('000101 ','000098 ') and tanggal<='2023-06-30'
and concat(kddivisi,no_aktiva) not in(select concat(kddivisi,no_aktiva) from djasset dj 
join jasset j on(dj.no_trans=j.no_trans) where j.tanggal<='2023-06-30') group by no_aktiva) d 
on(a.no_aktiva=d.no_aktiva) left join (select no_aktiva,no,expense expbln from dasset where kddivisi='R ' 
and no_aktiva in('000101 ','000098 ') and tanggal='2023-06-30') s 
on(a.no_aktiva=s.no_aktiva) join pos p ON(a.kd_asset=p.kd_pos)

With some trial again in MySQL Browse I just knew also, that the concat function is removing spaces, and I think maybe this is the real problem.
I have filtering clause using "concat(kddivisi,no_aktiva)" the result of concat is "R000098" rather than "R 000098"), although the vartype of kddivisi is char(2)
Combining this filter with another clause (where kddivisi='R ') makes the result different

@chriss: thank you a lot for sharing your experiment with general log. It's really broaden my knowledge. I am sure it will be very useful to trace odd query result in the future

 

[Chris Miller]

Sorry, Jay,

but I know at the stage of creating the SQL string VFP does not substitute thisform.txttgl.value into the string that includes ?thisform.txttgl.value, VFP will forward the ? expression and the value of thisform.txttgl.value seperately to the backend.

Your explanation of the difference by concat makes no sense, sorry. Look close at your own post, the concat expression is in there, it will run in MySQL and so that will run no matter if that SQL was coming from the Browser or VFP and thus will have the same effect. What could make a difference is the spaces you have in('000101 ','000098 ').

It seems to me you aren't able to reproduce the SQL you did in MySQL Browser, as atlopes and I would like to compare the two versions of the SQL and you don't have that anymore. From one version alone you can't see a difference.

Chriss

 

[Jay9988]

Hi Chriss,

Yes, I am sorry, my explanation is not clear enough
The difference between the statement results from VFP (from _CLIPTEXT using atlopes suggested command) and the statement I wrote manually is only in the clause ( ... where kddivisi='R '). The result of _CLIPTEXT, the parameter value 'R ' has one blank space behind, following the initial Value of textbox is char(2), but in the statement I wrote manually in Mysql Browxer, I just write 'R' (no space), and surprisingly the result is a bit difference

Related to my cases, and to simplify the statement to the parts that have strange behavior, I am doing this little experience in Mysql Browser

Experiment 1: I wrote simple statement

select * from dasset where kddivisi='R '

Whether I put space behind R or using 'R' (no space) the result is the same, all record with kddivisi R are shown, as I expected

Experiment 2: I wrote the part of my original code (especially the part with consist of concat function)

select * from dasset where kddivisi='R' 
and concat(kddivisi,no_aktiva) in(select concat(kddivisi,no_aktiva) from djasset dj
join jasset j on(dj.no_trans=j.no_trans) where j.tanggal<='2023-06-30')

The result is records that matched between table dasset and djasset and jasset (let say 2 records)
Strangely in this experiment 2, if I put space behind R ('R ') none records has shown
Notes: all kddivisi data in all table is actually R,and vartype is char(2)

I couldn't know the reason for this behavior...

 

[Chris Miller]

Of course, a space after the R can make a difference.
Within VFP itself you can make a difference of SQL results with SET ANSI ON/OFF. That's not influencing how MySQL compares strings, but showcases there are several modes to do string commparison and get different matching. It would always be a problem in SQL without varchar, if you would compare exactly, i.e 'R '='R' would be considered false, so SQL comparison usually isn't exact comparison, otherwise you'd always need to pad the value in your where clauses to the column width. Still if you compare 'R1'='R ' that's false and 'R '='R ' should be true. If you want everything starting with R the straight forward way to do in SQL is column LIKE 'R%' instead, that works the same no matter how equality of strings is defined in the SQL engine.

Chriss