-
Notifications
You must be signed in to change notification settings - Fork 31
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Invoke-SqlQuery returns only records that have a unique key #146
Comments
@kwein123 I couldn't easily reproduce via SQLite. Can you provide me a sql script that I can use to generate the table with the table so I can try and reproduce in my MySQL instance? I would also try running the queries and storing the results in a variable and then exploring the variable. You can also replace the |
With -Stream, I get all 77 records every time. I don't understand the documentation: "If the -Stream switch is used, only the first result set is returned and the output is a PSObject for each row in the result set." |
@kwein123 I think I need an example script so I can load and do some deeper testing on my side. |
I don't understand the problem but can confirm, with |
@OilyEscuelademarco or @kwein123 -- can one of you provide a sample code that reproduces this issue? This will need to include the schema of the data in the database and enough data to produce the issue. I've tried multiple times but never been able to reproduce. Thanks. |
So sorry it's taken so long to get back to you on this.
then this for my PS code:
Which should return a count of 4 each time, correct?
I'm using PowerShell 7.4.2 |
@kwein123 -- so I tried to reproduce and I'm getting 4 each time. Can you also provide me the SQL to create the table? FYI -- here is the full script I've used previously to try and reproduce -- can you try running this in your MySql database and see the results? # Create the table
Invoke-SqlUpdate -Query "CREATE TABLE testForSimplySql (First text, Second int)"
#Add 6 rows
Invoke-SqlUpdate -Query "INSERT INTO testForSimplySql (First, Second) VALUES ('alpha', 1)"
Invoke-SqlUpdate -Query "INSERT INTO testForSimplySql (First, Second) VALUES ('beta', 2)"
Invoke-SqlUpdate -Query "INSERT INTO testForSimplySql (First, Second) VALUES ('delta', 3)"
Invoke-SqlUpdate -Query "INSERT INTO testForSimplySql (First, Second) VALUES ('gamma', 4)"
Invoke-SqlUpdate -Query "INSERT INTO testForSimplySql (First, Second) VALUES ('alpha', 5)"
Invoke-SqlUpdate -Query "INSERT INTO testForSimplySql (First, Second) VALUES ('epsilon', 6)"
#Verify the data, should be 6 rows
Invoke-SqlQuery -Query "SELECT * FROM testForSimplySql"
#Query just for the column with duplicate data
Invoke-SqlQuery -Query "SELECT First FROM testForSimplySql"
Invoke-SqlQuery -Query "SELECT First FROM testForSimplySql" | measure # should return 6 for count
@(Invoke-SqlQuery -Query "SELECT First FROM testForSimplySql").count # should return 6
Invoke-SqlQuery -Query "SELECT DISTINCT First FROM testForSimplySql" | measure # should return 5 for count This is really confusing... because there is no reason for Something else you might try, installing a version of SimplySql prior to version 2.0 # you might need to use the -Force switch...
Install-Module -Name SimplySql -RequiredVersion 1.9.1 Then import that version and try again. If the issue reproduces with both versions, then this suggests that this is somehow environmental. You can also try Windows PowerShell 5.1 Just trying to think of different permutations that might allow us to hone in on what is causing this behavior. |
Hmmm. I did include the MySQL to create the table, but I'm not very good with markdown. I'll try again here:
I agree, this is bizarre behavior. I'm so glad that @OilyEscuelademarco had the same problem, or I'd be doubting my sanity! ;-) In your example above, your table doesn't have a primary key. I wonder if that's a driver of this problem. I'll try some more permutations. |
Interesting: PowerShell 5.1.22621.2506 returns all "4s". |
Your code (with no primary key) runs the same with both PS versions, giving the results you expected. |
@kwein123 -- hrm this suggest that there might be something going on with your environment. a couple of suggestions. Trying running the code from a different computer using PS 7.4.2 and see if you get the correct or incorrect results. When you launch PS7, you might want to launch it with the |
I tried running with -noprofile - no improvement. So I installed 1.9.1 in my PS 7 environment. If I require that version, the code works. I have never played with this before - is there a way for me to install multiple 1.X or 2.X SimplySQL versions in parallel and test those? Or I need to install/uninstall, and iterate through versions? |
@kwein123 - you can install multiple versions side by side -- the Can you also try create a new mysql database and trying the logic against there? I'm very curious why my instance of MySql (granted, I'm using the MariaDB engine) can't reproduce the issue. So I'm wondering if I'm not reproducing the conditions the same in my testing (despite running the same/similar powershell commands). If you can put together a complete script (so assume that there is no database at all, just a MySql Server.. then in the script provide all the needed commands to create the database, create the table(s) and load data into the table(s) and query to get the incorrect results) -- then I can continue testing and try to isolate what is happening. |
@kwein123 -- In case, you are interested -- here is how to walk through the code to see what is happening (and why I'm completely baffled by your experience).
|
Does that code work for you to create the environment needed to replicate the problem? It works for me. That is, if I require the 1.9.X version of SimplySQL, I get 3 "4s" (correct). If I require 2.0.2.70, I don't. |
@kwein123 - good news, i've been able to reproduce your experience using your sql . however my original attempt to reproduce still isn't giving an issue.. going to keep digging. |
OK -- if you create your table without the primary key, you will get back the proper count. -- Table with Primary Key
CREATE TABLE test1 ( SERVER VARCHAR(25), SHARE VARCHAR(25), DirectoryName VARCHAR(25), FileName VARCHAR(25), Comments TEXT, PRIMARY KEY (SERVER, SHARE, DirectoryName, FileName) );
-- Will return only 3 rows for 'Invoke-SqlQuery "SELECT Server FROM test1"'
-- Table with Primary Key
CREATE TABLE test2 ( SERVER VARCHAR(25), SHARE VARCHAR(25), DirectoryName VARCHAR(25), FileName VARCHAR(25), Comments TEXT);
-- Will return 4 rows for 'Invoke-SqlQuery "SELECT Server FROM test2"' So this seems to be something that the MySqlConnector may be doing -- I'll continue to dig into. |
I was hoping there was a solution that didn't involve removing the primary key.. no luck -- I've posted to the github for MySqlConnector hoping someone has a suggestion. Basically, when using the MySqlConnector if you query a table that has a primary key and only leverage columns from the primary key, then it will return a distinct on those columns... almost as if there is an optimization that is query the index directly, instead of the table... So, the first -- drop your primary key (uggh) or query more/all columns. |
Adding fields to the query works, but so does the -Stream option you suggested, so that's the most straightforward for now. Until MySqlConnector stops "optimizing" my query to return what it THINKS I want instead of what I asked for ;-) |
MySqlConnector is an ADO.NET layer that sends the SQL to the database and returns the results. It doesn't do any "optimizing" of the query. The results coming back from |
@kwein123 - So I was incorrect, MySqlConnector is not 'optimizing' @kwein123 -- I'm updated my own code to no longer use |
@kwein123 -- published, closing this issue. |
Thanks so much for your persistence on this! |
The following set of SELECT queries should each return 77 rows since there is no WHERE clause in any of the queries. Yet you can see that I get a different number of records back depending on the list of fields I am selecting, as though there was an implicit " | Unique" at the end of the line:
This table has an index of "Server, Share, DirectoryName, FileName" - but that shouldn't matter for how many rows the above queries return. But there are only 12 unique servers, and only 20 unique Server/Share combinations.
Note: All queries return 77 records, as expected, when run in SQLyog.
(I am running the queries against a MySQL 8.0.36 server, using SimplySql 2.0.2.70, Open-MySqlConnection)
The text was updated successfully, but these errors were encountered: