Windowing functions in recursive CTE

Written on Jul, 13, 2019

Today we will see an interesting case of incompatibility between MS SQL Server 2017 and PostgreSQL 9.6 (and different versions as well). Let’s start with this code:

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase
),
solution AS (
    SELECT * FROM dummy
),
solution2 AS(
    SELECT
        SUM(rowValue) OVER (PARTITION BY phase) AS rowValue,
        phase + 1 AS phase
    FROM solution
    WHERE phase = 0
)
SELECT *
FROM solution2
WHERE phase = 1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

WITH dummy AS(

SELECT 1 AS rowValue, 0 AS phase

UNION ALL

SELECT 2 AS rowValue, 0 AS phase

),

solution AS (

SELECT * FROM dummy

),

solution2 AS(

SELECT

SUM(rowValue) OVER (PARTITION BY phase) AS rowValue,

phase + 1 AS phase

FROM solution

WHERE phase = 0

)

SELECT *

FROM solution2

WHERE phase = 1

We emulate a recursive CTE. We have two columns in source dataset, we want to…

Running On Screen Keyboard in Windows 10 in C#

Written on Jul, 06, 2019

Today something supposedly very simple. How to run On Screen Keyboard from C#? Easy enough you say and type the following code:

Process.Start(@"osk.exe");

1	Process.Start(@"osk.exe");

And then you compile it as Any CPU, run on Windows 10 x64 and you get the following error: What…

Machine Learning Part 6 — Matrix multiplication in SQL

Written on Jun, 29, 2019

This is the sixth part of the ML series. For your convenience you can find other parts in the table of contents in Part 1 – Linear regression in MXNet Today we are going to implement a matrix multiplication in Redshift. Let’s go.…

Spark and NegativeArraySizeException

Written on Jun, 22, 2019

Recently I was debugging the following crash in Spark:

java.lang.NegativeArraySizeException
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.resize(IdentityObjectIntMap.java:447)
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.putStash(IdentityObjectIntMap.java:245)
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.push(IdentityObjectIntMap.java:239)
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.put(IdentityObjectIntMap.java:135)
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.putStash(IdentityObjectIntMap.java:246)
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.push(IdentityObjectIntMap.java:239)
	at com.esotericsoftware.kryo.util.IdentityObjectIntMap.put(IdentityObjectIntMap.java:135)
	at com.esotericsoftware.kryo.util.MapReferenceResolver.addWrittenObject(MapReferenceResolver.java:41)
	at com.esotericsoftware.kryo.Kryo.writeReferenceOrNull(Kryo.java:658)
	at com.esotericsoftware.kryo.Kryo.writeClassAndObject(Kryo.java:623)
	at com.twitter.chill.Tuple2Serializer.write(TupleSerializers.scala:37)
	at com.twitter.chill.Tuple2Serializer.write(TupleSerializers.scala:33)
	at com.esotericsoftware.kryo.Kryo.writeClassAndObject(Kryo.java:628)
	at com.twitter.chill.TraversableSerializer$$anonfun$write$1.apply(Traversable.scala:29)
	at com.twitter.chill.TraversableSerializer$$anonfun$write$1.apply(Traversable.scala:27)
	at scala.collection.mutable.HashMap$$anonfun$foreach$1.apply(HashMap.scala:99)
	at scala.collection.mutable.HashMap$$anonfun$foreach$1.apply(HashMap.scala:99)
	at scala.collection.mutable.HashTable$class.foreachEntry(HashTable.scala:230)
	at scala.collection.mutable.HashMap.foreachEntry(HashMap.scala:40)
	at scala.collection.mutable.HashMap.foreach(HashMap.scala:99)
	at com.twitter.chill.TraversableSerializer.write(Traversable.scala:27)
	at com.twitter.chill.TraversableSerializer.write(Traversable.scala:21)
	at com.esotericsoftware.kryo.Kryo.writeClassAndObject(Kryo.java:628)
	at org.apache.spark.serializer.KryoSerializationStream.writeObject(KryoSerializer.scala:207)
	at org.apache.spark.broadcast.TorrentBroadcast$$anonfun$blockifyObject$2.apply(TorrentBroadcast.scala:268)
	at org.apache.spark.broadcast.TorrentBroadcast$$anonfun$blockifyObject$2.apply(TorrentBroadcast.scala:268)
	at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1303)
	at org.apache.spark.broadcast.TorrentBroadcast$.blockifyObject(TorrentBroadcast.scala:269)
	at org.apache.spark.broadcast.TorrentBroadcast.writeBlocks(TorrentBroadcast.scala:126)
	at org.apache.spark.broadcast.TorrentBroadcast.<init>(TorrentBroadcast.scala:88)
	at org.apache.spark.broadcast.TorrentBroadcastFactory.newBroadcast(TorrentBroadcastFactory.scala:34)
	at org.apache.spark.broadcast.BroadcastManager.newBroadcast(BroadcastManager.scala:56)
	at org.apache.spark.SparkContext.broadcast(SparkContext.scala:1411)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

java.lang.NegativeArraySizeException

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.resize(IdentityObjectIntMap.java:447)

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.putStash(IdentityObjectIntMap.java:245)

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.push(IdentityObjectIntMap.java:239)

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.put(IdentityObjectIntMap.java:135)

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.putStash(IdentityObjectIntMap.java:246)

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.push(IdentityObjectIntMap.java:239)

at com.esotericsoftware.kryo.util.IdentityObjectIntMap.put(IdentityObjectIntMap.java:135)

at com.esotericsoftware.kryo.util.MapReferenceResolver.addWrittenObject(MapReferenceResolver.java:41)

at com.esotericsoftware.kryo.Kryo.writeReferenceOrNull(Kryo.java:658)

at com.esotericsoftware.kryo.Kryo.writeClassAndObject(Kryo.java:623)

at com.twitter.chill.Tuple2Serializer.write(TupleSerializers.scala:37)

at com.twitter.chill.Tuple2Serializer.write(TupleSerializers.scala:33)

at com.esotericsoftware.kryo.Kryo.writeClassAndObject(Kryo.java:628)

at com.twitter.chill.TraversableSerializer$$anonfun$write$1.apply(Traversable.scala:29)

at com.twitter.chill.TraversableSerializer$$anonfun$write$1.apply(Traversable.scala:27)

at scala.collection.mutable.HashMap$$anonfun$foreach$1.apply(HashMap.scala:99)

at scala.collection.mutable.HashTable$class.foreachEntry(HashTable.scala:230)

at scala.collection.mutable.HashMap.foreachEntry(HashMap.scala:40)

at scala.collection.mutable.HashMap.foreach(HashMap.scala:99)

at com.twitter.chill.TraversableSerializer.write(Traversable.scala:27)

at com.twitter.chill.TraversableSerializer.write(Traversable.scala:21)

at com.esotericsoftware.kryo.Kryo.writeClassAndObject(Kryo.java:628)

at org.apache.spark.serializer.KryoSerializationStream.writeObject(KryoSerializer.scala:207)

at org.apache.spark.broadcast.TorrentBroadcast$$anonfun$blockifyObject$2.apply(TorrentBroadcast.scala:268)

at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1303)

at org.apache.spark.broadcast.TorrentBroadcast$.blockifyObject(TorrentBroadcast.scala:269)

at org.apache.spark.broadcast.TorrentBroadcast.writeBlocks(TorrentBroadcast.scala:126)

at org.apache.spark.broadcast.TorrentBroadcast.<init>(TorrentBroadcast.scala:88)

at org.apache.spark.broadcast.TorrentBroadcastFactory.newBroadcast(TorrentBroadcastFactory.scala:34)

at org.apache.spark.broadcast.BroadcastManager.newBroadcast(BroadcastManager.scala:56)

at org.apache.spark.SparkContext.broadcast(SparkContext.scala:1411)

Disabling Kryo solves the issue. To do that just set spark.serializer to org.apache.spark.serializer.JavaSerializer. Other workaround is to change Kryo’s reference management, as explained on Github:

Kryo kryo = new Kryo();
kryo.setReferences(false);

1 2	Kryo kryo = new Kryo(); kryo.setReferences(false);

Spark and NullPointerException in UTF8String.contains

Written on Jun, 15, 2019

Recently I was debugging a NullPointerException in Spark. The stacktrace was indicating this:

java.lang.NullPointerException
	at org.apache.spark.unsafe.types.UTF8String.contains(UTF8String.java:284)
	at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIterator.processNext(Unknown Source)
	at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
	at org.apache.spark.sql.execution.WholeStageCodegenExec$$anonfun$8$$anon$1.hasNext(WholeStageCodegenExec.scala:377)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:231)
	at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:225)
	at org.apache.spark.rdd.RDD$$anonfun$mapPartitionsInternal$1$$anonfun$apply$25.apply(RDD.scala:826)
	at org.apache.spark.rdd.RDD$$anonfun$mapPartitionsInternal$1$$anonfun$apply$25.apply(RDD.scala:826)
	at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
	at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
	at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
	at org.apache.spark.scheduler.Task.run(Task.scala:99)
	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

java.lang.NullPointerException

at org.apache.spark.unsafe.types.UTF8String.contains(UTF8String.java:284)

at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIterator.processNext(Unknown Source)

at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)

at org.apache.spark.sql.execution.WholeStageCodegenExec$$anonfun$8$$anon$1.hasNext(WholeStageCodegenExec.scala:377)

at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:231)

at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:225)

at org.apache.spark.rdd.RDD$$anonfun$mapPartitionsInternal$1$$anonfun$apply$25.apply(RDD.scala:826)

at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)

at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)

at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)

at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)

at org.apache.spark.scheduler.Task.run(Task.scala:99)

at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:282)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

After some digging I found out that the following query causes the problem:

df1
  .join(df2,
	df1("id1") === df2("id2")
	  && !isnull(df1("ref"))
	  && !isnull(df2("ref"))
	  && df2("ref").contains(df1("ref")) // <--- this is the problem
	, "left_outer"
  )
  .drop("id2")

1

2

3

4

5

6

7

8

9

df1

.join(df2,

df1("id1") === df2("id2")

&& !isnull(df1("ref"))

&& !isnull(df2("ref"))

&& df2("ref").contains(df1("ref")) // <--- this is the problem

, "left_outer"

)

.drop("id2")

If I commented out the line with the comment the NPE was no longer there.…

Playing With args4j Part 5 — Safe setters

Written on Jun, 08, 2019

This is the fifth part of the Playing With args4j series. For your convenience you can find other parts in the table of contents in Part 1 – Mixins There is one more problem with our setters — they are not type safe.…

Playing With args4j Part 4 — Nicer setters

Written on Jun, 01, 2019

This is the fourth part of the Playing With args4j series. For your convenience you can find other parts in the table of contents in Part 1 – Mixins Last time we ended with the setter of a form

parameters.set(IJobParameters::parameter, "Value", JobParameters.class);

1	parameters.set(IJobParameters::parameter, "Value", JobParameters.class);

. What we don’t…

Playing With args4j Part 3 — Nice setters

Written on May, 25, 2019

This is the third part of the Playing With args4j series. For your convenience you can find other parts in the table of contents in Part 1 – Mixins Let’s see how we can implement the setters. Idea is as following: we will…

Playing With args4j Part 2 — Automatic getters

Written on May, 18, 2019

This is the second part of the Playing With args4j series. For your convenience you can find other parts in the table of contents in Part 1 – Mixins Last time we created mixins to be able to compose classes with multiple inherited…

Playing With args4j Part 1 — Mixins

Written on May, 11, 2019

This is the first part of the Playing With args4j series. For your convenience you can find other parts using the links below (or by guessing the address): Part 1 — Mixins Part 2 — Automatic getters Part 3 — Nice setters Part…

.NET Internals Cookbook Part 12 — Memory structure, attributes, handles

Written on May, 04, 2019

This is the twelfth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 82. What is a bit-mapped attribute? Custom attribute? Pseudo-custom attribute? There are…

.NET Internals Cookbook Part 11 — Various C# riddles

Written on Apr, 27, 2019

This is the eleventh part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 75. What is the TimeSpan resolution? It’s 100 nanoseconds, as specified by…

.NET Internals Cookbook Part 10 — Threads, Tasks, asynchronous code and others

Written on Apr, 20, 2019

This is the tenth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 65. How can you await async void method or catch exceptions thrown…

.NET Internals Cookbook Part 9 — Finalizers, queues, card tables and other GC stuff

Written on Apr, 13, 2019

This is the ninth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 59. What happens if an exception is thrown in finalizer? What about…

.NET Internals Cookbook Part 8 — C# gotchas

Written on Apr, 06, 2019

This is the eighth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 52. Can you have class in a class? Class in an interface?…

.NET Internals Cookbook Part 7 — Word tearing, locking and others

Written on Mar, 30, 2019

This is the seventh part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 45. Should we always avoid boxing? Boxing is expensive, it wraps value…

.NET Internals Cookbook Part 6 — Object internals

Written on Mar, 23, 2019

This is the sixth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 36. What is a shim? .NET applications are started directly, the same…

.NET Internals Cookbook Part 5 — Methods, parameters, modifiers

Written on Mar, 16, 2019

This is the fifth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 27. Can two methods differ in return type only? Not in C#,…

.NET Internals Cookbook Part 4 — Type members

Written on Mar, 09, 2019

This is the fourth part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 18. Can you add type constructor to the interface? In C# no.…

.NET Internals Cookbook Part 3 — Initialization tricks

Written on Mar, 02, 2019

This is the third part of the .NET Internals Cookbook series. For your convenience you can find other parts in the table of contents in Part 0 – Table of contents 13. Can a variable be true and false at the same time?…

Windowing functions in recursive CTE

Running On Screen Keyboard in Windows 10 in C#

Machine Learning Part 6 — Matrix multiplication in SQL

Spark and NegativeArraySizeException

Spark and NullPointerException in UTF8String.contains

Playing With args4j Part 5 — Safe setters

Playing With args4j Part 4 — Nicer setters

Playing With args4j Part 3 — Nice setters

Playing With args4j Part 2 — Automatic getters

Playing With args4j Part 1 — Mixins

.NET Internals Cookbook Part 12 — Memory structure, attributes, handles

.NET Internals Cookbook Part 11 — Various C# riddles

.NET Internals Cookbook Part 10 — Threads, Tasks, asynchronous code and others

.NET Internals Cookbook Part 9 — Finalizers, queues, card tables and other GC stuff

.NET Internals Cookbook Part 8 — C# gotchas

.NET Internals Cookbook Part 7 — Word tearing, locking and others

.NET Internals Cookbook Part 6 — Object internals

.NET Internals Cookbook Part 5 — Methods, parameters, modifiers

.NET Internals Cookbook Part 4 — Type members

.NET Internals Cookbook Part 3 — Initialization tricks

Recent Posts

Categories

Archive

Posts