SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Melraidin: Oct 11, 2005

I've run into some spooky MySQL stuff recently. Specifically, expressions being evaluated for each row of a resultset in a manner I'm really not comfortable with.

I'm looking for a running total of some values. This, at least in my past experience, is difficult with only SQL (at least, if you avoid cursors).

I've now found that I can do this in MySQL:

code:

SET @Total := 0;

SELECT
   UserID,
   @Total := IFNULL( @Total, 0 ) + SUM( BandwidthUsed ) AS BandwidthUsageToDate
FROM
   UserBandwidth
GROUP BY
   UserBandwidth.UserID,
   DATE( UserBandwidth.LogDate )
ORDER BY
   UserBandwidth.LogDate ASC

This returns a resultset with a user ID and their total bandwidth usage to date, grouped by day.

If I were to try the same in MS SQL:

code:

SET @Total = 0;

SELECT
   UserID,
   @Total = ISNULL( @Total, 0 ) + SUM( BandwidthUsed ) AS BandwidthUsageToDate
FROM
   UserBandwidth
GROUP BY
   UserBandwidth.UserID,
   DATEPART( YEAR, UserBandwidth.LogDate ),
   DATEPART( MONTH, UserBandwidth.LogDate ),
   DATEPART( DAY, UserBandwidth.LogDate )
ORDER BY
   UserBandwidth.LogDate ASC

I get this error: 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.'

Is there a way to get a running total from MS SQL in the same manner as from MySQL? Or, as I suspect, is getting a running total from MySQL a bad idea to begin with?

# ¿ Dec 5, 2007 15:41

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 21:19

Melraidin: Oct 11, 2005

Victor posted:

How does the MySQL example actually do what you think it is doing? You are grouping by UserID, but only getting one row back -- do you only have one user? If you are convinced that MySQL really is doing the proper thing, do post the results, because I would be very surprised.

The TSQL version can be fixed by only assigning to variables, and not retrieving data. However, be warned that, at least if you throw in an ORDER BY clause, SQL Server is not guaranteed to populate your variable properly. If you'd like, I can yank a script that generates pretty CREATE TABLE code from INFORMATION_SCHEMA, which assigns to a @sql variable in the select statement in the style above, and breaks (only returns one row) if I add an ORDER BY ORDINAL_POSITION, in SQL 2005 (SQL 2000 works fine with that ORDER BY).

Edit: if you're using SQL 2005, do this properly by writing an SQL CLR aggregate. I have two such aggregates: dbo.JoinWithPipe and dbo.JoinWithCommaSpace.

You're right that my example sucked. I tried to write something that would be a bit easier to understand than the actual code, but I sure screwed that one up...

A better example, I hope (better since it's not broken):

code:

SELECT
   DATE( UserBandwidth.LogDate ) AS EntryDate,
   @Total := IFNULL( @Total, 0 ) + SUM( BandwidthUsed ) AS BandwidthUsageToDate
FROM
   UserBandwidth
WHERE
   UserBandwidth.UserID = 21
GROUP BY
   DATE( UserBandwidth.LogDate )
ORDER BY
   DATE( UserBandwidth.LogDate ) ASC

This query is intended to retrieve the date this user (21) used some bandwidth as well as a running total of the user's bandwidth usage.

In MySQL the equivalent of this query on my DB gives:

code:

EntryDate      BandwidthUsageToDate
2007-01-01     31
2007-01-02     63
2007-01-04     82
2007-01-09     123

The second paragraph of your post refers to only assigning to a variable in the query. This is all that I would expect to be allowed to do with MS SQL, but this doesn't resolve the issue, as I would of course only be getting the single value, where I need a value for each log date.

Could you elaborate on your edit? I'm not certain what portion of my post this is in reference to.

Alex007 posted:

Calculating Running Totals, Subtotals and Grand Total in MSSQL Without a Cursor

Thanks for the link, and this does do what I need. This article shows examples of solving this in the manner I would expect, but hadn't thought of. The major difference between this method (a sub-query for each row) and the MySQL method, at least from my understanding, is the MySQL method will only do the single query on the table, while the article's method will be doing Number of Rows + 1 queries on the table.

# ¿ Dec 5, 2007 16:29

Melraidin: Oct 11, 2005

Victor posted:

What version of SQL Server are you using? I realize I misread your first post after you posted sample results, so I'm pretty sure you can't do what you want with variable assignment, but I think you can another way. SQL2005 offers a lot of sweet functionality that could potentially make this a lot easier.

MS SQL Server 2000 I believe. Even so, could you point me to these functions in SQL Server 2005, just for interest's sake?

# ¿ Dec 5, 2007 19:23

Melraidin: Oct 11, 2005

Yakattak posted:

Anyone have some tips for password security in MySQL? For instance, how do I store them, and then retrieve then securely with encryption? Does the MySQL password() function do that safely?

Salt and hash the passwords. This will prevent people with access to the DB from being able to determine the original password in any reasonable time. If you only hash the passwords then rainbow tables could be used to find values that would hash to the same password.

I use something along these lines:

code:

dbPassword = CONCAT(
    LEFT( dbPassword, {SaltLength} ),
    MD5( CONCAT( LEFT( dbPassword, {SaltLength} ), '{userPassword}' ) )
)

Where dbPassword is the field in the DB containing the salted and hashed PW, SaltLength is the length of the salt in characters, and userPassword is the PW the user has entered you're testing against.

------------------------

Now for my question:

I need to replicate certain data in certain tables to certain servers. This is to ensure some data is only on servers we're allowed to host it on.

We have a MySQL master-master replication with some slaves off of one of the masters. I'm considering horizontally partitioning the tables involved on a new field indicating where the data may be stored. Once this is set up I think I can have each partition stored in separate files then on the hosts we cannot store this data on I'll symlink the data files to /dev/null. I'll need to ensure all operations involving the data that can only be stored conditionally occur on hosts where the data is actually stored.

Does this make sense? Is it at all sane? Can anyone suggest other ways of handling this? This is on an existing system and we're of course trying to minimize the changes to support this requirement.

# ¿ Feb 4, 2011 16:50

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'