The 2nd Monitor

Quill

00:08

11

Q: In the parchment the Goblet of Fire spit out, why wasn't the school name there?

In the Goblet of Fire, students were supposed to put their name and the name of their school in the Parchment they put inside the Goblet. It is clearly stated that Fred wrote "Fred Weasly, Hogwarts", in his. But when the parchments come out, the school's name isn't there. It simply says 'Harry ...

harry-potter

The Goblet returns the original parchment, but Moody called the new school "". That's right, he named the new school blank, what you'd call in computing an empty string. This way he has a fourth school, and Harry's would seem to come out with just his name on it.
So the Goblet was a program and Moody hacked it!! LOL. Moody is 'The One'

nhgrif

I haven't heard of that programming language... harry-potter...?

SirPython

@nhgrif You need to get out more ;-)

Quill

Scifi.SO: clearly any time a program is hacked, that automatically becomes The Matrix. matrix puns ensue

Jeroen Vannevel

Come on Gradle, how hard is it to use the package I define in my dependencies instead of the one your come with

nhgrif

Look out.

Jeroen Vannevel

00:11

NuGet does this without any problem. Why can't youuuuuuu

nhgrif

@JeroenVannevel is writing some non C# stuff.

Non-stop complaints about everything ensue.

Jeroen Vannevel

And with reason!

nhgrif

If you count inexperience or ignorance as reason, sure.

Quill

What language are you writing in now Jeroen?

Jeroen Vannevel

@Quill Java

Quill

00:12

@nhgrif Shots fired

nhgrif

I'd probably have the same complaints as you're having now... so try not to take offense.

Jamal

Crouch/Moody must've used SQL injection to fool the goblet into thinking There Are Four Lights er... schools. — Chahk 7 hours ago

nhgrif

The only reason I'd never complain about C# is because I've written so much VB.

Jeroen Vannevel

I define a dependency on org.apache.httpcomponents:fluent-hc:4.4.1 which has a dependency behind the scenes on HttpClient. But HttpClient is provided by default by Android. So now it refuses to load my Fluent dependency because it is too dumb to realize it can use the dependency that I bring

Even explicitly declaring my own dependency on HttpClient doesn't go for Gradle.

nhgrif

Have you tried looking on Stack Overflow?

Jeroen Vannevel

00:17

I've found someone with the exact same error but I don't see what in his gradle script would have fixed it

It's a code dump so no direction from him

1

A: Gradle build dependancy throwing ClassNotFoundException

After some research I have used the following build script to build my Jar successfully. apply plugin: 'java' apply plugin: 'eclipse' defaultTasks 'clean', 'build' repositories { mavenCentral() } jar { from { configurations.compile.collect { it.isDirectory() ? it :...

Quill

"because it is too dumb"
I chuckled

Captain Obvious

0

Q: Remove K digits of a number N in order to maximize N

The first line has 2 integers \$S\$ and \$K\$ that represents respectively the number of digits in \$N\$ and the number of digits to be removed of \$N\$. A second line is given with \$N\$. (\$1 \leq K \leq N \leq 10^5\$) The problem asks to remove the \$K\$ digits in order to maximize the value ...

java programming-challenge time-limit-exceeded

Hosch250

Oh brother, can't I just use Linq instead of SQL?

I don't get it :(

Jeroen Vannevel

Knowing SQL is invaluable for creating performant LINQ queries

Hosch250

Maybe.

I can guess at the computational expensiveness of my Linq queries, and put them in ifs and the like.

This thing just keeps timing out on me :(

I think it is the same as an O(n^2) double-nested loop at a minimum, probably worse.

nhgrif

00:23

uh

This is why you need to do pure SQL.

Because you need to learn how to think about data and data sets.

Hosch250

OK.

nhgrif

@sᴉɔuɐɹɥԀ and I have already solved the query you were trying to write.

It's possible to do it without a GROUP BY. ;)

Hosch250

I just need to learn it, but it is annoying.

OK, that is part of the problem.

nhgrif

What's part of the problem?

Hosch250

The time out.

nhgrif

00:26

Yes, the time out is the problem... but the group by isn't necessarily the cause of it.

Let me see your current version of the query.

Hosch250

WITH FirstQs(QCreationDate, PostId, UserId)
AS
(
  SELECT
  MIN(p.CreationDate) [CreationDate]
  ,MIN(p.Id) [PostId]
  ,MIN(u.Id) [UserId]
  FROM Users u JOIN Posts p ON u.Id = p.OwnerUserId
  WHERE (p.PostTypeId = 1)
  GROUP BY u.Id
),

FirstQsA(ACreationDate, APostId, AUserId)
AS
(
  SELECT
  MIN(p.CreationDate) [ACreationDate]
  ,MIN(p.Id) [APostId]
  ,MIN(u.Id) [AUserId]
  FROM FirstQs, Posts p JOIN Posts p  ON u.Id = p.OwnerUserId
  WHERE (p.PostTypeId = 2 AND p.ParentId = PostId)
  GROUP BY p.ParentId

nhgrif

can you just give me the SEDE link?

DJanssens

Evening all °/

Hosch250

First off, it is only returning one answer.

data.stackexchange.com/codereview/query/302039/…

sᴉɔuɐɹɥԀ

@nhgrif I found a problem with mine after doing a sanity check on the data, also. Which is easily solved with an additional WHERE clause but didn't know it was there until I dug into the data

@DJanssens \o

DJanssens

00:27

Just checked out the new star wars trailer #2. Oh boy, oh boy..

Hosch250

Second, I know this is wrong, but I am using MIN for now to avoid the GROUP BY in the main block.

sᴉɔuɐɹɥԀ

FROM
  FirstQs
  ,FirstQsA
  ,Posts p

^ Ambiguous implicit join

That's gotta be it

Hosch250

OK, so what should I be doing?

nhgrif

Be explicit about how the tables join.

Hosch250

Gotta be what?

OK.

sᴉɔuɐɹɥԀ

00:28

Well first, stop using pre-ANSI-92 joins

nhgrif

What column in FirstQs links to FirstQsA?

This join takes 68% of your execution time.

sᴉɔuɐɹɥԀ

7 hours ago, by sᴉɔuɐɹɥԀ

I found a nasty 68% cost nested loop in your execution plan, that's probably what is making it slow

nhgrif

@Hosch250 Check mark the "Include Execution Plan"

Hosch250

OK.

OK, great.

Now, it is returning just one value.

nhgrif

My query takes 143ms to return the 8,646 rows.

sᴉɔuɐɹɥԀ

00:32

@Hosch250 Found the problem

,MIN(u.Id) [UserId]

You're selecting the first UserId (in this case, user # 20) which matches your criteria, and stopping there

Hosch250

Did one of you change something?

sᴉɔuɐɹɥԀ

Remove MIN() there and you'll be on to something

Hosch250

It started working all of a sudden, and I'm not sure if I did it or you.

nhgrif

I didn't change anything.

Hosch250

Oh, I think I did it.

sᴉɔuɐɹɥԀ

00:34

> (9173 row(s) returned)

^ without the MIN(), 168ms

Hosch250

I added the GROUP BY to the FirstQsA and it worked.

@sᴉɔuɐɹɥԀ Did you change my query?

I have 9173 in 301ms.

sᴉɔuɐɹɥԀ

I can't change your query, only fork it (which is what I did)

Hosch250

Oh, OK.

Then I did it.

OK, so it is working right now.

nhgrif

Hosch250

You are not in it, and I am (that's how I test it).

nhgrif

00:37

What?

sᴉɔuɐɹɥԀ

So, you got rid of that nasty 68% nested loop, and it appears the data set makes sense, though I've found edge cases while writing my version (which has more information in the output)

nhgrif

How many rows are you returning, @Hosch250?

Hosch250

4.

nhgrif

Well, 4 is wrong.

Hosch250

No, that is columns.

9173 rows.

sᴉɔuɐɹɥԀ

00:38

Important distinction!

nhgrif

9173 is too high.

It's close though.

Hosch250

Hmm.

sᴉɔuɐɹɥԀ

Found another thing

nhgrif

Should be 8,646. I'm not sure what the easiest way to determine the inconsistencies though.

sᴉɔuɐɹɥԀ

  SELECT
  MIN(p.CreationDate) [CreationDate]
  ,MIN(p.Id) [PostId]

You're finding the lowest CreationDate, and the lowest PostId

nhgrif

00:40

You need to find only the lowest CreationDate.

sᴉɔuɐɹɥԀ

PostId is not guaranteed to be sequential, I would remove the MIN() check there

Hosch250

And add it to the GROUP BY clause?

sᴉɔuɐɹɥԀ

Ids sometimes get reseeded, so they are not good indicators of a record's real age. Creation date is an auto-populate and cannot change

Hosch250

OK.

nhgrif

There are exactly 2 in 2008.

Then none in 2009 or 2010.

914 in 2011.

1443 in 2012,.

Hosch250

00:43

I'm not getting it.

nhgrif

1937 in 2013.

sᴉɔuɐɹɥԀ

ID-type fields are mostly for indexing, and for joins. They should not be relied upon for "understanding" the data

nhgrif

3394 in 2014

and 956 to date in 2015

sᴉɔuɐɹɥԀ

Oh damn, that's good to see @nhgrif!

Hosch250

When I remove the MIN, it returns every post that has an answer posted within 24 hours or something.

nhgrif

00:44

ID fields are for keys and indexes.

sᴉɔuɐɹɥԀ

@Hosch250 How many?

Hosch250

16632.

nhgrif

By the way, @Hosch250, by no means is this an easy query...

Hosch250

@nhgrif That is alright.

sᴉɔuɐɹɥԀ

It's an instructive query, though :)

Hosch250

00:46

It actually hasn't been too difficult.

Frustrating sometimes, when I can't just walk through the debugger...

Sooo, I could just remove the IDs, but then I can't link to the post.

Wait, I can't.

sᴉɔuɐɹɥԀ

You need them for joins

Hosch250

OK, so I remove the stupid MIN, and it gives this error:

> Column 'Posts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sᴉɔuɐɹɥԀ

Yep. Add that to your GROUP BY

nhgrif

No.

Hosch250

So, I put it in a GROUP BY clause, and it returns bad data (which is worse than crashing, IMO).

nhgrif

00:49

How will that help?

Hosch250

It doesn't.

It times out, it gives bad data...

nhgrif

@Hosch250 Start a new query. Break this down into the data sets you need.

Hosch250

OK.

So, what do I do in my new query?

nhgrif

Dataset 1: PostID for questions where CreationDate = MIN(CreationDate) grouped by User.
Dataset 2: PostID for answers where CreationDate = MIN(CreationDate) group by ParentID

Right?

These are two of the datasets you need to get, correct?

Hosch250

OK.

nhgrif

00:50

So work on these queries individually.

Hosch250

Yeah, at least, I thought so a while ago...

nhgrif

Don't worry about doing anything else until you can produce each of these individually.

Hosch250

Alright, sounds like what I would do in C#...

nhgrif

SQL is all about thinking in terms of datasets.

sᴉɔuɐɹɥԀ

Preach it!

nhgrif

00:51

Make one dataset work. Make another dataset work. Then make them work together.

Hosch250

Sounds like a feature for my app.

First, get a simple version working, then get the full thing working, then put it in the production code and really test it.

data.stackexchange.com/codereview/query/302426/…

Quill

0

Q: One way encoding a password

Alright, so I wrote a script that one way encrypts a user's password by generating a key, and multiplies by the ASCII value of the character and the ASCII value of the key character at (the position that the character has in the password string) and repeats this for the previous positions in the ...

php security

nhgrif

@Hosch250 something is wrong with that.

Hang on, let me organize my windows

Hosch250

You bet it is.

nhgrif

You got 22377 rows

Mine gets 11675 rows

You're grouping by user ID and Post ID.

brb

Hosch250

00:55

That is because it doesn't work the other way.

> Column 'Posts.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Captain Obvious

1

Q: One way encoding a password

Alright, so I wrote a script that one way encrypts a user's password by generating a key, and multiplies by the ASCII value of the character and the ASCII value of the key character at (the position that the character has in the password string) and repeats this for the previous positions in the ...

php security

Hosch250

So, why does that need an aggregate function and not this?

SELECT u.DisplayName'Display Name', u.Reputation'Rep', u.Location'Location'
FROM Users u
WHERE u.Location LIKE '%Australia%'
ORDER BY 'Rep' DESC

If I could get past that error, I think I could fix it.

sᴉɔuɐɹɥԀ

@Hosch250 Because that one doesn't do any aggregation

Hosch250

Is it something to do with the JOIN?

sᴉɔuɐɹɥԀ

Nope

Transcript for