Hacker News new | comments | show | ask | jobs | submit | bchallenor's comments login

Apparently CodeCommit is a new feature (last couple of months?). I had no idea it existed - thanks!

-----


This paper is very readable - thanks for the link.

-----


No, you can write queries that are not really possible to express without it. Basically, it allows you to execute a table-valued function for each row in an earlier query.

For example, in SQL Server I find a common use of CROSS APPLY (which appears to be the same thing) is where the "table-valued function" is a SELECT with a WHERE clause referencing the earlier query, an ORDER BY, and a TOP (=LIMIT) 1. (In fact, this is exactly the example given in the article.) It allows you to do things like "for each row in table A, join the last row in table B where NaturalKey(A) = NaturalKey(B) and Value1(A) is greater than or equal to Value2(B)".

-----


That's not true. Anything you can do with LATERAL you can also do with correlated scalar subqueries in the SELECT list. LATERAL simply makes writing these kinds of queries easier and more intuitive.

-----


The syntax for this is pretty horrible, however. And if you want to return more than one column from the subquery, you would have to duplicate the subquery definition for each column, right? Then you'd have to have faith that the optimizer can work out what you meant and reconstruct just a single subquery.

-----


There's no faith required; the planner is guaranteed not to do that. The "normal" way is to create a composite type containing each of the columns you need, and then "unpack" it to separate columns. Horrible? Yeah, but it's possible.

-----


Is it possible with scalar subqueries to perform anything other than a tree of correlation? With CROSS APPLY one can correlate a DAG of subqueries, e.g. a diamond where B and C depend on A, and D depends on B and C.

-----


What if the limits on the lateral subqueries were 2 instead of 1, and they were doing select * instead on select sum() in the outer query? How would you recreate that with correlated SCALAR subqueries? There's no such thing as non-scalar correlated subqueries is there?

-----


Untested, but this is the general approach:

  SELECT unnest(ar).* FROM
    (SELECT ARRAY(SELECT tbl FROM tbl
                  WHERE .. ORDER BY .. LIMIT 2) AS ar
     FROM .. OFFSET 0) ss;
If you want a specific set of columns instead of *, you'd need to create a custom composite type to create an array of, since it's not possible to "unpack" anonymous records.

-----


Thanks, joining to the last row is an instructive example.

-----


Thanks for the example. It seems to be the same as MS SQL Server's CROSS APPLY / OUTER APPLY?

Anyway, good that Postgres has it too, now. There are several Postgres features I'd love in SQL Server, like range types...

-----


Usually when I've had to use cross/outer apply it's been to work around overly normalized, and somewhat bad data.

Agreed on range types.. proper enums in T-SQL would be nice too. I'm really liking where PL/v8 is going, and would like to see something similar in MS-SQL server as well.. the .Net extensions are just too much of a pain to do much with. It's be nice to have a syntax that makes working with custom data types, or even just JSON and XML easier.

If PostgreSQL adds built-in replication to the Open-Source version that isn't a hacky add-on, and has failover similar to, for example MongoDB's replica sets, I'm so pushing pg for most new projects.

Maria/MySQL seem to be getting interesting as well. Honestly, I like MS-SQL until the cost of running it gets a little wonky (Azure pricing going from a single instance to anything that can have replication for example). Some of Amazon's offerings are really getting compelling here.

-----


I don't know about SwiftKey, but the Google English, French and Japanese keyboards all bring up the input method dialog when you long-press space. So these three at least are consistent.

-----


Paris (55,000/sq mi) is twice as dense as New York City (27,778.7/sq mi) (numbers from Wikipedia). It's one of the densest cities in the Western world.

Edit: though I suppose if you consider Manhattan to be "New York" then that is more dense (70,825.6/sq mi) than Paris.

-----


That Subtext screencast is fascinating - thanks for posting the link. Reminds me of the Bret Victor talks.

I agree with your other points - this is something I'd love to build (or see built). But do you think it could displace Excel?

-----


I'm not sure if anything will displace Excel (or spreadsheets in general).

But what I'd like to see is a generalization beyond columns/rows/sheets for relating data of various dimensions. The underlying data type would probably be a list (so lisp) but have options to enable order, array access, key->value access, etc. Cell references would work more like in programming languages and operate over ranges that aren't just two dimensional.

So a spreadsheet would be a subset of this. So a set of columns with array access, a set of rows with array access, a notation like column-row for cell references, and all of the operators like $ for absolute references, etc.

I guess the underlying data structure could be a relational database but I'd prefer something human-readable like JSON with a thin notation over it for expressing relationships between structure members. You could use something like the Subtext editor to work on it but maybe if the syntax was easy enough you could write it manually.

I think the real power of this would be if relationships could be executables (like how Excel macros work but with stdin/stdout/stderr and black box scripts written in any language). Imagine being able to call sed/awk or really high level tools like Go/Erlang and Matlab/Octave per-cell the way we use SUM in Excel..

-----


Yes this is close to what I was thinking of. Basically I want a tool for manipulating ad-hoc collections (lists/sets) of data. Something that is as powerful as SQL (but easy to set up for throwaway pipelines), as accessible as Excel (but not as restrictive - Excel forces you to work in two-dimensional projections of your actual data), that interfaces well with standard formats (JSON, CSV, Excel) and data sources (grep, find, REST, SQL, log files).

For example, say I want to make a wiki page to track some kind of code migration project. Currently I might grep for usages of a term, and project out the (module name, term) pairs. I then run this query a few times for different terms, and use Excel to merge and pivot the data so the first column is a module name and the remaining columns mark occurrences of each term. I then copy paste into Vim and use a regex to mangle the data into wiki markup.

There is surely a better way of doing this, using a single tool to glue the steps together so that the pipeline is repeatable and the various steps are individually reusable.

-----


All these suggestions would make for a really powerful tool. The issue is how to make it all approachable to non-coders. Almost all the excel users I've talked to don't know how to make macros let alone any more complex code. I think the issue is trying to make these concepts understandable and usable to everyone so users can get started straight away.

Right now companies don't send their employees on VBA courses even though they could work some much more efficiently with a basic knowledge of coding.

-----


I've been dreaming of a general purpose tool using Excel as a front-end and being able to dynamically manipulate backend data for different presentation.

For example, an asset management database. If I can use this to generate different free form queries, it would be something I would totally throw money at.

-----


I actually worked on this exact solution a few months ago.

I translated excel into a javascript data structure, translating the formulas into javascript functions. The data structure could be saved to json, and them reloaded back into javascript. The cool thing is that changing a value (a2 = 3) automatically updates dependent values in the rest of the data structure. The data structure can be referenced much like the spreadsheet - it is a tree with branches for pages, rows, columns. There is a simple faster csv like API that makes it easy to go page by page, row by row. Also, cells can be looked up explicitly.

The code is proprietary, which is a shame. It was written as a library to quickly reverse engineer large, complex xlsx files. The trick in making it work is just having the right data structure.

Sorry if I am rambling or any misspellings. I am very sick right now, drowsy from medication, and trying to type thus on my phone.

-----


Both the [Leopold Tenkeyless](http://elitekeyboards.com/products.php?sub=leopold,tenkeyles...) and the [Filco Majestouch-2 Tenkeyless](http://www.keyboardco.com/keyboard/usa-filco-ninja-majestouc...) are "full size [except the numpad], heavy, long-key-travel, concave key-tops, easy-typing, standard layout". I have the Filco.

-----


Others make the point about calling costs, but if you want data only (say for a tablet/laptop/mifi) an any-network roaming SIM would be very attractive. For example my current network is great in London, but has no signal at all in my hometown. It would also be handy for use on the train, where coverage on all networks is patchy.

-----


Various countries have dividend imputation for that:

> it reduces or eliminates the tax disadvantages of distributing dividends to shareholders by only requiring them to pay the difference between the corporate rate and their marginal rate.

http://en.wikipedia.org/wiki/Dividend_imputation

-----

More

Applications are open for YC Summer 2016

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact

Search: