The Heap™ – Consultancy ©®

I'm cool with the way things are now. my original edit had the wrong table name, @ypercubeᵀᴹ is right it should have been tmp. I also should have had it typed as ::timestamp, but again these are minor issues and not worth fighting about. CTAS or DDL is fine and does the same thing, it's about style at that point. Let's just not be rewriting things for mere style moving forward.

Paul White

Seems reasonable.

Evan Carroll

12:06 AM

I already in fact edited my answer to use the new tmp name.

McNets

I hope that OP doesn't store it as string...

Paul White

Ha. Wouldn't be the first time.

Evan Carroll

I like your answer @McNets over joalondo there.

if you're not going to go window function, don't go window function fully!

Paul White

BTW @ypercubeᵀᴹ while we're waiting for Erwin, did you get an answer to that 1/300s thing? I have this idea is was about Windows timer resolution?

ypercubeᵀᴹ

@PaulWhite No, Im not sure at all.

McNets

12:11 AM

@EvanCarroll In fact I was trying to find a reset value as your answer do.

And then set a group

ypercubeᵀᴹ

Celko says - in a somewhat passing comment - in one of his blogs that it was Unix related.

Paul White

@ypercubeᵀᴹ Oh yes I've read that before. Hm. Not convinced either. Could be I suppose.

ypercubeᵀᴹ

Or there Unix / machine related and the limitations of clocks at that era - when the datetime was first implemented

@PaulWhite Yeah, seems plausible. If I see a blog post by a Sybase/Microsoft engineer from the 90s, I'll believe it ;)

Joe Obbish

@PaulWhite Glad to hear that. I did watch his parallelism talk on youtube at pass summit 2013 (?). I think you were monitoring the twitter feed

before that I thought that I kind of understood parallel execution

after it I feel like I do, although that's probably incorrect

Paul White

@ypercubeᵀᴹ Nah. I'd need to see some source code :)

Joe Obbish

12:14 AM

interestingly enough, I'm looking right now at a query that does not go parallel

Paul White

@JoeObbish Yes that's right! Well remembered. I had just finished my half-day I think.

ypercubeᵀᴹ

@PaulWhite and comments in the source code!

Joe Obbish

but with the evil TF 8649... CPU time = 288374 ms, elapsed time = 52589 ms.

I am tempted :(

Paul White

There's nothing much wrong with 8649. Except being undocumented.

Joe Obbish

I do not have the required "decades of experience" ;)

Erwin Brandstetter

12:15 AM

I don't have anything to add on the question edit confusion. That seems all clear now. But I take offense when @Evan writes: I almost think he copy pasted it. I answered that kind of question four years ago and many variants since. In fact, this one is mostly a duplicate question to the one I referenced. I only added an answer to slip in some special details, the rest is in the links.

Paul White

@ypercubeᵀᴹ Comments >> code :)

ypercubeᵀᴹ

Do they keep them somewhere at Microsoft / Sybase? (I mean the source code from very old versions (Windows and applications)?

Paul White

@ErwinBrandstetter Thank you I'll just unlock the question in its current state then @ypercubeᵀᴹ @EvanCarroll

@ypercubeᵀᴹ One would hope so!

Evan Carroll

@ErwinBrandstetter It's certainly almost a duplicate question. And you certainly knew this method of solving islands-and-gaps before me, as I only learned it like 3 months ago. And, you certainly know more about PostgreSQL than me. That all said, I didn't see what you're doing differently. It wasn't apparent to me where the divergence in approach was..

Erwin Brandstetter

@Evan: That your base to slip in an accusation I would be copy / pasting your answer? Your guns are sitting loose.

Paul White

12:24 AM

@EvanCarroll Are you planning to respond? I've been woken up for this, and am hoping to get it resolved so I can go back to sleep.

Evan Carroll

Respond to what? How am I going to defend a claim to copy-pasting. That's a trap. I don't care to defend that claim. I retract it entirely.

Let the timestamps and content of the answers speak for themselves. I answered first to my merit this time. Substantially before Erwin. And he had the same great thought just 15 minutes later in almost every way.

Paul White

@EvanCarroll I guess I was expecting either an explanation (poor wording?) and/or an apology to Erwin.

Evan Carroll

Guess the thought fairies liked me more today.

Paul White

But if @ErwinBrandstetter is happy with that and we're all friends again, I can go back to bed.

ypercubeᵀᴹ

I go anyway. Sorry guys, see you tomorrow. Very early wake up.

Paul White

12:27 AM

@ypercubeᵀᴹ See ya. Thanks.

Erwin Brandstetter

@Paul: I don't think there is anything else for you to do here.

Paul White

Cheers. Goodnight all. Try not to shoot anyone.

Evan Carroll

=) good night.

McNets

nite all

Erwin Brandstetter

@Evan: I added a link to my prior answer two minutes after you posted your first draft, which I only saw after that, and which was still under construction when I saw it. If anyone could claim prior art, it's me, but I am not going down that silly road. You accuse me of copy-pasting and then instead of saying sorry, you accuse me again of laying a trap, which is .. just weird. I have the feeling you are not aware how offensive you are.

All that aside, your answer is still plain incorrect, like I commented.

Good night all.

Joe Obbish

2:24 AM

Hey @AaronBertrand, apologies but I don't quite follow your comment on 166404.

Are you saying that something like this still does loops behind the scenes? dbfiddle.uk/…

So there isn't a solution that avoids looping?

Aaron Bertrand

2:55 AM

@Joe The OP is on 2012, not on v.Next which is required for STRING_AGG, but do you think STRING_SPLIT avoids loops? You don't have to type them, and they're probably implemented a lot more efficiently than while charindex junk, but they're there.

Same with FOR XML PATH, set-based concatenation, and other solutions. It doesn't say WHILE but there's a loop in there somewhere. Letting a newbie believe there aren't loops at some layer is the same kind of thinking that leads people to believe while loops are faster than cursors because they're not cursors.

Joe Obbish

Ok, I understand what you mean now, thanks.

I'm used to working in situations where the application restricts what SQL can be sent to the database

so it seems perfectly reasonable to ask for a single query solution that performs well instead of CHARINDEX junk

but, the question needs a lot of work

hopefully he comes back and edits

Andriy M

7:14 AM

Good morning

@JSapkota: Should be count(diff)+1 but otherwise please consider posting an answer. — Andriy M 1 min ago

ypercubeᵀᴹ

m

Andriy M

mm?

2

Jack Douglas

mmmmobile

Andriy M

mmmakes sense

Evan Carroll

@McNets He is actually storing it as a string. =(

which is terrifying.. You can tell because he says he gets this as a desired result 2017-01-10 07:19:21.0 2017-01-10 07:19:32.0 and for the .0 to not be truncated with that query, he must be storing it as text.

lol, so we can reinvigorate this dilemma!

Andriy M

7:30 AM

@EvanCarroll Couldn't he be using timestamp(1)?

McNets

mmmorning

Evan Carroll

@AndriyM nope

select '2017-01-10 07:19:29.0'::timestamp(1);
timestamp
---------------------
2017-01-10 07:19:29

the .0 gets truncated.

@ErwinBrandstetter @ypercubeᵀᴹ dba.stackexchange.com/q/166419/2639

McNets

@EvanCarroll he uses min(), max():

SELECT distinct
min(min(date)) over w as begin,
max(max(date)) over w as end,
id_type
from tmp
GROUP BY id_type
WINDOW w as (PARTITION BY id_type)
order by begin;

Evan Carroll

text has a min max too...

Using collation

so long as it's of equal length, it'll even work pretty well.. Try it.

Andriy M

7:55 AM

@EvanCarroll I see, that makes sense, thanks. I understand that's how you see the timestamps when using PostgreSQL's standard management tool (psql or whatever it's called).

That is, it truncates trailing (non-significant) 0s. Perhaps it's not very likely that the OP is using a different tool to display the results. But if they do, how likely is it that the other tool would truncate the trailing 0s in the fractional part as well?

McNets

@EvanCarroll yes it works fine.

ypercubeᵀᴹ

11:06 AM

@EvanCarroll For all we know, they could be using left(column::text,21) (or the tool they use does that)

Philᵀᴹ

11:29 AM

0

Q: Convert table from MyISAM to InnoDB and keep MyISAM rows order

I'm currently rewriting old project and I've got this strange scenario. It's an online store, it has products with images. The images are stored in separate table. Here is example query. The part after the id- in the img column is the same for every row for pid. I want to migrate this table to ...

Oh dear

McNets

12:54 PM

I wonder if OP will review the. question again...

McNets

1:45 PM

Yep, he did it.

McNets

2:22 PM

Why do I get banners like this on SO?

bluefeet

@McNets it based on some settings on your browser. If they are set to a language that we have a language specific Stack Overflow we let you know it's available

McNets

@bluefeet but has been activated today?

bluefeet

@McNets Not sure. I believe they have been doing some testing of it, it's possible you hit the test

McNets

@bluefeet I'm feeling like a guinea pig ;) Thanks

Lamak

@McNets you don't speak spanish?

McNets

2:31 PM

@Lamak is a question?

it

Lamak

@McNets of course

;)

McNets

@Lamak I do! ;)

Lamak

@McNets then you should visit SOes someday :)

McNets

I think I have about 500 rep there

Lamak

@McNets I see...yeah, I have like 1,500 rep there too

Philᵀᴹ

2:35 PM

¿To make a sentence Spanish, don't you just put an upside down question mark at the start?

Lamak

@Philᵀᴹ that's the gist of it

McNets

@Philᵀᴹ correct, as using exclamation mark ¡OH Nice!

Lamak

@Philᵀᴹ you can also ask @billinkc to teach you some spanish

Andriy M

He taught @Lamak

Philᵀᴹ

⸘Or maybe just this‽

Andriy M

2:40 PM

Or was taught by @Lamak. Sometimes you are not sure with those two.

Lamak

@AndriyM we'll never know

Paul White

@AndriyM Our very own Heap Bromance :)

Lamak

@PaulWhite I just threw up a little

Paul White

I'm off to a good start today then.

@dezso Of course. I believe it is the SQL Standard name-sexy-value operator.

Andriy M

2:58 PM

The newest draft proposes it be replaced with ‽‽

Paul White

Ah thanks! It's so hard to keep up.

dezso

@AndriyM sadly,

>The operator name is a sequence of up to NAMEDATALEN-1 (63 by default) characters from the following list:

+ - * / < > = ~ ! @ # % ^ & | ` ?

(with some further restrictions)

Paul White

Maybe it's different for Postgres.pt, which is no doubt an extension or spin-off.

Jack Douglas

3:42 PM

Thanks @Paul ;)

Paul White

No worries.

McNets

@PaulWhite is it better a inline function? aren't my functions deterministic?

Paul White

@McNets Yes inline functions are almost always better - they are inlined into the calling query much like a view is. Deterministic or not, scalar function code is fully executed once per row - much like running a separate query per row.

McNets

@PaulWhite thanks. let me try with the inline version.

Paul White

Or, in your specific case, by all means save the single result of the function calls into a variable before running the main query.

Joe Obbish

3:55 PM

are we talking about scalar UDFs?

McNets

@PaulWhite by now this is what I did.

Paul White

@JoeObbish Again, yes :)

Joe Obbish

what's the context?

Paul White

1

A: Help constructing a query specifically querying against different dates

First and foremost, I'd suggest you to create two functions just to calculate fiscal months and fiscal years. CREATE FUNCTION dbo.fnFiscalMonth(@Date datetime) RETURNS int AS BEGIN DECLARE @Month int = MONTH(@Date); IF @Month >= 1 AND @Month <= 4 SET @Month = @Month + 8 ELSE...

Sorry took me a moment to locate it. Had closed the tab.

Joe Obbish

ah

@McNets, you might find this answer helpful:

1

A: When is it appropriate to use a scalar valued function?

There are good reasons to be critical of the performance of scalar UDFs. Performance problems include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. However, that doesn't mean that they don't have good use cases. Consid...

McNets

3:59 PM

@JoeObbish thank you.

Joe Obbish

@McNets You're welcome. I am happy to talk people out of using scalar UDFs

if you put them in variables like Paul suggested imo it's not a bad use case

McNets

@JoeObbish ;)

Paul White

The issue being that if you provide such scalar functions, there is little to stop other people using them in dumb ways.

Lamak

@JoeObbish he doesn't identify as @PaulWhite anymore....but I don't remember his current name

Paul White

20 hours ago, by Paul White

You may now address me as Mr BA58C248-924B-47FA-8F37-8B0351F4BD3D

It's right there on the star wall!

Andriy M

4:12 PM

Exactly

hot2use

@PaulWhite better up it one so it doesn't vanish

Lamak

@PaulWhite ah, yeah, it's there

Paul White

Amateurs.

McNets

What I am doing wrong? dbfiddle.uk/…

Paul White

@McNets You have a CASE missing :) SELECT [CASE] WHEN MONTH(@Date) <= 4

Lamak

4:18 PM

@McNets you are spending time in The Heap

McNets

@PaulWhite arrggg, looking at last error message I've loosed the first one

Lamak

4:35 PM

I don't get how some people expect us to read a whole book of a code

Joe Obbish

eh, what he really needs is plans and other useful information

if he wants to put the query text in a pastebin, great

better than providing no info about the query

of course I would prefer a MCVE or however that goes

Paul White

@Lamak I don't get how people expect the system to work properly when they don't vote on questions.

ypercubeᵀᴹ

-1

A: datetime fractional seconds

Based on this answer, it is clear that the exact encoding is opaque and counter-intuitive. It is also clear that the statement in Sybase documentation to the effect that datetime can hold values accurate to 1/300 second is misleading and imprecise. What the documentation should have said is that...

wBob

I don't system how people expect the get to work properly when they don't questions on the votes.

3

ypercubeᵀᴹ

He could have just written "I did not understand Max Vernon's answer"

Max Vernon

4:42 PM

@ypercubeᵀᴹ lol

ypercubeᵀᴹ

> It is also clear that the statement in Sybase documentation to the effect that datetime can hold values accurate to 1/300 second is misleading and imprecise.

Joe Obbish

nice answer Max

Max Vernon

thanks, Joe

@ypercubeᵀᴹ yah, I lol'd at that

Paul White

@ypercubeᵀᴹ So clear it does not need explaining.

Max Vernon

I'm lucky enough to have access to Adaptive Server Enterprise on AIX here, so I can test stuff like that.

ypercubeᵀᴹ

4:47 PM

@MaxVernon You can add in your answer that a small difference between SQL Server and Sybase is on the (formatting of the) output of the 1/300. SQL Server rounds to .xx0, .xx3, .xx7 while Sybase rounds down to .xx0, .xx3, .xx6

(if I understand correctly and that output is consistent)

Philᵀᴹ

<3 ASE

I started on Sybase ;P

ypercubeᵀᴹ

Did you just add Postgres 8.4 to dbfiddle? Cool, for questions like this one! — Erwin Brandstetter 48 mins ago

3

Philᵀᴹ

Running on TRU64

ypercubeᵀᴹ

Ha, @JackDouglas, will you add a SQL Server 6.5 as well?

3

Max Vernon

@ypercubeᵀᴹ it certainly seems to do that

I'll update

Jack Douglas

4:56 PM

@ypercubeᵀᴹ it crossed my mind to add 2000 — we do still see it come up in questions from time to time

Max Vernon

@Philᵀᴹ nice!

Lamak

5:29 PM

@PaulWhite I voted, then removed the downvote because I wanted to vote to close...then remembered I couldn't, and then I just gave up

:)

ypercubeᵀᴹ

@Lamak you should have posted an answer and get closer to vtc privilege ;)

Lamak

I should have

Joe Obbish

I can't convince the query optimizer that my data is already sorted

It is, I swear!

Paul White

@Lamak You can/should vote down and vote to close independently.

Down votes are actually a more powerful signal that someone is asking poor questions.

Lamak

@PaulWhite yeah...I should have

Paul White

5:38 PM

@Lamak I forgive you because you're new here, and Bill said he would vouch for you.

wBob

@JoeObbish OPTION ( SORTED=PINKY_PROMISE );

4

Lamak

@PaulWhite when did he say that?

Paul White

@Lamak In my twisted imagination.

Lamak

I see, the dark side of @DarthPaul

3

Max Vernon

just on the off-chance anyone likes banjo music; check out Steve Martin. He's amazing.

Paul White

5:50 PM

Oct 30 '14 at 14:55, by swasheck

also - i'd really like to learn how to play banjo

Max Vernon

woah

Paul White

yep

Joe Obbish

@wBob Yes please! Have a connect link for that one?

Forrest

I wonder what kind of connect items get created on April 1st.

Joe Obbish

I suppose that they have that option for bcp and bulk insert, but the docs say that sql server verifies that the data is ordered

Paul White

6:03 PM

There's also ORDER clause for CLR functions. That gets validated in the plan with a sequence project (step_direction) and an assert.

Joe Obbish

I think I've run into this issue before in a different context

sql server will know that SELECT N is ordered

but DATEADD(MONTH, N, GETDATE()) is not ordered

Paul White

@JoeObbish Sort (!) of related to connect.microsoft.com/SQLServer/feedback/details/526431/…

Joe Obbish

sigh...

Paul White

It can be tricky (and sometimes impossible) to workaround e.g. by requiring order on the underlying ordered thingy e.g. N.

Joe Obbish

6:21 PM

for my particular example the type conversions might play a role too. not sure

but since it's a staging table I'll just change the clustering key

Max Vernon

7:27 PM

kill this: dba.stackexchange.com/q/166473/10832

McNets

@MaxVernon off topic or spam?

Max Vernon

off-topic.

Lamak

@MaxVernon seems more like spam to me

Max Vernon

could be I suppose. It's very strange spam, I guess.

Tom V

@McNets Three strikes, flag, downvote and VtC

Lamak

7:31 PM

@MaxVernon I don't know if it's off-topic. I mean, the op did say "come to join"

Max Vernon

@Lamak lol

McNets

@EvanCarroll I've tried with distinct and I can't get it

@TomV all-in-one, do it.

Lamak

@McNets it's already deleted

Tom V

Flags usually don't last very long, the other votes were just to make a point

Paul White

Nuked.

Tom V

7:37 PM

@McNets whatever action you pick, any of them might make the post show up in the next queue a mod looks at

McNets

@TomV yes I know it from SO where I can VtC

@EvanCarroll forget what I said, it works fine with distinct

Paul White

8:00 PM

@JackDouglas We were editing that question at the same time :-/

Jack Douglas

oops

shall we have an edit war?

McNets

not another one!!

Paul White

@JackDouglas Ha! Just letting you know in case I messed something up.

I dislike that orange bar.

Answer accepted so I guess all's well that ends well.

McNets

Just curious, to comute is from home to work or it is applied from work to home too?

Joe Obbish

both

Forrest

8:06 PM

@JoeObbish It's commutative.

4

Paul White

9

Q: Does "commute to" refer to both there and back from work?

I want to tell someone the following: "I bring an e-reader with me on the bus every day so that I have something to do during my commute to work." Does the phrase "commute to work" clearly refer to the trip there and the trip back from work? Or is it more proper to say "commute to and from work" ...

meaning

@Forrest Ha! Took me a moment.

Andriy M

Like rush hour

McNets

Thaks all

Evan Carroll

8:37 PM

dafaq

@EvanCarroll I want to get the result with and without groupping on sl.product_id — m3asmi 3 mins ago

and I already retracted my close vote

damn

Lamak

@EvanCarroll he just wants the result with and without groupping on sl.product_id

Evan Carroll

What does that even mean?!

Lamak

I don't know

3

Evan Carroll

I want GROUP BY (a) and GROUP BY (a,b) to return the *same thing*

lulz, fml.

I think he needs a new database. SQL Server has that feature afaik.

Jack Douglas

@EvanCarroll postgresql.org/docs/devel/static/…

probably not what the OP wants, I have no idea, but I thought you'd be interested

Evan Carroll

8:43 PM

I just want to see his output I know how grouping sets work. Just show me what he wants it to look like

Jack Douglas

move along

Paul White

He just wants it with and without groupping on sl.product_id.

Evan Carroll

lolol

Jack Douglas

@EvanCarroll yeah, it's groupping sets to you

Evan Carroll

I'm waiting for clarification before I spend more time on it. I don't think it's too much for him to draw up the desired output

with and without groupping on sl.product_id whatever that means

Lamak

8:46 PM

@EvanCarroll maybe he wants something like SUM(sale_qty) OVER(PARTITION BY s.id)...so he gets the same result for every value of product_id for the same id?....I don't know

Evan Carroll

we'll never know without clarification

Paul White

And groupping

Ok enough silliness. Out of here for a few hours.

Jack Douglas

silliness immediately stops as @Paul leaves the room

7

Paul White

Hey!

Evan Carroll

RIP silliness

he's self-banning silliness

Lamak

8:48 PM

silliness is back!

3

McNets

9:03 PM

@EvanCarroll I'm not sure he can clarify. He doesn't seems good with English.

He is using a join that doubles the qty sum.

Andriy M

The just added answer's got the issue right IMHO. At least if it wasn't posted, I would've posted mine about the same thing.

McNets

I've deleted mine.

I think Evan's solution is the correct answer.

Evan Carroll

9:20 PM

@AndriyM I'm not sure how the recently added answer is better at anything, except being slower. But at least it's different. My query generates the same thing

Andriy M

@EvanCarroll It correctly (again, IMHO) identifies the problem and explains the way to solve it. The point of performance is moot when the result is not correct. The OP's issue is about the result first of all.

Evan Carroll

k, well I have no idea what he's talking about so I'm out until he, or someone else, can clairify

Andriy M

The OP is not explaining the problem well, that's for sure, and it only takes analysing their data samples in combination with the expected result and the query to get what the issue is.

@EvanCarroll And your solution is not correct. If it produces the correct result, that's only because the number of distinct products happens to match the number of times each product is repeated. It's the number of repetitions that causes the actual total sales_qty, 2600 for their example, to become three times that amount in the query. So if it's possible to solve this issue by dividing the total by the count of distinct something, it should likely be the count of distinct picking_id.

Evan Carroll

9:56 PM

I disclaimed all of that in the first half of the answer. I have no idea what the questioner is looking for.

So as I said before. well I have no idea what he's talking about so I'm out until he, or someone else, can clairify

Feel free to get with him and create some real sample data with a desired result, and let's talk about how we get that desired result. It's how we solve the vast majority of problems on this site. He's going against the trend and making us solve with a lot of assumptions.

Until that point in time, it's your interpretation of his description vs my own. =( And, you may very well have the right interpretation. No idea.

I entitled the first half of the answer as Reading of problem. I'm not sure I can do more than that in an effort to help him out and disclaim the limits of my own understanding. I've never had to write a section like this before.

Max Vernon

10:11 PM

any chance we can migrate this to Stack Overflow?

McNets

10:47 PM

Is 'SELECT INTO' deprecated on SQL Server?

Joe Obbish

@McNets nope

it's the only way to get parallel inserts in SQL Server 2014

McNets

@JoeObbish thanks.

Joe Obbish

@McNets It does have a few disadvantages too. Why did you ask?

McNets

11:08 PM

@JoeObbish it is related to this question stackoverflow.com/questions/42659483/ms-sql-code-sanity-check, but in fact I misread it.

Paul White

@MaxVernon Given that there were no answers, it would have been quicker and easier for him to just delete and re-ask. Anyway, there were 4 migrate votes so I finished it off.

Joe Obbish

@McNets I've never tried that pattern before but I would expect them to be the same

from a personal style point of view I would write the second one

McNets

@JoeObbish It seems a lazy work.

Transcript for

The Heap™ – Consultancy ©®